cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
eselover
Level: Powered On

Common Data Service Returns no data from a Lookup column

I am having an issue while developing a new App;

 

Within the CDS I have a few Entities, Customer, Contact, and Activities, the Activities Entity has two lookup columns, (created from setting the relationship first, rather than the field) that looks at Customer and Contact. 

 

Within my PowerApp I have a custom form that I patch the information to the CDS, I am passing the entire Contact / Customer Record into that LookUp Column. For reference later.

(i.e.

"Patch(b_Activities, Defaults(b_Activities), {ID: 1, b_Customers: LookUp(b_Customers, 'Company Name' = Customers_NewInput.Selected.cr528_name)})"

)

 

Then I have Gallery that displays the information from the Activities Entity, within that Gallery I have a Label that is supposed to show the Customer / Contact name from that LookUp Column. I have the Text field set to: ThisItem.b_Customers.'Company Name'. 

 

This returns no value, even when I have a Form that once you select that item from the Gallery and then try to display the information within a form still no value.

 

I have opened the data entry in Excel to view the data, to see maybe the patch didn't work correctly but it did! The information on the CDS side is working like it should, but within PowerApps it doesn't show anything, like the LookUp didn't work even though the data is within the Entity.

 

On App start I do create a Local Collection of the data ( ClearCollect(Collection_Activities, b_Activities)  ) so I don't run into delegation warnings trying to either Filter or search on the DataSource. And if I switch the 'Items' field on my Gallery to the Data Source it shows the information correctly, but not my local Collection copy of the data.

 

I am not sure if this is a known issue, I have tried to find a solution to it in the Community but I had no luck finding anything. Passing the Data source into a Collection doesn't seem to grab the data from LookUp columns the majority of the time,  some help on solving this issue would be greatly appreciated.

 

My current work around is instead of using LookUp columns I have text fields then do a LookUp of the Contact / Customer data based off of that text field on the PowerApps side, which the more I do that the more the app response time will slow down, which is not ideal but is a work around until the issue is fixed.

1 ACCEPTED SOLUTION

Accepted Solutions
mfco
Level: Powered On

Re: Common Data Service Returns no data from a Lookup column

My team and I have been having this issue as well.

Using the ClearCollect() function does NOT pull any lookup values.

Our current workaround is to use the AddColumns() and DropColumns() function to force the lookup field to be pulled.

For example, say we have a User entity which has a Lookup property (relationship created in CDS) for the Country they live in.

User Entity

Fields:

  • cr123_Name - text
  • cr123_Country - Lookup

 

Country Entity

Fields:

  • cr123_ID - text
  • cr123_CountryName - text

 


If we try to Collect() the UserEntity, we get all the regular fields, but the Country is blank. To work around this, we will use an AddColumns() function.

 

We will use an arbitrary column name "tempCountryColumn" (you can name this whatever you want), and pass in ANY field from that lookup entity, or the lookup itself.

 

In this case, we use cr123_Country, but we could have used cr123_Country.cr123_ID  or cr123_Country.cr123_CountryName.

 

This will add a column to the collection named "tempCountryColumn" with the country record.

 

You can stop here if you want. But if you are using the gallery to modify the record, it needs to match the Entity.

 

Since there is an additional column in this User record, you won't be able to modify it in a form. Next we wrap it with a DropColumns() function and drop the "tempCountryColumn" we just added.

 

As a result, we will now have the Country Lookup records within our userCollection. You can pull more than one Lookup column by adding more columns for the same Entity.

ClearCollect(userCollection, DropColumns(


   AddColumns('UserEntity',
       "tempCountryColumn", cr123_Country
   ),


   //Columns to Drop
   "tempCountryColumn"
   )


);

Do note that doing this for entities with many Lookup columns will be quite slow.

I hope that the PowerApps team is able to fix this issue as this workaround is rather hacky and inefficient.

 

View solution in original post

5 REPLIES 5
mfco
Level: Powered On

Re: Common Data Service Returns no data from a Lookup column

My team and I have been having this issue as well.

Using the ClearCollect() function does NOT pull any lookup values.

Our current workaround is to use the AddColumns() and DropColumns() function to force the lookup field to be pulled.

For example, say we have a User entity which has a Lookup property (relationship created in CDS) for the Country they live in.

User Entity

Fields:

  • cr123_Name - text
  • cr123_Country - Lookup

 

Country Entity

Fields:

  • cr123_ID - text
  • cr123_CountryName - text

 


If we try to Collect() the UserEntity, we get all the regular fields, but the Country is blank. To work around this, we will use an AddColumns() function.

 

We will use an arbitrary column name "tempCountryColumn" (you can name this whatever you want), and pass in ANY field from that lookup entity, or the lookup itself.

 

In this case, we use cr123_Country, but we could have used cr123_Country.cr123_ID  or cr123_Country.cr123_CountryName.

 

This will add a column to the collection named "tempCountryColumn" with the country record.

 

You can stop here if you want. But if you are using the gallery to modify the record, it needs to match the Entity.

 

Since there is an additional column in this User record, you won't be able to modify it in a form. Next we wrap it with a DropColumns() function and drop the "tempCountryColumn" we just added.

 

As a result, we will now have the Country Lookup records within our userCollection. You can pull more than one Lookup column by adding more columns for the same Entity.

ClearCollect(userCollection, DropColumns(


   AddColumns('UserEntity',
       "tempCountryColumn", cr123_Country
   ),


   //Columns to Drop
   "tempCountryColumn"
   )


);

Do note that doing this for entities with many Lookup columns will be quite slow.

I hope that the PowerApps team is able to fix this issue as this workaround is rather hacky and inefficient.

 

View solution in original post

eselover
Level: Powered On

Re: Common Data Service Returns no data from a Lookup column

Thanks mfco!

I was considering about doing something like this, adding a column and forcing the LookUp.  But I will eventually have more Entities that have the same structure for the lookups, and I am weary of the application suffering on speed if I have too many of those.  Especially if the user ever wanted to change the Contact / Customer information, and that data needs to be reflected across the app. I would have to force a refresh of the collections to pull in the changed data, and we have 3000+ Entries.  I will give that a try until the issue is fixed, it may not be as slow as I am thinking it will be, but worth a shot.

 

Right now I am using the Text fields to just display which Customer / Contact is attached to said Entries in the Activity Entity, and if the user wants to view the details of that entry, once selected I preform a lookup and store that record in a variable to reference, then clear it out when they navigate away from that page.

 

I appreciate the help, and hope the PowerApps team can fix this issue soon so we can use the CDS like it is intended! 

Helpful
Level 8

Re: Common Data Service Returns no data from a Lookup column

This needs to be fixed urgently.

 

The behavior change is not consistent with "Lookup to second level tables" specified in this post back in August:  https://powerapps.microsoft.com/en-us/blog/preview-for-improve-data-source-experience-and-common-dat...

eselover
Level: Powered On

Re: Common Data Service Returns no data from a Lookup column

I apologize for the super late response and classifying the above comment as the solution to the problem. This project got tabled and I had forgotten about the post. 

Helpful
Level 8

Re: Common Data Service Returns no data from a Lookup column

Unfortunately, I don't think the post is solved.  I'm still seeing the issue where related values are not accessible for some reason.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 326 members 5,658 guests
Please welcome our newest community members: