cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

7 REPLIES 7

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (9,256)