Answered

Limit exceeds threshold error

(0) Share

Report

Posted on by lakka00

I have been using the following code about 6 months with no issues (the date has always been over 5k) and it has suddenly stopped working, no reason as to why. Can anyone help? (It has multiple filters and a search bar) I have tried general troubleshooting but cant get it to work again.

Search(
If(
And(
IsBlank(Dropdown1.Selected.Value),
IsBlank(DatePicker1.SelectedDate)

),
Sort('Call Log', Created, SortOrder.Descending),
If(
And(
Not(IsBlank(Dropdown1.Selected.Value)),
Not(IsBlank(DatePicker1.SelectedDate))

),
FirstN(
SortByColumns(
Filter(
'Call Log',
Dropdown1.Selected.Value = 'Agent Name'&&
Created >= DateTimeValue(DatePicker1.SelectedDate) &&
Created <= DateAdd(DateTimeValue(DatePicker1.SelectedDate),1,TimeUnit.Days)
),"Created"
,
SortOrder.Descending
),
2000
),
If(
Not(IsBlank(Dropdown1.Selected.Value)),
FirstN(
SortByColumns(
Filter(
'Call Log',
Dropdown1.Selected.Value = 'Agent Name'
),
"Created",
SortOrder.Descending
),
2000
),
If(
Not(IsBlank(DatePicker1.SelectedDate)),
FirstN(
SortByColumns(
Filter(
'Call Log',
Created >= DateTimeValue(DatePicker1.SelectedDate) &&
Created <= DateTimeValue(DatePicker1.SelectedDate)+1

),
"Created",
SortOrder.Descending
),
2000))
)
)),'search all'.Text,"Title")

Categories:

Building Power Apps

All responses (6)

Answers (1)

Sort by

Verified answer

WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @lakka00 ,

Two things here - your code should be able to be condensed as below as with your Delegation limit to 2,000, that is the maximum you are going to get anyway, however I have also tried to keep this partially Delegable to maximise what those 2,000 items contain after the filter and before the Search

With( { wDate: DateAdd( DatePicker1.SelectedDate, 1, TimeUnit.Days ) }, With( { wData: Filter( Sort( 'Call Log', Created, SortOrder.Descending ), ( Value(DatePicker1.SelectedDate) < 1 || ( Created >= DatePicker1.SelectedDate && Created <= wDate ) ) && ( Len(Dropdown1.Selected.Value) = 0 || 'Agent Name' = Dropdown1.Selected.Value ) ) }, Search( wData, 'search all'.Text, "Title" ) ) )

Secondly, you need to ensure the following columns are indexed in SharePoint - 'Agent Name', Created, Title

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
lakka00 16 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi, thank you, however, this has not worked. I am struggling to understand how it worked for months and just today has broken. The error is only this line Sort('Call Log', Created, SortOrder.Descending), if I remove the sort it has no issues displaying data but it doesn't seem to be able to use the sort to display the most recent 2000 records anymore. I hadn't made any changes but did change the list view on SP then changed it back to the default descending view, I don't see how this would have done anything but then again it is power apps so I wouldn't be surprised.
WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @lakka00 ,

I have dealt with using large SharePoint Lists in Power Apps for about 5 years now (I am exclusively SharePoint) and that specific error (which I have encountered on numerous occasions) inevitably comes down to not having a column you are querying indexed in SharePoint. So do you have 'Created' in your indexed columns list ?
lakka00 16 on at

Like
a
(0)

Report
Copy link

Link copied!

I did not, I have now and it is currently indexing, once done will it start working or is there anything to do in addition?
lakka00 16 on at

Like
a
(0)

Report
Copy link

Link copied!

This has worked! Thank you so much.
mychau 56 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you so much sir!!! I have been cracking my brain over the empty gallery without knowing why... I confirmed the index columns are the correct solution