Odata Query Failing - Hope I can you from loosing a week of your life!
It may be unusual to post here after I think I have solved my own issue but I have lost a week of my life, but I hope that I can save others my grief.
I have SharePoint list with over 32000 records.
In trying to run the odata query (code simplified for easy read)
AccYear eq 'AccYear' and Pupil eq 'ID' and Subject3 eq 'subejct' and TrackingEvent eq 'TrackingEvent'
it would appear to work (find the matching record) then stop working for no apparent reason.
I think I have now resolved the issue by changing the order of the query terms.
Pupil eq 'ID' and Subject3 eq 'subejct' and TrackingEvent eq 'TrackingEvent' and AccYear eq 'AccYear'
Out of the 32000 total records roughly 15000 are filtered by the AccYear value
Out of the 32000 total records roughly 100 are filtered by the Pupil value
Out of the 32000 total records roughly 5000 are filtered by the TrackingEvent value
Out of the 32000 total records roughly 600 are filtered by the Subejct3 value
I think that by asking the query to first find the 15000 (AccYear) values then find the 100 (Pupil) values within this, I was enduing up with only the first 5000 values (lovely pagination), then trying to find the matching pupil... but if it was record 5001 onwards it wasn't being returned.
By having the pupil as the first term (and finding only 100 records)m then looking for the other filters I believe the pagination issue isn't an issue and so the records are returned.
So I'm hoping for two things... some guru confirming this was the issue, and secondly hopefully some other poor newbie avoiding my problem and the associated stress!
Hi! I'm starting some Querys using Odata too, however with a list of 120k rows. I haven't had the problem reported yet, using filters on multiple columns. But I would like to ask... Are all informed columns Indexed in the SharePoint settings?