ADVANTAGES AND DISADVANTAGES OF USING POWER FX TO OBTAIN RELATIONSHIP DATA

[Jmanriquerios]

We have Power FX columns available to use formulas in the columns from this language, but we can also obtain the fields of a relationship that we have between two tables.

I have been working on a use case where I have created a data model that has several tables but I will focus on three tables:
Teacher: where the information regarding the teacher is recorded.

Section: where the information regarding a subject is recorded and has N students and a single teacher associated with it, which is why it has an N:1 relationship with the teacher.

Teacher payment: where a teacher's payment is recorded based on the section assigned to them.

Under this scheme in the section table I have created a lookup column with the teacher field.

In the case of the teacher pay table, I have created a lookup column with the section table

So far nothing that we do not know and have not done before, now within the Teacher Payment table, instead of creating the teacher field as an N:1 relationship with a lookup, being able to obtain the value of the teacher under JS or a Workflow Once the section is obtained, or allowing it to be selected by the user, I have decided to use the Power Fx column.

Now, as advantages we see:
1. Without programming we can get the value of the teacher's name
2. Any other additional values we need from the teacher table.
3. A field whose value depends on the relationship, if you change the section the value of teacher will change.

Disadvantages, this is where depending on what you need it can have a big disadvantage, for example if you needed the teacher's GUID you would not have it, it is a limitation of the Power Fx columns

This could complicate access either by code using the Wep API and in the use of Power Automate if we want to expand the teacher column we could not because we do not have the GUID, and sometimes we need to use the GUID to create the relationship or perhaps for the simply relate it to the activities table or use it within the To of the Email table.

As we can all have a solution to this either with code or with Power Automate, at the Power Automate level it is using a JSON action to be able to use this value, despite coming from the OUTPUTS of the Dataverse action when we expand a table we cannot use that field.

We will have to use Parse JSON to be able to use the enc_profesor_value field

So when you face these situations in modeling your data you must consider the entire process that you will build around it and whether or not it is worth using one way or another to obtain the data.

On the other hand, and finally, if we want to do data exploitation, it may be better to use the lookup. Additionally, we will lose relational navigation in model-based applications (Model Driven).