Filtering Datasource based on Collection

(0) Share

Report

Posted on by mathewschultz

I’m trying to filter a gallery by collection with a connection to an sql database.

Here’s the flow:

End user selects from a group of sales orders (screenshot #1)
These sales orders are placed into a collection
I want to take that list of sales orders and pull the associated items from them and put them into a new gallery

I can get the sales orders in the collection fine, but can’t get the second gallery to show the sales order items based on the collection- it returns no records.

I can do this by individual sales order with this code: Search([@'[dbo].[v_PBI_CustomerEquipment]'],tsbSONumber.Text, "SONumber")

Here’s the code using the collection: Filter([@'[dbo].[v_PBI_CustomerEquipment]'],SONumber in SOCollection.SONumber)

Powerapps is showing a delegation warning, but the gallery is not displaying the sales order items.

Below are the screen shots-

#1 shows collecting the sales order numbers into my collection

#2 is where I am expecting those sales order details to show

#3 represents this working while using a single sales order.

pa collectionissue.png

Categories:

Building Power Apps

All responses (3)

Answers (0)

Sort by

GarethPrisk 2,828 on at

Like
a
(0)

Report
Copy link

Link copied!

As you noted, the In query parameter is finicky and non-delegable.

Another route would be to loop through your first collection, and for each loop, collect into the second collection with an explicit filter. This is also non-delegable, but you could guarantee up to 2000 records per loop.

Example (excuse bad syntax, on phone)
ForAll ( colOne, Collect ( colTwo, Filter ( sqlSource, sqlColumn = colOne.Column ) ) )

That would get all columns from SQL, but could be pared back. Make sure to clear the colTwo before repopulating it. Or RemoveIf in colTwo if an item in colOne is removed.
mathewschultz 4 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for the response. I'm trying to wrap my head around this and need a bit of clarity.
Sales Orders(SO) collection looks like this SO1, SO2 and SO3.
Sales Order Equipment (SOE) datasource would be SO1[1,2,3], SO2[4,5,6], and SO3[7,8,9] (equipment in brackets)

With the ForAll statement, I would need to use SOE as colOne, and SO and colTwo correct? Or would ForAll evaluate all of the SOE items if I used SO as colOne?
Seems like I would need all of the equipment and filter by SO.

If the above is correct, wouldn't I need to pull the data out of the table and put it into a collection to evaluate it or can I evaluate like this: ForAll([sqldatasource], Collect(SO, Filter(sqldatasource,column1, SOcolumn1)))?

Thanks much,
mathewschultz 4 on at

Like
a
(0)

Report
Copy link

Link copied!

I've been playing around with this and I can't get the formula right. Here's what I have:

ForAll(colSOCollection, Collect(colCustomerSalesOrderEquipment, Filter('[dbo].[v_PBI_CustomerEquipment]', SONumber = colSOCollection.SONumber)))

colSOCollection is the collection of all of my sales orders
colCustomerSalesOrderEquipment is a new collection I want to deposit all of the items associated with the colSOCollection collection
v_PBI_CustomerEquipment is the view on the sql datasource with all of the equipment.

But PA keeps telling me that the '=' is an invalid argument. It still accepts the 'in' parameter.

Appreciate any insight - thanks,