[Power Apps] Using Microsoft Dataverse Lookup Data Type Field in Canvas App

If you come from the Dynamics 365 CE/CRM developer background, you would know that the value of the lookup field (or attribute, whatever you prefer to call it) comprises of 3 basic components:

1. ID (the GUID of the related record)
2. Name (the Primary Field value of the related record which is used as the Display value of the lookup field in the model-driven app)
3. Entity Type (the logical name of the related record entity which is used to identify the entity type especially in polymorphic lookups such as Owner, Customer, etc.)

The data type is EntityReference in Xrm.Sdk and depending on which development platform you are on (JavaScript, C#, etc.) you will need to use one or more of these components to get/set the value of the lookup field. (there is another component for the alternate key but I'm not going to cover that in this post).

When you start working on canvas app in Power Apps, you will notice that the lookup field of CDS connector is quite different from what you would expect, comparing to what you are used to in Common Data Service and model-driven apps in Dynamics 365. These are the key differences (properties/behaviour) I found working with lookup fields in canvas app development:

1. Referencing the lookup field:  Data Type = Record
2. Getting the GUID Value of a Lookup Field
3. Updating/Patching to a Lookup Field with GUID Value
4. Getting the Other Fields from the Related Entity of the Lookup Field
5. Getting the Lookup Fields from the Related Entity of the Lookup Field
6. Filtering Records by Related Entity field
7. Using lookup fields in Collection
8. Working with Polymorphic Lookups
9. Importing to a different environment

1. Referencing the lookup field: Data Type = Record

The data type of the lookup field is no longer just a reference with GUID. It is the reference to the whole record. (read more details in this Microsoft documentation). In that way, a lot of things are changed in terms of how you get the lookup field GUID value or how you set the value to the lookup field.

E.g. in the following formula First(Contacts).'Created By', Created By lookup field is returned as Record data.

2. Getting the GUID Value of a Lookup Field

Since the data type is Record, the lookup field can be accessed by the primary ID field of the entity. (which is the same as the display name of the entity in singular form)
E.g. in this case, First(Contacts).'Created By'.User where the User is the display name of systemuser entity which is the lookup entity for Created By field.

This is quite similar to the following SQL statement rather than getting contact.createdby directly

 SELECT TOP 1 systemuser.systemuserid FROM contact  
 INNER JOIN systemuser  
   ON contact.createdby = systemuser.systemuserid  

Make sure to add the CDS data source of that related entity (e.g. Users in the above example) to retrieve the data. If you've enabled "Improve data source experience and Common Data Service views" setting, the Power Apps Studio will automatically add it for you.

If you use the global disambiguation for your data source, it does not return the values for the related Record. I could not find any related official article/blog/forum post about it but that's one thing to look out for when getting values from lookup field.

First([@Contacts]).'Created By'.User

3. Updating/Patching to a Lookup Field with GUID Value

Since the lookup field data type is Record, you also cannot just set the GUID value to update in the Patch function. You will see the following error if you try to set the GUID value to the Lookup field.

The value to set the lookup needs to be of Record data type and if you only have the GUID value, you will need to call Lookup() function to get the record. This is the sample formula which updates the Primary Contact lookup field of the Account record with GUID.

 Patch(  
   Accounts,  
   First(Accounts),  
   {  
     'Primary Contact': LookUp(  
       Contacts,  
       Contact = GUID("cd6843b8-d603-ea11-a811-000d3a300a85")  
     )  
   }  
 )  

4. Getting the Other Fields from the Related Entity of the Lookup Field

Even though getting the GUID Value of a Lookup Field is tricker than normal, getting the value of other fields (except lookup fields) from the related entity is easier. Since you have the whole Record as the lookup field, you can just get the other values by just getting the field of that Record data.

First(Accounts).'Primary Contact'.'Full Name' First(Accounts).'Primary Contact'.Email

5. Getting the Lookup Fields from the Related Entity of the Lookup Field

The only exception is that you cannot get the value of Lookup field of the related entity.
In the following example, I am trying to get the Created By User GUID value of the Primary Contact of the Account record. My attempt to get the lookup field of the related entity ended up the error message "Multiple levels of many-to-one relationship expansion aren't supported".

In that case, Lookup() function needs to be used to get the related Record, and then get the lookup value from it.
The following sample formula will return the Created By User GUID value of the Primary Contact of the Account record.

 LookUp(  
   Contacts,  
   Contact = First(colAccounts).'Primary Contact'.Contact  
 ).'Created By'.User  

If you want the have the lookup attribute of the parent record in the single collection by joining two entities, you can refer to my other blog post on Getting the Lookup Attribute of the Parent Record in Power Apps Canvas App Development with Common Data Service (CDS)

6. Filtering Records by Related Entity field

You can filter the records based on the GUID value of the lookup field by using the formula below:

Filter(Accounts, 'Primary Contact'.Contact = GUID("cd6843b8-d603-ea11-a811-000d3a300a85"))

Or if you need to Filter any other field of the related entity, you can filter it this way :

Filter(Accounts, "(Sample)" in 'Primary Contact'.'Full Name')

More details can be found in my answer in this forum post.

If you have "Explicit column selection" setting on, make sure to use that related entity field in the control that you are binding the data source even if it is not supposed to be displayed to the user (e.g. hidden label in the gallery template). In that way, the app would retrieve that data from the related entity. Otherwise, it will return empty data, the condition will always be false for all rows and the Filter will return an empty table.

7. Using lookup fields in Collection

Using lookup fields in the Collection can be a bit tricky sometimes. Sometimes, you might experience that Collecting from CDS Data Source into Collection would result in the empty value for the lookup fields.

 ClearCollect(  
   colContacts,  
   ShowColumns(  
     Filter(Contacts, EndsWith('Full Name', "(Sample)")) ,  
     "fullname",  
     "createdby",  
     "modifiedby"  
   )  
 )  

In the example above, you will see that the createdby column has a value in the collection (which is shown as a complex data type block) and modifiedby column is empty (which is not possible for a CDS record).

This behaviour is by design and the app will only load the value of the lookup fields which are being used in the app. (this is to optimise the data loading of the app, avoiding to retrieve unused data in the app). So if you are having empty columns in your collection, don't panic! - just use them in the other parts of the app and trigger the ClearCollect event again to see the data in the lookup of the collection.

If you have "Explicit column selection" setting on, make sure to use that particular field of the related entity and trigger the ClearCollect event again.

8. Working with Polymorphic Lookups

When working with polymorphic lookups, you need to note that the Canvas-app support for polymorphic lookups to the Owner, Customer, and Regarding entities is using the new Record Reference data type and the IsType and AsType functions.

Basically, the polymorphic lookups will be returned as Record Reference data and you will need to casts a record reference to a particular entity type before you can reference them as lookup Record data type. The following sample formula will return the Owner User Full Name or Team Name based on the data type of the owner lookup value. More details can be found in this blog post and this Microsoft documentation.

 If(  
   IsType(First(Contacts).Owner, Users),  
   AsType(First(Contacts).Owner, Users).'Full Name',  
   AsType(First(Contacts).Owner, Teams).'Team Name'
 )  

If you are using IsType and AsType functions in the record scope, make sure to use disambiguation operator as the sample formulas below.

Filter(Contacts, AsType('Company Name', [@Accounts]).Account = [@ModelDrivenFormIntegration].Item.Account)

AsType(ThisItem.'Company Name', [@Accounts]).'Account Name')  

9. Importing to a different environment

When you import the canvas app to a different environment with different entity schema (e.g. develop in POC environment and import to Dev once POC is approved), you might experience some problems where the lookup schema names are not being recognised and are showing some formula errors. In my case, the app seems to cache data source schema names in the metadata and it's conflicting with the data sources from the old environment. I was told by the product team engineer that the issue has been fixed since then but if you are still experiencing this type of error, raise a support ticket with Microsoft and then would normally ask for .msapp export file, fix the issue inside and send you back the patched .msapp file.

More Info

You can find really good video tutorials related to lookup fields and canvas apps in the following two-part series by Abe Saldana:

• https://abesaldana.com/canvas-using-optionsets-and-lookup-fields/
• https://abesaldana.com/powerapps-canvas-using-lookups-with-cds/

And some other useful blog posts:

• Embedded canvas app how to get the selected (Polymorphic) lookup field value
• How to set the Default Value for a Dynamics 365 Lookup Control in a Canvas PowerApp
• Canvas Apps & Lookup Fields (CDS)
• How to display the Parent Customer Name on a Gallery or Form in a Canvas PowerApp