Solved: Model driven Lookup on calculated column

Model driven Lookup on calculated column

I am attempting to Lookup on a Parent calculated column. It works in a very odd way. In one instance it brings in the locally defined field (of the calculated column) in the parent table but not on the looked-up field in the parent. In another case the parent table shows as a popup table but when a record is selected no data is copied into the child field. In another case where the parent calculated field is composed of two lookup fields no popup table shows at all.

Is it possible to lookup to a parent calculated field?

Categories:

Microsoft Dataverse

Tables

Thanks for the video, that really helped. So you're issue is actually not with the calculated fields/columns, but with the tables/entities data. I've summarize them with a solution below:

Every table has a primary field/column. The purpose of the primary field is promote a field that will be displayed in the header of its table's forms, and most importantly in lookups of referencing tables. We need a primary field because otherwise what would be shown in lookups, the ID (GUID)? But that wouldn't be very user-friendly. This is the MS docs definition:
"By default, every table contains a Primary Field, which is used by lookup columns when establishing relationships with other tables. Typically the primary column stores the name or primary description of the data stored in the table. You may update the name and display name of the primary column before saving the table for the first time. Also, observe that the primary column also has its own Name box, which functions similarly to the table name described above. The primary column name is autopopulated when a display name is entered, uses the same prefix as the table, and cannot be changed after the table is created."

You don't have data in those primary field and it's the reason why you see "(no name)" and why it seems that the calculated fields do not work. It does calculate in the background but the field is an empty string so there's nothing to concat.
A common solution is to auto-populate that primary field with the desired value from another field. Suggest to use classic workflows to have it sync but if you need async leverage Power Automate.
The reason you see values in the lookup dropdown is because the lookup on the form is bound to a view with columns that has data and does not contain the empty primary field.
A solution for the other lookups is to update that view (typically the lookup type of view) or create a new view and associate it to the lookup field.
More info: https://docs.microsoft.com/powerapps/maker/model-driven-apps/form-designer-add-configure-lookup