cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

CDS Lookup & ForAll?

Dear community


I have two tables:

  • Users (Standard in CDS - including all Users. I have added a custom column called "Rate" to it)
  • School (including a column "User" that is a LookUp to the "Users" table)

In my ForAll() function, I'm looping through all "School" Items and would like to add them to a  collection, including the "Rate" column from the "Users" table.

ForAll(
    Filter(
        'School',
        Date > Date(2020,12,1) && Date < Date(2020,12,31)
    ) As School,
    Collect(
        colResults,
        {
            Name: School.Name,
            Rate: School.User.Rate,
            Qty: School.Hours
        }
    )
);

 

Now for some reason my "Rate" column in "colResults" is empty. It doesn't throw an error. 

For testing purposes, when I'm trying to get the "Rate" column with something like this, it works seamlessly:

LookUp(Users, User = First(School).User).Rate

 

Any idea why that is?

2 ACCEPTED SOLUTIONS

Accepted Solutions

@mrQ ,

Without seeing your data, this is difficult, what is in the School.User field and how does this match Users.User?

If you do two collections

ClearCollect(colSchool,School);
ClearCollect(colUsers,Users)

then View > Collections, what do you see in the two User fields?

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

Hi @WarrenBelz 

 

Don't know exactly what happened, but I posted my reply to this post a few hours ago just to realize that somehow it disappeared.

 

In my previous post I was mentioning that it does not add the lookup values to the collection. It's just empty.

 

A few minutes ago I found out that it's due to the As function, as it somehow is not a reference, but rather a temporary copy of the source. Meaning, it won't add the values of the the CDS LookUp fields into that temporary collection. 

 

As a workaround, I used the following formula. Note the Schools@[User].User part:

 

Clear(colResults);
ForAll(
    Schools,
    Collect(
        colResults,
        {
            Name: Name,
            Rate: LookUp(Users, User = Schools[@User].User, 'Billable Rate'),
            Qty: Hour,
            User: User.User
        }
    )
);

 

 

With above it works great. 

 

Thanks for your effort and your replies. While your answer wasn't directly the solution, it certainly helped to find it. So I'm going to mark it as a solution anyway 🙂 Thanks for all your help and happy new year!

 

*** EDIT ***

Just found out that when I leave out User.User in Collectit still won't work. Somehow, only when <lookup field>.<unique identifier of lookup table> is present, it can successfully get all the other values from it in another call (e.g. in my case Rate). This seems like a bug to me that MS needs to fix....

 

View solution in original post

7 REPLIES 7
Super User III
Super User III

Hi @mrQ ,

Does this work for you?

ForAll(
   Filter(
      'School',
      Date > Date(2020,12,1) && Date < Date(2020,12,31)
   ) As School,
   Collect(
      colResults,
      {
         Name: School.Name,
         Rate:
         LookUp(
            Users, 
            User = School.User
         ).Rate,
         Qty: School.Hours
      }
   )
)

Also Date is a very bad title for a field as it is a Reserved Word in Power Apps and can easily lead to ambiguity and unexpected results.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Thanks for your reply @WarrenBelz 

 

Unfortunately, it still doesn't show anything, even with the LookUp.

Do you have any other ideas why? 

 

 

HI @mrQ ,

I just spotted something else - assuming your code produces no errors, it is valid, except you have used the same name to label the As statement as the list name - this will lead to ambiguity (which is the principle that the As statement tries to avoid) - so does this work? 

ForAll(
   Filter(
      'School',
      Date > Date(2020,12,1) && Date < Date(2020,12,31)
   ) As aSchool,
   Collect(
      colResults,
      {
         Name: aSchool.Name,
         Rate:
         LookUp(
            Users, 
            User = aSchool.User
         ).Rate,
         Qty: aSchool.Hours
      }
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

Hi @WarrenBelz 

Thanks for your reply.

Unfortunately not. Still no Rate.

 

@mrQ ,

Without seeing your data, this is difficult, what is in the School.User field and how does this match Users.User?

If you do two collections

ClearCollect(colSchool,School);
ClearCollect(colUsers,Users)

then View > Collections, what do you see in the two User fields?

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

Hi @mrQ ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

Hi @WarrenBelz 

 

Don't know exactly what happened, but I posted my reply to this post a few hours ago just to realize that somehow it disappeared.

 

In my previous post I was mentioning that it does not add the lookup values to the collection. It's just empty.

 

A few minutes ago I found out that it's due to the As function, as it somehow is not a reference, but rather a temporary copy of the source. Meaning, it won't add the values of the the CDS LookUp fields into that temporary collection. 

 

As a workaround, I used the following formula. Note the Schools@[User].User part:

 

Clear(colResults);
ForAll(
    Schools,
    Collect(
        colResults,
        {
            Name: Name,
            Rate: LookUp(Users, User = Schools[@User].User, 'Billable Rate'),
            Qty: Hour,
            User: User.User
        }
    )
);

 

 

With above it works great. 

 

Thanks for your effort and your replies. While your answer wasn't directly the solution, it certainly helped to find it. So I'm going to mark it as a solution anyway 🙂 Thanks for all your help and happy new year!

 

*** EDIT ***

Just found out that when I leave out User.User in Collectit still won't work. Somehow, only when <lookup field>.<unique identifier of lookup table> is present, it can successfully get all the other values from it in another call (e.g. in my case Rate). This seems like a bug to me that MS needs to fix....

 

View solution in original post

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (48,446)