Understanding delegation of Sharepoint list for a gallery

[SMurphy]

After reading many blogs and watching videos i still slightly struggle understand the delegation. I have an app for our portfolio and project management. I have a tblTasks which is a SharePoint list and is nearing 2000 records. I have a screen which has a gallery and displays tasks for that specific project#. My question is does delegation only affect retrieving data if the formula for the gallery has a delegation warning or regardless will it still have issues due to the 2000 record limit even if it does not have any warnings? Does Dataverse follow the same rules as SharePoint for the 2000 limit? If not could i potentially get a Dataverse license create a gallery that is read only and allow other users to still use the app with the base license and no Dataverse license? 

I know of the solution about creating two collections to increase the limit to 4000 but i fear of it slowing down the app since this app is already quite large. Id assumes the collection would have to reload every time they add a task to display that new task as well. 

So far, the only solution i have thought of is to archive records and direct them to a PowerBi dashboard to review older tasks.

Any answers to my questions and ideas would be appreciated, thanks. 

[BCBuizer]

 Hi @SMurphy ,

Responses in Orange:

After reading many blogs and watching videos i still slightly struggle understand the delegation. I have an app for our portfolio and project management. I have a tblTasks which is a SharePoint list and is nearing 2000 records. I have a screen which has a gallery and displays tasks for that specific project#. My question is does delegation only affect retrieving data if the formula for the gallery has a delegation warning or regardless will it still have issues due to the 2000 record limit even if it does not have any warnings? If there are warnings, definitely there will be an issue. There is a possibility you may be affected without warnings. You can lower the Data Row Limit temporarily to a very low value, like 5, to see if this changes anything: No changes, no propblem. Does Dataverse follow the same rules as SharePoint for the 2000 limit? No, Dataverse has more delegation options, but the sky is not the limit. It depends on your specific situation if Dataverse provides an improvement. If not could i potentially get a Dataverse license create a gallery that is read only and allow other users to still use the app with the base license and no Dataverse license? No, if you use Dataverse as a data source, all users require a per user license or the app needs to be assigned a per app license.

I know of the solution about creating two collections to increase the limit to 4000 but i fear of it slowing down the app since this app is already quite large. Id assumes the collection would have to reload every time they add a task to display that new task as well. Yes, regular refreshes of the collections will be necessary, but this doesn't necessarily have a big impact on the app performance.

So far, the only solution i have thought of is to archive records and direct them to a PowerBi dashboard to review older tasks. A common workaround to evade delegation issues is indeed to use Power Automate to archive items once completed.

Any answers to my questions and ideas would be appreciated, thanks. 

In case you share the code you have delegation issues with, we can see if there is a delegable alternative.

[Drrickryp]

@SMurphy 

Not all functions will show a delegation warning.  The best way to test for delegation is to reduce the row limit to 1 in the Settings and see if your results are affected. It certainly is possible to use large datasets with SharePoint but you have to work around the limit using certain functions and operators creatively.  @WarrenBelz is the master for this.  Otherwise, you can use SQL or Dataverse as the data source as they are far more flexible in that they use almost all of the PowerApps operators and function.

[SMurphy]

Thank you for your response!

This above image is the galleries for the tblTasks.  

SortByColumns(Filter(tblTasks, 'Project#' = CurrentSelection.Text, !IsBlank(Completion_Date)), "Completion_Date", Descending)

SortByColumns(Filter(tblTasks, 'Project#' = CurrentSelection.Text, IsBlank(Completion_Date)), "Expected_End_Date", Ascending)

The first line of code is for the top gallery and the second is for the bottom. The galleries would display 8 records and 6 records. 

After some research i updated the code to the following to fix the delegation issue:

SortByColumns(Filter(tblTasks, 'Project#' = CurrentSelection.Text, Completion_Date<>Blank()), "Completion_Date", Descending)

SortByColumns(Filter(tblTasks, 'Project#' = CurrentSelection.Text, Completion_Date=Blank()), "Expected_End_Date", Descending)

I updated the delegation limit to 6. (Project# I'm using to test is the 6th project in the table.)

The result displayed the same tasks

So i suspect your recommendation is working for all the tasks for this project in tblTasks do not start until the 30th or higher record. 

My main menu which displays the projects typically displays about 350 projects and only displayed 2 after updating the delegation limit so i suspect that worked. 

To me it appears this has worked. In your opinion would you say i should be good to go and not have issues with loading data beyond the 2000 limit as long as i do not change the code for the gallery? Much appreciated. 

[WarrenBelz · Accepted solution]

Thanks for the tag @Drrickryp ,

@SMurphy ,

Firstly, you might start with reading this blog of mine for a basic understanding of some of the issues involved, however you still have a "not" filter in there. You can do this

WIth(
   {
      wData:
      SortByColumns(
         Filter(
            tblTasks, 
            'Project#' = CurrentSelection.Text
         ),
         "Completion_Date", 
         Descending
      )
   }, 
   Filter(
      wData,
      Completion_Date <> Blank()
   )
) 

or you can be a little more devious with dates  (use a date that is old enough)

With(
   {
      wDate:
      DateAdd(
         Today(),
         -5,
         Years
      )
   },
   SortByColumns(
      Filter(
         tblTasks, 
         'Project#' = CurrentSelection.Text &&
         Completion_Date > wDate
      ), 
      "Completion_Date", 
      Descending
   )
)

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

[SMurphy]

Hi @WarrenBelz, thanks for your response. I read your blog and i see you stated all forms of not are non-delegable but in one of your code recommendations i see you use <>, could you explain this to me? 

WIth(
   {
      wData:
      SortByColumns(
         Filter(
            tblTasks, 
            'Project#' = CurrentSelection.Text
         ),
         "Completion_Date", 
         Descending
      )
   }, 
   Filter(
      wData,
      Completion_Date <> Blank()
   )
) 

Thanks. 

[WarrenBelz]

Hi @SMurphy ,

Please also have a read of this blog as it relates to the first option I posted. I have "pre-filtered" in the top With() statement - this however still needs to return record numbers under your Delegation limit (you can start with any amount of records), then the bottom filter is processed "locally" using the table generated from the top filter. Also (as I assume) Created is a date field, you can try the second option I posted.

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

[SMurphy]

Thanks again, so if i am understanding this correctly the With() statement is querying SharePoint based on the project# and returning values for tasks for that project # and assuming a project wouldn't have 2000+ tasks. Then based on this "temporary collection" you are using the filter function to filter that data more and at this point it wouldn't matter if that formula on non-delegable? 

Last question is why would this following formula not show a delegation warning if i am using the <> clause?

SortByColumns(Filter(tblTasks, 'Project#' = CurrentSelection.Text, Completion_Date<>Blank()), "Completion_Date", Descending)

Thanks a lot! 

[WarrenBelz]

@SMurphy ,

First part absolutely correct. Second part - "by the book" it should show a Delegation warning as <> is not Delegable and I also did a test here and it appears a Delegation warning is issued for everything but date fields. This may be a bug and I personally would not rely on the lack of a warning and assume it is Delegable when there are other options that I know will work.