Help with LookUp Columns - CDS Entities returning null data for lookups in PowerApps

[AccuFloDev]

Hello,

I've posted about this twice before over the past month with no answer from anyone from Microsoft, and now my project is way behind.

I have a PowerApp Canvas App I'm developing with the new CDS for Apps.
One of my custom entities has six lookup columns which I'm trying to load into a form from a Gallery.

When I open an item from the Gallery in a Form, most of the time it WILL NOT load LookUp data.
When I try to display the lookup information in the "DefaultSelectedItems" function, it is blank because the lookup data is blank.

When I compare the data of the Entity Row to what's in the database, it shows values for the lookups.
However, in the app it is 'blank'.  

This issue comes and goes (sometimes it works, usually it does not) and I can't make heads or tails of it.

How can I query for a specific entity row and ensure that ALL the columns, including the lookup columns, have data in them?  Is this an issue that is Microsoft is aware of, or is it possibly something I'm doing wrong?

[TimRohr]

I don't think it is just you. I am having a very similar issue.

Given 2 entities, Regions and RegionalGroups, where a Region has many RegionalGroups, I have a relationship between them:

Regions: {RegionName, RegionDescription}

RegionalGroups: {Region [as Lookup field], GroupName}

In a form or gallery displaying the RegionalGroups data, I should be able to reference the RegionName through that lookup field:

ThisItem.Region.RegionName

This will pass the syntax check, but most times it pulls in blank data. I've tried using "Value" for the lookup field as some older posts mention, but it is apparently no longer accepted by the compiler:

ThisItem.Region.Value   <----Produces RedX

I have had somewhat better luck with the LookUp() function, but even that gives me blank results sometimes, especially if a third entity is involved (where RegionalGroups are used in many Roles).

Role: {RegionalGroup [as Lookup field], Employee [as Lookup field], RoleName,...}

If I select the Role in a gallery and I want to store the Region, I should be able to do a LookUp against the RegionalGroup associated with the Role, and return the RegionName. For instance, I can return the RoleName with the LookUp:

LookUp(RegionalGroups, RegionalGroup = RolesForEmployee_BrowseGallery.Selected.RegionalGroup.RegionalGroup, RoleName)

But I CAN'T return the LookUp field through the LookUp() function:

LookUp(RegionalGroups, RegionalGroup = RolesForEmployee_BrowseGallery.Selected.RegionalGroup.RegionalGroup, Region.RegionName)

That, again, passes the syntax checker, but it returns blank.

[AccuFloDev]

Hey @TimRohr,

Good to know it's not just me.

Based on some blog entries I've read, PowerApps is supposed to 'intelligently' decide which columns to select.
In practice however, it does not seem to work very well at all.

[TimRohr]

@AccuFloDev, I just had a conversation with a PowerApps escalation engineer about my issue with LookUp columns returning blank data.

If it helps you, we converted my Lookup() formulas trying to pull in data from the LookUP columns into First(Filter()) statements, and it worked.

So, as an example, if my statement used to be:

Lookup(RegionalGroups,RegionalGroup=GroupID,Region.Region)

We changed it to:

First(Filter(RegionalGroups,RegionalGroup=GroupID)).Region.Region

And that pulled it in.

He said he'd seen where LookUp() doesn't always pull the data properly, and if it doesn't get it, it returns a blank, which is what I was seeing (and it sounds like you were, too).

Try that and see if it helps you.

[AccuFloDev]

Hey @TimRohr,

I did find the same behaviour as you did, where the First(Filter()) method would get what I wanted but not always.
A more reliable method I have found is (if this design is applicable) to do something like this:

1. Use a Gallery control to select your item(s) to be opened in a Form
   
   
2. Make sure the Gallery control template has every LookUp field you intend on using in the form displayed in a Label or similar control (The label does not need to be visible, just part of the Gallery Template).
   
   For example, if an Entity has a One-To-Many relationship, I would show something like Text = Text(CountRows(Entity.YourLookupColumn))
   
   
3. Repeat for every LookUp column necessary
   
   
4. When selecting an item to display on a form, make sure you pass it to the Form using ThisItem.Selected from the Gallery

By using the Gallery Control, PowerApps seems to use the Template to determine what data it needs to pull into the form.  I tested this by using one with the LookUp columns in the template, and one without.  The Gallery without the LookUp columns in the Template consistently returned empty data for Lookups.

Hopefully this workaround helps someone until MS can get the bugs ironed out of the current version.

[TimRohr]

Interesting that First(Filter()) didn't always pull the data for you, either. So far mine has been consistent, but I will have to watch for that.

I worried that a workaround like yours would be the solution. I was really hoping to avoid that length of a workaround just to get consistent returns from the datasource. 

[KelvinBox]

6 months on and this is still a major problem!

It works sporadically and most of the hacks provided by Microsoft to make it work seem to only work 95% of the time... not good enough.

How do we find out if a real fix is being worked on?

[PierreJoubert]

Having the exact same issue here.

The CDS connection is touted as the better option, yet I am unable to retrieve any data that is a lookup.  Always comes through as blank.

At least with the Dynamics 365 connection I get the lookup data, but I lose a ton of capabilities.

When is a Microsoft PowerApps Dev Team resource going to respond to this? Or do I have to log a Service Request?

[KelvinBox]

We have a service request open for it now, just waiting for it to be addressed.

[jzcase]

Reporting same problem here. Not just LookUp() but also ClearCollect() pulls null values for lookup fields! Ridiculous!