You’re offline. This is a read only version of the page.
Hello,
I'm trying to perform a psudo-join between a cached collection and an sql table.
Share
ReportClearCollect(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
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.
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!
@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'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.
One clarification:
"in" has two behaviors:
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.
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
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.
Hi
What's the warning - is it just that the list could get big and slow down the app performance or somethiong else?
#1
WarrenBelz
146,660
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,999
Most Valuable Professional
All responses (
Answers (