Filter with Multiple Rows from Collection, ForAll?

[rileyrichards]

Hello,

I have a SQL database with work order line items and are connected by a Work Order Header ID, I use Collections to get the specific data I need from these large databases to use in the app.

I want a Collection with all line items for a Work Order and am able to do this for a single Work Order when I Set a variable and use it in my Filter:

//get Work Order Header ID of Last Entered Work Order Number, Set Variable

Set(LastEnteredWOHeaderID, LookUp('[dbo].[Source1]', WorkOrderNo = LastEntered.WorkOrderNo));

//Collect Work Order line items for all items that match Work Order Header ID

Collect(WODetailsData, Filter('[dbo].[Source2]', WOHeaderID = LastEnteredWOHeaderID))

The above results in showing all line items for that Work Order Header ID.

My problem is I cannot make this same Filter work when I try to pass multiple Work Order Header IDs at the same time using a Collection or a Set variable composed of a table of these values.

If I change = to "in" and reference the collection, I get thousands of results as if my filter doesn't know what to look for even though I try pointing it to the column in the collection containing the Work Order Header IDs.

The best luck I have had so far is using ForAll to iterate through the collection of Work Order Header IDs, but all it does is use the first record value for all and I end up with a collection of repeated line items of a single Work Order for as many rows I have in the collection:

ForAll(WOHeaderData, Collect(WODetailsData, Filter('[dbo].[Source2]', WOHeaderID in WOHeaderData.WOHeaderID)))

I am looking for a formula that would basically do what my first Filter does for each row of my collection of Work Order Header IDs all at once instead of 1 at a time.

Any help would be appreciated, this is my first post - Thank you.

[v-xida-msft]

Hi @rileyrichards ,

Based on the formula you provided, I think there is something wrong with it. Please consider modify your formula as below:

Clear(WODetailsData);
ForAll(
       WOHeaderData, 
       Collect(WODetailsData, Filter('[dbo].[Source2]', WOHeaderID = WOHeaderData[@WOHeaderID]))
)

Please take a try with above solution, check if the issue is solved.

Best regards,

[rileyrichards]

@v-xida-msft Kris,

Thank you so much for your response.

I tried your formula (Copy and Paste) and I ended up with only 1 Work Order's line items, seems like the "For All" did not iterate through all items and instead only looked at the first row.

When I change the formula back it gives me the repeated line items, which confirms I have the expected number of rows in WOHeaderData.

For example, the first Work Order has 19 line items.  When I run your formula I get only 19 rows in the WODetailsData collection.

When I run my formula where I end up with repeated line items, I get 114 rows because there are 6 rows in WOHeaderData: (6 rows * 19 line items) = 114 rows

Thank you again for your quick response, I really appreciate it.

[v-xida-msft]

Hi @rileyrichards ,

Could you please share a bit more about the data structure of your WOHeaderData data source and '[dbo].[Source2]' data source?

Based on the neeeds that you mentioned, I think the solution I priovided above could achieve your needs. Please consider modify your formula as below:

Clear(WODetailsData);
ForAll(
       WOHeaderData, 
       Collect(WODetailsData, Filter('[dbo].[Source2]', WOHeaderID in WOHeaderData[@WOHeaderID]))   /* <-- Find matched records from Source2 table based on each one WOHeaderID from WOHeaderData data source, and save it into the collection  */
)

Or

Clear(WODetailsData);
ForAll(
       RenameColumns(WOHeaderData, "WOHeaderID", "WOHeaderID1"),
       Collect(WODetailsData, Filter('[dbo].[Source2]', WOHeaderID in WOHeaderID1))     /* <-- Find matched records from Source2 table based on each one  WOHeaderID from WOHeaderData data source, and save it into the collection */
)

Please take a try with above solution, check if the issue is solved.

More details about ForAll function in PowerApps, please check the following article:

ForAll function

Best regards,

[rileyrichards · Accepted solution]

@v-xida-msft Kris,

Your second formula worked other than 1 change, you rock!  Thank you.

I think the issue with the original was the formula wanting text values and by recreating the WOHeaderID column with RenameColumns it decided to work!  Great idea.

The only thing I had to change was "in" to "=":

Clear(WODetailsData);
ForAll(
       RenameColumns(WOHeaderData, "WOHeaderID", "WOHeaderID1"),
       Collect(WODetailsData, Filter('[dbo].[WorkOrderDetail]', WOHeaderID = WOHeaderID1))    
)

Thank you for all your help,

Riley