Filter, AddColumns and Delegation

(0) Share

Report

Posted on by BenMunk

114

I'm trying to join 2 lists and filter where there are no matches.

Currently I'm doing this below. I'm expecting 400 rows, but I'm getting 50. I suspect it has something to do with delegation?

Since Filter and AddColumns are delegable, shouldn't this work?

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

WarrenBelz 154,797 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @BenMunk ,

I would suspect it has everything to do with Delegation, AddColumns is a "hidden" Delegation as it will only act on and return records up to your Delegation limit and the Relational Lookup to the other list is also not Delegable (SharePoint is not a Relational Database). If you had some sort of "pre-filter" on list A to get the numbers under 2000 records, it is doable.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
BenMunk 114 on at

Like (0)

Report
Copy link

Link copied!

Thanks Warren,

SharePointListB has 10,000 rows.

I thought since AddColumns was wrapped in Filter...

Delegate AddColumns function > Sharepoint creates "temp table" with 10,000 rows > Filter function runs on "temp table" > Sharepoint Returns records up to the 2000 delegation limit.

How does it actually work?

Was this reply helpful? Yes No
Verified answer

WarrenBelz 154,797 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @BenMunk ,

Firstly, can you please post any code in Text as well - saves responders a lot of typing.

I updated my post while you were responding, but to answer your question the filter is not Delegable - a Lookup to another list is relational and SharePoint will not accept Delegation on relational queries. Also using your logic, the maximum "temp table" you can return to process "locally" (this applies to the With() function as well) is your Delegation limit. As I mentioned, if you can "pre-filter List A with something Delegable it can be done. Another possibility is if the Newest 2,000 records will do the job. - note below I must query your ID filter as it is suggesting the ID in List A and List B are the same.

With( { wList: Sort( SharePointListA, ID, Descending ), wListB:SharePointListB }, ClearCollect( colTemp( AddColumns( wList, "Reports_ID", LookUp( wListB, ID = wList.ID && ReportsID = Blank() ) ) ) ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
BenMunk 114 on at

Like (0)

Report
Copy link

Link copied!

Thanks so much for your detailed response.

re: Posting code: noted. thanks.

Was this reply helpful? Yes No
CMOLINAV 108 on at

Like (0)

Report
Copy link

Link copied!

Hello Warren,

You say that if you pre-filter with something delegable it will be ok, but I don't see how to do it. For example, on a gallery:

With(
{
wList: listSharepoint
};
Filter(listSharepoint;Field="MOLINA")
)

That returns me only the limite delegable configured on PowerApps. If I configure on 1 to test, it is retrieve me 1 record.

Was this reply helpful? Yes No
WarrenBelz 154,797 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
@CMOLINAV ,

With() is subject to Delegation limits on the output, so if you set you limit to 1, it will return one record. However setting your limit back to 500 (or whatever) and doing something like this

With( wList: Filter( SPList( Field = "MOLINA" ) }, Search( wList, SearchBox.Text, "Title", "OtherField" ) )

and you have 2,000 records, will allow up to 500 of these (where field is MOLINA) to be then searched (a non-Delegable filer) whereas

Search( Filter( SPList( Field = "MOLINA" ) }, SearchBox.Text, "Title", "OtherField" )

is not Delegable and you could only address the first 500 records of the list with it,

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
CMOLINAV 108 on at

Like (0)

Report
Copy link

Link copied!
Hello Warren,

I think I understand you after read your blog and your answer.

Even if I previously used a delegable function (Filter), if later I use a non-delegable function, the result is non-delegable and could be wrong according delegation limit. For example Filter+AddColumns or Filter+Search…

But If I use before With+Filter and I make sure that the filter always returns less than the delegation limit (500-2000), I could use non-delegable functions without worry, the data will be correct, for example:

With( wList: Filter( ) // I have to make sure that Filter returns less than the delegation limit (500-2000) }, Search / Add Columns /...( wList, ... ) )

Please tell me if my conclusions are correct.

Many thanks for your help and contributions to the community!!!

Was this reply helpful? Yes No
WarrenBelz 154,797 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@CMOLINAV ,

Correct - actually Delegation does not come into play at all as the With() statement returns a Table Variable (call it a collection if you want to) and the subsequent actions are performed "client-side" or locally and there are no further calls to the data source, so you can use any valid filter/lookup you want to and it will work fine.

Was this reply helpful? Yes No
CMOLINAV 108 on at

Like (0)

Report
Copy link

Link copied!

Many thanks!!!

Was this reply helpful? Yes No