[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:
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:

And some other useful blog posts:

Comments

  1. Very thorough Linn Thanks for the post!

    ReplyDelete
  2. Hi Linn,
    Have a question on Item 3, Updating/Patching to a Lookup Field with GUID Value
    I assume the example is based on the following:
    Many-to-One relationship
    -
    First Entity: Accounts (Many)
    Field: Primary Contact (with LookUp data type)
    -
    Second Entity: Contacts (One)
    Field: Contact
    -
    Question 1:
    Just to confirm the Patch function is to copy the value
    from
    Contact field of the second Entity Contacts, with record number specified by the GUID ("cd6843b8-d603-ea11-a811-000d3a300a85")
    to
    the first record of the Primary Contact field in the First Entity Accounts
    -
    Question 2:
    Where do we get the ("cd6843b8-d603-ea11-a811-000d3a300a85") to the GUID?
    -
    Thanks in advance for your help.

    Regards,

    Albert

    ReplyDelete
    Replies
    1. Hi Albert

      A1: That's correct. It is the simplified version of how updating the lookup field works.
      In other words, that commend is
      1. Lookup the record from Contacts entity data source where the field Contact (which is the primary ID field) = hardcoded GUID value
      2. Update the Primary Contact field of the first record in Accounts entity data source with the value from step 1

      A2: Since the sample code was the simplfied version, it is using the hardcoded GUID.
      Getting the GUID of the contact (or any record that you would like to retrieve will vary according to the scenario)

      One of the examples is updating the Primary Contact of the Account records.
      The first screen contains the gallery of Account records and when the user selects a particular Account record, it will show the second screen with the Dropdown control with the list of Contacts from that organisation.
      When the user selects one of the Contacts and click Save, it triggers the Patch command to update the Primary Contact lookup of the select Account with the value from the dropdown control.

      Let's say the name of the Dropdown control is Dropdown1
      The Items property of Dropdown1 is Contacts data source and filtered for the selected Account
      OnSelect of the Save button, you can get the GUID of the selected contact record from the dropdown like this to patch the Primary Contact lookup

      Dropdown1.Selected.Contact

      If you need to discuss further, you may DM me on Twitter.

      Delete
  3. Hi Linn, thank you so much for sharing this amazing article.
    I have two tables: Table 1: Project Name and the other table that has different records that can be associated with many Projects. (1:N)
    I was trying to display the project name in a combo box based on the below filter:
    With(
    {
    _resid: LookUp(
    TimeSheetResources,
    'User Name'.'Timesheet User' = varuserID,
    'Resource Identification'
    ),
    wNextDay:
    DateAdd(
    Today(),
    1,
    Days
    )

    },
    ShowColumns(
    Filter(
    'Project Resources',
    'Res. Name'.'Resource Identification' = _resid ,
    'Start Date' < DateAdd(Today(),1,Days) && 'End Date' >= Today()
    ),
    "cr884_ProjectID","cr884_projresid")
    )
    https://imgur.com/2P2NGLN

    The filter function is working very well. However, the label of the Project Name is not shown in the combo box. If I place the same function in a gallery and I use on the label Text property: ThisItem.cr884_ProjectID.'Project Name' it is showing the project name successfully.
    https://imgur.com/5iMgAal
    I have also tried the below logic without any issue in the function but still the label value of the project name is not showing:
    https://imgur.com/MUHTHyt
    Could you please advise what might be the issue?
    I look forward to your response.
    Thank you!

    ReplyDelete
    Replies
    1. I am not sure how you can set the DisplayFields property of the combo box with the column from the lookup table.
      The easier solution might be adding the filtered data into collection, populate the project name column as a separate text column in the collection and set that as the DisplayFields property of the combo box.

      Delete
  4. I spent hours trying to figure out how to do number 2. You're a life saver, how did you even figure that out? Was it buried in the documentation?

    ReplyDelete
  5. HI Linn,
    Have a question on Item 3, there is guid you provided for particular rocord, but what if user select any one record in primary lookup ? How should we write the expression, can you please help me with the solution.

    ReplyDelete

Post a Comment

Popular Posts