Nested ForAll() and Filter not working

[Anonymous]

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:

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

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)));

[WarrenBelz]

Hi @Anonymous ,

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 · Accepted solution]

@Anonymous 

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.

[WarrenBelz · Accepted solution]

Hi @Anonymous ,

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

[Anonymous · Accepted solution]

@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!