cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Struan
Frequent Visitor

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
Frequent 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
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (5,197)