0

I am running into an issue when using the listdata.svc REST API on large lists. I have a query that looks like: .../_vti_bin/listdata.svc/MyList?$filter=(substringof('<query>',LastName))or(substringof('<query>',FirstName))&$select=Name,Id

Where Name is a calculated column that combines LastName, FirstName (and does a bit more stuff in cases where there are middle names/initials etc.)

The list has over 5000 items and throws a 500 error. Both FirstName and LastName are indexed. When I replace the or operator with an and operator there is no error. So the issue must be related to the list view threshold and SharePoint not wanting to filter on more than 1 field. (And I can't use a Person field for this as many of the entries in this list do not exist in AD). If it matters I am using O365. My goal is to get a list of names that contain the query text

Anyone have any ideas on how to get around this?

Improve this question
2
  • Had you indexed the fields before crossing the threshold? Commented Sep 16, 2018 at 11:37
  • Yes; both Firstname and Lastname where indexed before hitting 5000 items Commented Sep 17, 2018 at 12:48

2 Answers 2

Reset to default
1

You can try this then -> .../_api/web/lists/getByTitle('MyList Display Name')?$filter=(substringof('<query>',LastName))or(substringof('<query>',FirstName))&$select=Name,Id

if this throws an error then maybe this should work ->

.../_vti_bin/listdata.svc/MyList?$filter=(substringof('<query>',LastName) eq false)or(substringof('<query>',FirstName) eq false)&$select=Name,Id
Improve this answer
4
  • The first one returns the list. Even when I add the /items to the query, it is still throttled. The Second one isn't functionally any different than the original query, which again, is still throttled. Commented Sep 18, 2018 at 14:25
  • Try with single filter. Most of the time "or" in filters do not work on threshold crossed lists. Commented Sep 19, 2018 at 8:16
  • Without the "or" operator it works fine (as they are indexed columns). I get that the "or" operator is the problem as it requires doing a join which results in a large set of data which has no relevant index. Commented Sep 19, 2018 at 12:58
  • 1
    You will have to write 2 separate calls and then join the result sets. Commented Sep 19, 2018 at 13:00
0

OR is an evil operator.

This will force SP to evaluate both sides of the query and then join them. The 5000 item limit is thrown once the internal engine accesses the 5000th record.

Also substring is not a good operator for indices. Even indexed data will have to work through an index scan...

Improve this answer
1
  • I agree with you. However it is still something that needs to be done in my scenario (looking up a person (not necessarily user) so I can assign them a permit). So it is this evil lookup operation (as lookup fields don't work either), or I have to get the user to manually search for the person they want, and trust that they type in an id correctly. It is good to know how the SP engine is evaluating the expression and why it throws an error Commented Sep 18, 2018 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.