Answered

How to avoid delegation warning and enable search and filter for more than 2000 records

(1) Share

Report

Posted on by udaniweeraratne

Hi,

I am trying to implement the search function on my help desk tool. But I am unable to figure out how to work around this delegation warning while achieving my goal. I need to be able to display only the records related to current user and use the text the given in the search box to run a contains search/filter on the Title column of my datasource. I am using a SharePoint list as my datasource.

Given below is the condition I am using in the Items section in my gallery. But I am getting delegation warning for both && and in (underlined in blue)

SortByColumns(
 If(TextSearchBox1.Text<>"",
 Filter('tracker', UserEmail=IssueLoggedByEmail && TextSearchBox1.Text in Title), 
 Filter('tracker', UserEmail=IssueLoggedByEmail)
 ), 
 "ID", 
 If(SortDescending1, SortOrder.Descending, SortOrder.Ascending)
)

Warning: Delegation warning. The highlighted part of this formula might not work correctly with column "Title" on large data sets.

Appreciate, if someone can help me to resolve this. I have been looking for solutions for past few days. It seems Search function and in is not delegatable. But, how can we handle this kind of situation?

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Verified answer

LaurensM 12,516 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @udaniweeraratne ,

You can prefilter the data via email to make sure the dataset is below 2000 and then search via the Search() function:

SortByColumns( Search( Filter('tracker', UserEmail=IssueLoggedByEmail), TextSearchBox1.Text, "Title" ), "ID", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending) )

As long as the Filter() returns less than 2000 records, you should be fine. 😊

If this solves your question, would you be so kind as to accept it as a solution.

Thanks!

Was this reply helpful? Yes No
Pstork1 69,657 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Unless you are going to have more than 2,000 tickets per user the way to fix this is by doing an inner delegable filter and an outer search. Like this

SortByColumns( If(TextSearchBox1.Text<>"", Filter(Filter('tracker', UserEmail=IssueLoggedByEmail), TextSearchBox1.Text in Title), Filter('tracker', UserEmail=IssueLoggedByEmail) ), "ID", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending) )

You may still get a delegation warning. But since you are filtering by userEmail first you'll be below the 2,000 limit before the non-delegable filter is applied.

Was this reply helpful? Yes No
udaniweeraratne 25 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks @LaurensM and @Pstork1 for the prompt reply. Hope you wouldn't mind helping me clear one more doubt. Based on both of your comments, what I understood was, although we set the no delegable record count to 2000 in the app, the first Filter function which filters the datasource records by the UserEmail will not consider the 2000 record limit, and it will run the initial filtering in the SharePoint side and return only the records related to the given user. After that Search will happen on the app side.

Therefore, we are having more than 2000 records in the microsoft list wouldn't be a problem to the initial filter, it will run through all the records in the list. But, if the filter result count is more than 2000, the Search function will receive only the first 2000 records of the Filter output.

Am I understanding this correctly?

Was this reply helpful? Yes No
LaurensM 12,516 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @udaniweeraratne,

Yes that is correct. The first filter (via text fields) is delegable and will search the full list.

The Search function will only search through the filtered dataset, which should be below 2000. Everything over 2000 will be ignored by the Search function. (to be more technically correct: the filter can only output 2000 records so the Search() function will simply not receive the 2001st filtered record)

Should your filter return more than 2000 records, then you will have to look at ways of extending your inner filter to make it more specific (e.g. only the active tickets for this user...).

I hope this helps!

Was this reply helpful? Yes No
Pstork1 69,657 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

@udaniweeraratne not quite. Let me explain further. The inner Filter() statement will abide by whatever data row limit you have set in the app. The default is 500 but can be increased to 2,000. Since the inner filter is delegable it will filter all the records, but it will still return a maximum for whatever the data row limit setting. So if you had 600 records for a user and leave the data row limit at 500 you will only get 500 records to be searched by the outer function. If you change the limit to 2,000 the inner filter will return all 600 to be searched. But as long as the number of records returned by the inner filter statement doesn't exceed the data row limit you will be able to search all the records even if there are more than 2,000.

Was this reply helpful? Yes No