Model driven Lookup on calculated column

[cshinneman]

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?

[EricRegnier]

Hi @cshinneman,

Would it be possible to share a little more info and some screenshots on what you are trying to do? I assume you're using a model-driven form and out-of-the-box calculated column/field? You can lookup a parent column in the calculated field with this format: <LookupFieldName>.<FieldName> in your expression.

Cheers

[cshinneman]

Hi Eric,

Thanks for you interest.

Yes I am using out of the box "calculated column/field" in the Parent table with the concat() function to populate a calculated column with the string that I am using as the field for the connection between the Parent and Child tables.  It is in the Child form when selecting the Parent value that the problem arises.  For some reason it does not want to work where the Parent column is calculated.

There isn't much to look at.  I have attached my schema to get some idea of what I am doing.  The "Trainings" table population is the one that is giving me trouble.

[EricRegnier]

You should be able to reference a calculated column in another calculated column (up to 5). Can you paste your calculated expression?

[cshinneman]

this is the expression:

Concat(crd24_employeefullname.crd24_employee, "-", crd24_skill.crd24_skillspecialty)

this expression works fine.  It is the Form that references this column this is not working properly.

Thanks,

[EricRegnier]

That’s weird. Just to try to isolate the issue, crd24_employee and crd24_skillspeciality are both text columns that contain values? Also, doesn’t work on a model-driven form but can you see the data in an entity/table view or via Advanced Find?

[cshinneman]

I am attaching a little video that shows how it is working. 

If we could use alternate keys it would be soo much easier.

[cshinneman]

Was just testing the table view and it is connected to the parent record, it just doesn't display it, shows a "(No Name)" but if clicked on to show the Parent it displays the correct parent record.

[Fubar]

If I a understanding what you are attempting to do correctly, I think you are trying to break one of the limitations:

A calculated field can’t contain a calculated field from another entity that also contains another field from a different entity (spanning three entities): (Current Entity)Calculated Field <- (Parent Record) Calculated Field 1 <- (Parent Record) Calculated Field 2.

https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/customize/define-calcula...

[EricRegnier · Accepted solution]

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:

1. 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.
   
   
2. 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 
   

Hope this all makes sense!