Skip to main content

Notifications

Community site session details
Power Apps - Building Power Apps
Answered

Common Data Service Returns no data from a Lookup column

Like (1) ShareShare
ReportReport
Posted on 24 Apr 2019 15:23:07 by 15

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.

  • bkumar Profile Picture
    46 on 22 Jul 2023 at 15:58:40
    Re: Common Data Service Returns no data from a Lookup column

    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.

  • AlexLindberg Profile Picture
    87 on 08 Oct 2021 at 01:10:51
    Re: Common Data Service Returns no data from a Lookup column

    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

    AlexLindberg_0-1633655355079.png

    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.

     

  • gdiegoli Profile Picture
    6 on 01 Jun 2020 at 11:49:34
    Re: 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.

  • Helpful Profile Picture
    382 on 21 Oct 2019 at 19:15:43
    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.

  • eselover Profile Picture
    15 on 21 Oct 2019 at 16:49:10
    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 Profile Picture
    382 on 21 Oct 2019 at 14:52:45
    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-data-service-views/

  • eselover Profile Picture
    15 on 25 Apr 2019 at 13:37:26
    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! 

  • Verified answer
    Community Power Platform Member Profile Picture
    on 24 Apr 2019 at 22:12:38
    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.

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,763 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,091 Most Valuable Professional

Leaderboard