Getting the Lookup Attribute of the Parent Record in Power Apps Canvas App Development with Microsoft Dataverse

When you collect the CDS data source into the collection, you can add the fields from the related (lookup) entity using AddColumns function. That can be achieved because the lookup values from CDS data source are returned as a record in Power Apps comparing to Dynamics 365 SDK/API which only returns an Entity Reference with GUID and Entity Type.

But adding the fields from the related (lookup) entity works for all data type except the lookup fields. When we add the lookup value of the lookup field, there is no error in the formula but it just returns an empty column. I guess that is because of the same reason why CDS data source structure needs to return the whole record for the lookup value of the lookup field.

I asked in the Power Apps Community forum but I got a response from the Microsoft Community Support Engineer that "we can't get the Lookup value of lookup field in batches". So here is the workaround solution provided by the Microsoft Community Support engineer and I improvised a little bit.

I will try to explain the solution with the out-of-the-box entities as an example for better understanding. Let's say the maker wants to collect a Collection of Account data with an additional user GUID column of the Created By of the related Primary Contact. Here's the relationship diagram for those 3 entities involved in this example.

My first attempt is the following formula where I used AddColumns function to add the GUID, Full Name and Created By columns of the related Primary Contact lookup. Both ContactID and Full Name columns were populated into the collection but the "Contact_Created By" column was empty. ☹

 ClearCollect(  
   colAccountJoin,  
   AddColumns(  
     Accounts,  
     "Contact_Contact",  
     'Primary Contact'.Contact,  
     "Contact_Full Name",  
     'Primary Contact'.'Full Name',  
     "Contact_Created By",  
     'Primary Contact'.'Created By'  
   )  
 );  

So I looked around in the Community Forum to find out how to join two data sources in Power Apps. I found a few posts which suggest using Lookup function to add fields from multiple data sources into a collection. I updated my formula as below and added the Lookup function but it didn't work and "Contact_Created By Lookup" column was empty in the collection. ☹

 ClearCollect(  
   colAccountJoin,  
   AddColumns(  
     Accounts,  
     "Contact_Contact",  
     'Primary Contact'.Contact,  
     "Contact_Full Name",  
     'Primary Contact'.'Full Name',  
     "Contact_Created By",  
     'Primary Contact'.'Created By',  
     "Contact_Created By Lookup",  
     LookUp(  
       [@Contacts],  
       Contact = 'Primary Contact'.Contact,  
       'Created By'  
     )
 );  

That's when I posted into the forum and got the solution. The solution suggested was to add an empty column, loop through the collection and patch the column value using the Lookup function. But what I didn't understand with the solution was that the suggested solution function clones the collection and loop through the cloned collection to update the original collection. So I tried to remove the cloning of collection as the formula below. But the App checker is showing the error message "This function cannot operate on the same data source that is used in ForAll." and that's why I understood why the collection needed to be cloned. Unlike regular programming, I cannot Patch the same collection while looping through.

 ForAll(  
   colAccountJoin,  
   Patch(  
     colAccountJoin,  
     {  
       'Contact_Created By GUID': LookUp(  
       [@Contacts],  
       Contact = Contact_Contact,  
       'Created By'.User  
                )  
     }  
   )  
 );  

Then, I found a better way to loop through the whole collection to populate the column with Lookup function. Here's the final solution formula and I can finally get the Created by GUID of Primary Contact in the "Contact_Created By GUID" column.

 ClearCollect(  
   colAccountJoin,  
   AddColumns(  
     Accounts,  
     "Contact_Contact",  
     'Primary Contact'.Contact,  
     "Contact_Full Name",  
     'Primary Contact'.'Full Name',  
     "Contact_Created By",  
     'Primary Contact'.'Created By',  
     "Contact_Created By Lookup",  
     LookUp(  
       [@Contacts],  
       Contact = 'Primary Contact'.Contact,  
       'Created By'  
     ),  
     "Contact_Created By GUID",  
     GUID("00000000-0000-0000-0000-000000000000")  
   )  
 );  
 UpdateIf(  
   colAccountJoin,  
   true,  
   {  
     'Contact_Created By GUID': LookUp(  
       [@Contacts],  
       Contact = Contact_Contact,  
       'Created By'.User  
     )  
   }  
 );  

Please comment below if you are able to add the lookup value of the lookup field or have a better workaround solution for this.