cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LucasCSousa
Advocate I
Advocate I

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

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

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

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.

View solution in original post

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!

View solution in original post

8 REPLIES 8

Hi

 

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

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.

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

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.

View solution in original post

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...

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

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!

View solution in original post

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.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,918)