Delegation warning for Sharepoint Lists

[LeoGS]

Hello all,

I have been searching for the solution of my problem, but no example I've seen this far solves my problem.

Long story short:

• Sharepoint List / Database
• I know from here (https://docs.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-ope... that Filter and Sort functions are delegable
• My formula:
  
  FirstN(
  Sort(
  Filter( dbo_Audits,
  searchAuditorField.Text in UserName && searchBusinessUnitField.Text in BusinessUnit && searchAuditField.Text in Audit
  ),
  DateTime, Descending
  ), 50
  )
  
  
• I am getting the warning:
  

  This sounds bad.

Could somebody please tell me what is going on? For instance, my formula is actually pretty simple.

Thank you a lot!!

[LeoGS]

I just found out that the problem was with the "in" operator. I didn't even know that "in" was considered an operator, as it is the same for me as "=". I just replaced "in" for "=" and the delegation warning disappeared.

The problem now is different. How can I perform a search in a gallery according to a text field without using "in" ? The problem with "=" is that if the search field in Empty, it shows no item in the gallery.

[RandyHayes]

@LeoGS 

Delegation warnings are just that...a warning.  You need to assess the situation based on your knowledge of the data.  If the datasource you are trying to filter is expected to be more than 2000 record (the limit), then you need to consider another approach.  If it is not expected to exceed 2000, then you can ignore the warning and set your record limit accordingly.

If delegation is a concern (more than 2000 records expected), then consider if there is a narrowing criteria that you can use to filter the data.  Example - within a date range, based on a status or field value, etc.

If those exist, then you can use that criteria to prefilter the data and then use the in operator against those prefilter results without any delegation issues.

I hope this is helpful for you.

[LeoGS]

Hey @RandyHayes ,

thanks for your reply!

Delegation is definitely a concern, because I am going way above 2000 records. I decided to use "StartsWith" instead of the "in" operator to make this query. It is not EXACTLY the same, because with "in" the program will still find entries which have the searched term in the middle of it (for example, searching "house" in "tankhouse"), while with StartsWith the example given would not return anything. But it is good enough!

Thanks your the reply again!

[RandyHayes · Accepted solution]

@LeoGS 

Yes, the in operator will search anything in the text while the StartsWith will only return true if the text starts with that value.

Don't rule out the in operator though as you can still use this if you have a prefilter condition.  In other words, although you cannot throw the in operator at the datasource itself, you can throw it at the results of the filtered datasource.

So, for example, if you have a list with 15k items and they have a column for date and status. Then you can consider prefiltering.  For example, if the real requirement is that the in operator only return records that are within the past month and have a status of open, then consider the results of that.  Those two criteria are all delegable.  So, if the result of a filter like : Filter(datasource, DateColumn>=DateAdd(Now(), -1, Months) && Status="Open") would return less than 2000 records as a result, then this can be a prefilter.

Ex.

With({_prefilter: Filter(dbo_Audits, DateColumn>=DateAdd(Now(), -1, Months) && Status="Open")},
    Filter(_prefilter, 
        searchAuditorField.Text in UserName &&
        searchBusinessUnitField.Text in BusinessUnit &&
        searchAuditField.Text in Audit
    )
)

The above would work perfectly fine and not have delegation issues.

The other consideration is to determine if you have "standing" values in your columns.  For example, I see there is a BusinessUnit field.  Perhaps you have a fix set of business units.  If so, then consider a dropdown list rather than a textinput control.  That would then be delegable.

[LeoGS]

@RandyHayes, thank you, this is exactly what I need to overcome this problem. I marked it as solution and hope it can help other people further.

[RTanka]

Hello,

Thanks a lot @RandyHayes for providing a workaround for pre-filtering.

I have tried this and found that the Delegation warning has gone, but if I set 'Data row limit' for connections where the delegation is not supported, still I receive limited number of records.

Is there anything else that needs to be considered as looking at the topics about delegation, this is one way of checking if the issue is gone.

[LeoGS]

Hello @RTanka ,

Could you please give more details? What database are you using that "delegation is not supported"?

As you said yourself, if your database does not support delegation, results WILL be limited by the number you set at "Data row limit". To overcome this, change your data source to a kind that accepts delegation and use "delegable" formulas when dealing with such a database.

Please note: a certain formula can be delegable for some data sources and NOT delegable for others. You have in the end to make sure you are using a delegable Data source AND THEN check which formulas are delegable for this data source.

Please see below four links for four databases containing which formulas are delegable for them. I hope you are able to use one of these:

• Power Apps delegable functions and operations for Microsoft Dataverse
• Power Apps delegable functions and operations for SharePoint
• Power Apps delegable functions and operations for SQL Server
• Power Apps delegable functions and operations for Salesforce

Regards.

[RTanka]

Hi @LeoGS ,

Thank you, I am all aware of this, but you are right I forgot to mention in my case it is a Sharepoint-based data source. And I checked the delegable functions accordingly.

So my problem had a different reason. And actually, it is the past because my test method was wrong, sorry.

I will write it down so that others studying could have a reference.

So I created the pre-filter as described in this thread, and limited the data rows in Power Apps settings. But I limited it so much that all the matches were cut, too.

So the right way is to create sample data that contains X matches, and to add Y others that will not give match after the filter applies.

Then you should set the data row limit between X and X+Y (what I did wrong was setting the row limit less than X).

It seems obvious, but for the first sight I did not see it.

Thanks, all good now.