cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bogdans
Frequent Visitor

Nested ForAll() and Filter not working

Hello all,

 

I am working with some tables which all have Dataverse as the source and the ForAll() function doesn't seem to work properly.

 

I have a collection of text values called "ProviderKPIS" where I used nested ForAll() loops successfully and stored 17 values. With these values from the "ProviderKPIS" collection I want to filter a dataverse table called 'ESG OS-KPI Values' based on a column called "KPI ID_Text". This "KPI ID_Text" column is a text column that is calculated in Dataverse from a Lookup column "KPI ID" that pulls in the same values that I collected in the "ProviderKPIS" collection. So there is a one-to-many relationship from the "ProviderKPIS" collection to the 'ESG OS-KPI Values' dataverse table. I tried to use both the Filter() and the Search() functions to pull all the records that have the same "KPI ID_Text" values as the values in the "ProviderKPIS" but it always returns 0 records so I'm not sure what I'm doing wrong. Here is the code below:

 

ForAll(ProviderKPIS,
          ForAll(Filter('ESG OS-KPI Values','KPI ID_Text'=Value),
                    Collect(ProviderKPI_Values,ThisRecord)));

 

Below is screenshots of the collection "ProviderKPIS" for reference:

bogdans_0-1636143996048.png

 

And screenshot of the "KPI ID_Text" column in the Dataverse table:

bogdans_1-1636144081096.png

 

Any advice would be helpful!

 

EDIT: 
I also tried just one ForAll() loop and still no success:


ForAll(ProviderKPIS,
           Collect(ProviderKPI_Values,Filter('ESG OS-KPI Values','KPI ID_Text'=Value)));

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
RandyHayes
Super User
Super User

@bogdans 

What you are seeing is correct for a ForAll.  A ForAll is not a For/Loop function.  It is a function that returns a Table.

The first parameter determines the table to iterate over and the second parameter determines the record structure of each record for the table.

So, your first ForAll is iterating a collection, the second parameter you have (the record structure for your table results) is another ForAll.  So that means that there is no defined column name - and PowerApps will just assign it a name of Value.  That column will then have a table with a record structure based on the second parameter - which in your case is a Collect statement - which returns nothing!

 

So your formula should be more along this line:

Collect(ProviderKPI_Values, 
    ForAll(ProviderKPIS,
        Filter('ESG OS-KPI Values','KPI ID_Text'=Value)
    )
)

 

This would give you a collection with a single column called Value that contains a table of the records that match that filter.

 

NOW...that all said, I am guessing that is not exactly what you want.  

 

And since you mention no records are returned, I would start with a testing of the Filter statement by itself.

 

Perhaps put a gallery on the screen and set the Items property to the filter statement and then hard code in one of the values from your other collection in place of Value and see if it returns any results.  If not, then you can troubleshoot back from that.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

WarrenBelz
Super User
Super User

Hi @bogdans ,

Second thought - try this

ForAll(
   ProviderKPIS,
   Collect(
      ProviderKPI_Values,
      'KPI ID_Text'.Text in 'ESG OS-KPI Values'.Value
   )
)

 

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.

Visit my blog Practical Power Apps

View solution in original post

@RandyHayes @WarrenBelz 

 

Thank you for the replies.

 

The mistake was mine, the values I collected in the "ProviderKPIS" collection were not returning any rows because the 'ESG OS-KPI Values' table didn't have any records associated with those ones (data was not populated yet). But now my original code works as desired.

 

Thank you for your detailed answers though, I will accept them both as solutions!

View solution in original post

4 REPLIES 4
WarrenBelz
Super User
Super User

Hi @bogdans ,

Try referring to the output of the text box, but you are only going to get a single field value, all the same

ForAll(
   ProviderKPIS,
   ForAll(
      Filter(
         'ESG OS-KPI Values',
         'KPI ID_Text'.Text=Value
      ),
      Collect(
         ProviderKPI_Values,
         ThisRecord
      )
   )
)

 

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.

Visit my blog Practical Power Apps

RandyHayes
Super User
Super User

@bogdans 

What you are seeing is correct for a ForAll.  A ForAll is not a For/Loop function.  It is a function that returns a Table.

The first parameter determines the table to iterate over and the second parameter determines the record structure of each record for the table.

So, your first ForAll is iterating a collection, the second parameter you have (the record structure for your table results) is another ForAll.  So that means that there is no defined column name - and PowerApps will just assign it a name of Value.  That column will then have a table with a record structure based on the second parameter - which in your case is a Collect statement - which returns nothing!

 

So your formula should be more along this line:

Collect(ProviderKPI_Values, 
    ForAll(ProviderKPIS,
        Filter('ESG OS-KPI Values','KPI ID_Text'=Value)
    )
)

 

This would give you a collection with a single column called Value that contains a table of the records that match that filter.

 

NOW...that all said, I am guessing that is not exactly what you want.  

 

And since you mention no records are returned, I would start with a testing of the Filter statement by itself.

 

Perhaps put a gallery on the screen and set the Items property to the filter statement and then hard code in one of the values from your other collection in place of Value and see if it returns any results.  If not, then you can troubleshoot back from that.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

WarrenBelz
Super User
Super User

Hi @bogdans ,

Second thought - try this

ForAll(
   ProviderKPIS,
   Collect(
      ProviderKPI_Values,
      'KPI ID_Text'.Text in 'ESG OS-KPI Values'.Value
   )
)

 

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.

Visit my blog Practical Power Apps

View solution in original post

@RandyHayes @WarrenBelz 

 

Thank you for the replies.

 

The mistake was mine, the values I collected in the "ProviderKPIS" collection were not returning any rows because the 'ESG OS-KPI Values' table didn't have any records associated with those ones (data was not populated yet). But now my original code works as desired.

 

Thank you for your detailed answers though, I will accept them both as solutions!

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (2,756)