Threshold error: In long time exisiting app / no changes to filter formula or data structure.

(1) Share

Report

Posted on by Jbeau83

Greetings,

I've been banging my head against the wall since yesterday. Any help would be appreciated.

I have a filter formula for a gallery , using a large list (15k plus records)via SPList. The filter argument works (including deligation restrictions based on the date range). It is a formula i've used in this app for nearly 2 years (With help from this very community).

Filter Forumla for Gallery:

With(
    {
        wList: SortByColumns(
            Filter(
                DATABASE,
                (aDropBox_2.SelectedText.Value = Blank() || Market = aDropBox_2.SelectedText.Value) && (bDropBox_2.SelectedText.Value = Blank() || Region = bDropBox_2.SelectedText.Value) && SupportDate >= StartDateNew_1.SelectedDate && SupportDate <= EndDateNew_1.SelectedDate),
            "SupportDate",
            SortOrder.Descending,
            "Title",
            SortOrder.Ascending
        )
    },
    If(
        CountRows(wList),
        Filter(
            wList,
            TextSearchBox1_2.Text in Title || TextSearchBox1_2.Text in UserName || TextSearchBox1_2.Text in SiteName || TextSearchBox1_2.Text in SupportMethod),
        wList
    )
)

The issue is that when one option from aDropbox is selected (specifically 1 option) it throws the following error:

The requested operation is invalid.
Server Response: DATABASE failed: The attempted operation is prohibited because it exceeds the list view threshold.

However every other option in aDropBox works perfectly fine and always has. Nothing on my side code wise changed, nor the database itself.

The question is why one option is throwing that error , from the same element to the same output when every other one does not. Any insight would be most appreicated.

All responses (6)

Answers (2)

Suggested answer

CA1105 499 on at

Like (0)

Report

Threshold error: In long time exisiting app / no changes to filter formula or data structure.

Indexing could be issue here since SP list view have 5k threshold limit and after that it'll start giving the errors on the app.
WarrenBelz 147,610 Most Valuable Professional on at

Like (1)

Report
Threshold error: In long time exisiting app / no changes to filter formula or data structure.

Hi @Jbeau83,

I will offer some thoughts here - the last part of your formula
If( CountRows(wList), //is what ?? Filter( wList, TextSearchBox1_2.Text in Title || TextSearchBox1_2.Text in UserName || TextSearchBox1_2.Text in SiteName || TextSearchBox1_2.Text in SupportMethod ), wList //if not what ?? )

The first part, with the CountRows has no criteria (does it need to be equal to or greater/less than something) ? The second is that there is no alternative if this test fails. I suspect the relevant filter at the top is not meeting the test and returning a error that is a bit misleading (addressing a non-indexed column in some way), which is why is worked until the record numbers exceeded 5,000.
IAmIntractable 270 on at

Like (0)

Report

Threshold error: In long time exisiting app / no changes to filter formula or data structure.

Since you know the specific option that is having an issue, do you know exactly how many rows it will return? Either it is exceeding the a limit or perhaps the way its being interpreted incorrectly resulting in more rows than you expect. It's actually good it only happens for one option as you should be able to create a list view to see what happens, or pull the data into Excel or MS Access to review.
adedapo 256 on at

Like (0)

Report
Threshold error: In long time exisiting app / no changes to filter formula or data structure.

Your data has grown in such a way as one option on the Market column is now more than 5000 items. Try moving

(aDropBox_2.SelectedText.Value = Blank() || Market = aDropBox_2.SelectedText.Value)

to the end of the filter.
Verified answer

Jbeau83 35 on at

Like (0)

Report
Threshold error: In long time exisiting app / no changes to filter formula or data structure.

@SebS,

Thank you. I did go back and reindex any of the query points from the list. Still no avail, as the error was only from 1 specific choice in the drop down. I modified the formula at this time to remove the following:

, "SupportDate", SortOrder.Descending, "Title", SortOrder.Ascending ) }, If( CountRows(wList), Filter( wList, TextSearchBox1_2.Text in Title || TextSearchBox1_2.Text in UserName || TextSearchBox1_2.Text in SiteName || TextSearchBox1_2.Text in SupportMethod), wList ) )

Which now at least, the items are populating in the gallery as expected, I'll be slowing adding back the various arguments until I find the culprit. It is a bit frustrating that this issue self manifested seemingly from no where and after so long. Perhaps its time to move into Dataverse... Thank you again!
Verified answer

SebS 4,197 Super User 2025 Season 1 on at

Like (0)

Report

Threshold error: In long time exisiting app / no changes to filter formula or data structure.

The list you using is not properly Indexed and that means if you cross 5K records You may get this error.

Go to your list and Create Index for all columns You using in the Formula you can create up to 20 index columns in one list.

let me know if this worked for You.