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

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

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?

24 REPLIES 24
TimRohr
Level 10

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

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
Level: Powered On

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

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
Level 10

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

@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
Level: Powered On

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

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
Level 10

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

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. Smiley Frustrated

KelvinBox
Level: Powered On

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

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
Level: Powered On

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

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
Level: Powered On

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

We have a service request open for it now, just waiting for it to be addressed.
Highlighted
jzcase
Level: Powered On

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

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

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 223 members 4,627 guests
Please welcome our newest community members: