Answered

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

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (2)

Sharon Sumner MVP / RD 78 on at

Like (0)

Report
Copy link

Link copied!

Hi

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Sharon Sumner MVP / RD 78 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

Mr-Dang-MSFT Microsoft Employee on at

Like (4)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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-Sharepoint-List/m-p/267711/highlight/true#M78211

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like (4)

Report
Copy link

Link copied!
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!

Was this reply helpful? Yes No
narwind 2 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No