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 Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (1,845)