[Bug] Using the ClearCollect() Function in PowerApps does not Pull any Lookup Columns from Common Data Service (CDS) Data Source

When I Collect from CDS Data Source into Collection in canvas app designer, the lookup fields are not being populated even though I can see the lookup values when I bind the data source to the data table.
This is the sample formula that I used
 ClearCollect(ContactCollection, ShowColumns(Contacts, "fullname", "createdby"))  

And this is the output collection without any data in the lookup column.


https://powerusers.microsoft.com/t5/Building-PowerApps-Formerly/Common-Data-Service-Returns-no-data-from-a-Lookup-column/td-p/272581
When I looked through how to resolve this issue, I finally found the forum thread above and the workaround solution suggested by one of the users is to use the AddColumns() and DropColumns() function to force the lookup field to be pulled.

You can also see the explanation of the workaround solution in the reply post the user mfco in that forum thread. Basically, what we are trying to achieve is to add a column from the data source of the lookup entity (e.g. User entity in this case for "createdby" column) in order to force the app to pull the lookup field data.

But in my case, adding the "Users" or "First(Users).User" as the column did not resolve the issue. So what I did was just adding the same createdby column of the first Contact record as a new column " First(Contacts).'Created By' "
Dropping a column is optional and you can keep it in the collection if it is not going to confuse the other makers.

This is the final formula after adding workaround solution and once it is added, the collection is populated with the values in the lookup column.
 ClearCollect(ContactCollection,  
   DropColumns(  
     AddColumns(  
       ShowColumns(Contacts, "fullname", "createdby"),  
       "tempColumn",  
       First(Contacts).'Created By'  
     ),  
     "tempColumn"  
   )  
 )  


Hope this helps others who are having the same issue until the issue is fixed.


Update: There is another workaround solution provided in the comment section.
 ClearCollect(clColl,Filter('[dbo].[Collection]', cnt_styles>0 And parent_season <> "" And category_1<>""))  
On the formula above, the Collection table from SQL Server table (through the On-Premises Data Gateway) was added to the collection clCol1 but the parent_season and category_1 columns were not filled. The workaround solution was to add the conditions for each of these columns.

Update 2: Another solution provided in the comment section was to disable the app setting (Settings--> Advanced Settings --> Explicit Column Selection)
When enabled, this parameter causes PowerApps to bring only columns referenced in the App. When that setting is OFF, the Datasource is fully loaded with all column data.


If you have any information about the permanent fix or a better workaround solution, please leave a comment below.

Comments

  1. Common Data Service Returns no data from a Lookup column

    I had the same problem with columns not being populated when running the ClearCollect function. I solved (workaround) by applying a condition to each column that was not being filled:

    ClearCollect(clColl,Filter('[dbo].[Collection]', cnt_styles>0 And parent_season <> "" And category_1<>""))

    I access SQL Server tables through the On-Premises Data Gateway and needed to filter the records that had cnt_styles> 0, but the parent_season and category_1 columns were not filled. After adding the conditions for each of these columns, they are now fulfilled.

    ReplyDelete
    Replies
    1. Thanks for providing the workaround solution based on your experience.
      That will be really helpful for my future canvas apps and the other readers.

      Delete
  2. Hi Linn, I discover another solution, maybe the cause of this issue.
    There´s a parameter (Settings-->Advanced Settings-->Explicit Column Selection
    When enabled, this parameter causes PowerApps to bring only columns referenced in the App. I turned it OFF and the Datasource was fully loaded with all column data. I think this feature (Explicit Column Selection) contains a bug.

    ReplyDelete
    Replies
    1. Thanks. It is good to know about it.

      I guess it works for data source from SQL Server tables through the On-Premises Data Gateway. But for Common Data Service data source, I tried turning off the "Explicit Column Selection" when I encountered that issue and it did not solve the problem.

      Delete

Post a comment

Popular Posts

[Power Automate] How to Set Lookup Field Value in Common Data Service (current environment) Flow Connector

Access Flow Run History within a Record in Dynamics 365/CDS

[Power Automate] List Records - Use Expand Query to Retrieve Related Data in flow

[Power Apps] Using Common Data Service's Lookup Data Type Field in Canvas App

Find out how to include a link to the record (Record URL) when sending an email from Dynamics 365/CDS using flow

Script Errors Encountered after Field Service Upgrade to 8.x

Pass Parameters to Quick Create Form in Dynamics 365 using formContext.data.attributes

Getting the Lookup Attribute of the Parent Record in Power Apps Canvas App Development with Common Data Service (CDS)

Send Email from Dynamics 365/CDS with Attachment from Notes Using Flow