Filter, AddColumns and Delegation

[BenMunk]

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?

[WarrenBelz]

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

[BenMunk]

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?

[WarrenBelz · Accepted solution]

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

[BenMunk]

Thanks so much for your detailed response.

re: Posting code:   noted.  thanks.

[CMOLINAV]

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.

[WarrenBelz]

@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

[CMOLINAV]

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

[WarrenBelz]

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

[CMOLINAV]

Many thanks!!!