Hello,
I have a large list and I need to filter items by some users in a period. The logical conditions are:
DataFrom AND DataTo AND (User1 OR User2 OR User3)
The same conditions in other mode:
(DataFrom AND DataTo AND User1) OR (DataFrom AND DataTo AND User2) OR (DataFrom AND DataTo AND User3)
I followed all recommendations:
- Inserted in CAML Query a {Filter} token
- Removed the OrderBy section
- Indexed by filtered fields
- Generated programmatically the code for replace {Filter} token
But the result is the same:
Request failed: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.
undefined
Indeed, the three users have over 5 thousand items in the entire list, but in the indicated period the number of items is only a few dozen and the error should not persist (the created impression is that the filtering by date would not work). On the other hand, if I choose three other users or remove one of them and in total they does not have in entire list 5000 elements, no error results and the filtering works well even by period.
How can I solve this case?
Thank you
Dear @Cumatale,
Instead of building such complex conditions, have you tried adding them one by one, and see that they work? You can test each condition one piece at a time, then combine them together slowly and see at what point the whole thing breaks, so you can make the necessary changes.
Of course, the filter has been tested, more - I want to mention that for several months it has been working on production and is working well (by date and by users) even now for combinations of users who have not yet reached 5000 items in the entire list. The problem is that if 5000 items by users are reached, then the date filter does not work, which must reduce the number of items below 5000
Dear @Cumatale,
When was the field indexed last time? Have you tried to re-index it?
The automatic reindexing takes place every 4 hours
Dear @Cumatale,
Have you tried selecting a filtered List View in Data Source - SharePoint List with less than 5K items?
I have just modified the view selected in the Data Source to return only 20 elements - the result in the report is the same (exceeding 5 thousand elements)
Does it work without the filter condition though? Is it the only difference?
I'm sorry, but I don't understand how to interpret your question. Of course, if I remove the filter and set a View in Data Source with less than 5,000 elements, everything works. But I have related graphics and the filter is formed automatically based on the clicks on other graphics. I will try to explain in another way what the problem is:
Dear @Cumatale,
Okay, I see! The underlying problem seems to be that the total of returned items is over 5000, right?
I don't think there is anything we can do to work around that specifically, it's just how CAML works, the filter conditions should probably be even more specific to only return less than 5000 items.
