cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cshinneman
Frequent Visitor

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?

1 ACCEPTED SOLUTION

Accepted Solutions
EricRegnier
Super User II
Super User II

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 
    2021-01-28_14-21-29.png

Hope this all makes sense!

View solution in original post

12 REPLIES 12
EricRegnier
Super User II
Super User II

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

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
Super User II
Super User II

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

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
Super User II
Super User II

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?

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

 

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

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
Solution Sage
Solution Sage

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
Super User II
Super User II

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 
    2021-01-28_14-21-29.png

Hope this all makes sense!

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Users online (2,150)