Get first item matching condition in large SharePoint list

DanielR 0
Viewed 35

So I have a SP list with about 100k items (voucher codes) in it. Each of them has columns for State (Active/Used), Value (10,20,30) Group (Normal, Special) and Code (random alphanumeric). Each of the columns are indexed

I can't use CAML to get the next active code for a certain group and value, because each of the criteria would return > 5k items (list view threshold).

So what would be the most efficient way to retrieve the next code? As the list is continuously growing, loading all items with SPListItemCollectionPosition is not really an option. Isn't there a better way? It should work for onprem, as well as spOnline

Thank you

sharepoint sharepoint-online c#
1 Answers

If your Code is a progressive number, increasing at any new entered item, you have 2 options:

  1. Create a service List that you can name something like CodeCounter, here you store the last created Code in a column. Create a workflow in main list, starting at item creation. This workflow will read the last created Code from service list, then update it to Code+1 and in parallel manages the update (optional) of main code in main list
  2. use an event handler (farm solution, here the query surely works better)
M.Grag0
Related