cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Struan
New Member

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!

 

 

Struan

 

 

1 REPLY 1
bTeixeira
Regular Visitor

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?

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,608)