Issue with "in" operator inside "Filter()" function using delegation

[Anonymous]

Hello, 

I'm trying to perform a psudo-join between a cached collection and an sql table.

ClearCollect(Collection_A; Filter('[dbo].[Table_A]'; Col_A_ID in ListOfIds));;

ClearCollect(Collection_B;
    Filter('[dbo].[Table_B]'; Col_A_ID in Collection_A[@Col_A_ID])
);;

But there is a warning about delegation at the "in" operator.
As I've read the documentation [1], there shouldn't be any issue using the "in" operator inside a "Filter()" function for a SQL DataSource.
The first statement returns the correct expected values, but the second one doesn't.

Can someone help me?

[1]-https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

[sharonsumner]

Hi

What's the warning - is it just that the list could get big and slow down the app performance or somethiong else?

[Anonymous]

Forgive my translation, as the error message is not in english, but the message roughly says:

Delegation warning. The highlighted part of this formula might not work correctly for big data sources. Right side of "in" operator must be a column name from a correct data source.

---

And the highlighted parts are the "in" operators.

[sharonsumner]

As per the delegation control notes you referred to this is just a warning, the App may always function correctly if your data stays small but the moment you get above 500 records it will not return record 501 and will therefore appear not to work / will give odd results. 

Will your data get this big - if so then you need to rethink a little

Sharon

[Mr-Dang-MSFT · Accepted solution]

One clarification: 

"in" has two behaviors:

• String search: Is a certain text inside a certain other text string?
• Membership: Is a given record inside a table of records?

Membership is not delegable today, but the string search is delegable with SQL.

So something like this would be delegable with SQL:

Filter(dbo.table,"find_me" in column_name)

This would attempt to return records where the string 'find_me' is in the given column of type NVARCHAR (text).

Filter(dbo.table, column1 in collection.column2)

This one means, "Filter the table where the content in column1 of the table is an entry in column2 of the collection." You'll receive some results, but it would not be delegable.

Let me know if this made sense.

[Anonymous]

I'm experiencing a similar issue with delegation - I have another thread which I've lined below.

I am trying to filter sharepoint based on a Folder Path with '=' or 'In' and am told 'Folder Path' has a delegation warning even though MS documentation says that it is a string.

https://powerusers.microsoft.com/t5/General-Discussion/Delegation-not-working-with-Filter-on-Sharepo...

[Anonymous]

@Mr-Dang-MSFT  Thank you very much, this is the distinction I wanted to find out.
I'm creating a app that needs to work offline, so I make all queries at OnStart, but there is a lot of data to gather.
My idea was to make psudo-join using the ids and the "in" statement.
I have a collection of ids and a SQL table of corresponding items (One-To-Many), how can I gather that information on PowerApps?

[Anonymous · Accepted solution]

I managed to do what I wanted by using a "ForAll()" function and iterating over the list of IDs, then collecting each result by using the "Filter()" function with a "=" operator.

Clear(JoinedTable);;
ForAll(
    TableOfIDs
    Collect(JoinedTable;
        Filter(dbo.Table_A; Column_ID = Result)
    )
)

Thanks everyone who replied!

[narwind]

My concern with this solution is that each loop of the ForAll function will count as a separate API call from PowerApps. If you are looping through 1,000 IDs, that's 1,000 API calls. Depending on how much data you have, this could quickly eat into the user's API limits for PowerApps licenses. It's not to say that I have a better solution, just something to be aware of.