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

ForAll - how to access property of current item in iteration?

I have a collection (Collection1, for simplicity) that is a single column of integers with the column name "id".

I have a SQL table with a column that I want to query for matches based on Collection1.

That is, any time the column in the SQL table matches an entry in Collection1, I want to retrieve it. However, to avoid Delegation issues, I cannot use an "in" operator with numerics. Therefore I am using a ForAll statement as seen below:

 

 

 

 

ForAll(
    Collection1,
    Collect(
        Collection2,
        Filter(
            '[dbo].[table]',
            shipper_location_id = Collection1[@id]
        )
    )
);

 

 

 

 

What I expect this to do is return every location from [dbo].[table] where the entry in column "shipper_location_id" matches the id of the item that the ForAll statement is iterating over (Collection1[@id], as suggested by this post). 

Instead, it returns every record from [dbo].[table] where that record's own id (that is, the [dbo].[table].id) is equal to it's shipper_location_id. 

(Edit to add: I have also tried using ThisItem.id, but within the Filter method, ThisItem refers to the items in the collection being filtered, in this case [dbo].[table], and therefore has the same result as Collection1[@id].)

If I rename the Collection1 to altId and replace Collection1[@id] with just "altId", it works. While this solves my problem, renaming a column in my 1st collection seems like an unnecessary extra step. What would the proper syntax for this Filter statement be if both Collection1 and [dbo].[table] have a column with the same name, but I explicitly want to reference that column in the collection I am iterating over, not the collection I am querying?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: ForAll - how to access property of current item in iteration?

Gotcha. I've had this happen before, as well, and we just may need to refine the pattern.

 

What is Collection1 comprised of? Since it's a collection, it is inherently non-delegated. Similarly Collection2 is also subject to the app record limit, as a non-delegable query. Just wanted to point that out, since you mentioned the delegation warning.

 

Therefore, it may just be easier to add a column when collecting into column 1. AddColumn can accomplish this. Call it "appId" and make it the id id. You can then easily disambiguate in the ForAll, or at least ensure you have the right context.

View solution in original post

4 REPLIES 4
Highlighted
Super User
Super User

Re: ForAll - how to access property of current item in iteration?

https://powerapps.microsoft.com/en-us/blog/formulas-thisrecord-as-and-sequence/

 

ThisRecord should be what you need. It isolates the exact record you're looping over, and provides it contextually.

Highlighted
Frequent Visitor

Re: ForAll - how to access property of current item in iteration?

I should have mentioned that I tried ThisRecord as well. Unfortunately, when used within the Filter statement, ThisRecord references the record the records in [dbo].[table]. This is evident both by the results I get and IntelliSense following ThisRecord. suggests columns in the [dbo].[table] that are not present in Collection1.

Highlighted
Super User
Super User

Re: ForAll - how to access property of current item in iteration?

Gotcha. I've had this happen before, as well, and we just may need to refine the pattern.

 

What is Collection1 comprised of? Since it's a collection, it is inherently non-delegated. Similarly Collection2 is also subject to the app record limit, as a non-delegable query. Just wanted to point that out, since you mentioned the delegation warning.

 

Therefore, it may just be easier to add a column when collecting into column 1. AddColumn can accomplish this. Call it "appId" and make it the id id. You can then easily disambiguate in the ForAll, or at least ensure you have the right context.

View solution in original post

Highlighted
Frequent Visitor

Re: ForAll - how to access property of current item in iteration?

Yes, adding another column with a unique name is the workaround I found. I thought I must be missing something given the options for being deliberate about which item I am referencing (also worth noting using the As operator from the link above doesn't work) and there would be a simpler way to do it, but sounds like there might not be so I'll mark this solved.


And thank you for the heads up on the local collection limit.

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (5,430)