Common Data Service Returns no data from a Lookup column

[eselover]

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.

[Anonymous · Accepted solution]

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.

[eselover]

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]

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]

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]

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

[gdiegoli]

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.

[AlexLindberg]

I had the same issue.  I have a page with three forms.  Only 1 is to be visible at any time based on input data.

When doing a LookUp to fetch data from CDS, only the columns reference in the first form where returned if

was enabled.

When Off all columns where fetched as expected.  Is there a way to turn this on to make loading faster but also specifically request columns to be return in an LookUp?

Thanks.

[bkumar]

This helped me also i tried to use !IsBlank(Lookupcolumn.Lookuptable other column that i wanted to bring ) gave error data type mismatch:
Then i tried !IsEmpty, gave me delegation warning and then finally i used the example provided and this helped.