Retrieve from a list with more than 5000 items

Forms
1

I have a Rest API query to a list that now has more than 5000 items in it and I’m struggling with the code to retrieve the data I want, my code is below: -

// Function to retrieve Equipmment List from QPS and set in Equipment List Field
function updateEquipmentList () {
	var equipmentList = '';
	$.ajax({  
		url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Solutions%20Quotation%20Products')/Items?$select=Quantity,Product_x0020_Description$filter=(Solutions_x0020_Quotations_x0020/Title  eq '"+quoteReference+"' and Show_x0020_on_x0020_Proposal eq 1)",  
		type: "GET",
		cache: true,
		async: false,
		headers: {
			"accept": "application/json;odata=verbose",
		},
		success: function(data) {
			for (var i = 0; i < data.d.results.length; i++) {
				var item = data.d.results[i];
				equipmentList += (item.Quantity+' x '+item.Product_x0020_Description+'; ');
				fd.field('Equipment_x0020_List').value = equipmentList;
			}  
		},  
		error: function(error) {  
			alert(error.responseText);
		}
	});
}

I would really appreciate a bit of help with the best way of dealing with the issue and retrieving the data I need.

Thanks

2

Dear @Tony_Duke,
In order to retrieve items from a large list with over 5,000 items make sure that you only filter it by indexed fields. If fields are not indexed, they cannot be used for filtering.

Also, as an experiment, try to construct some sort of a URL which would retrieve some items and test it in the browser (just go to this URL). If it retrieves items successfully it will return them to you in text, then you’ll just need to replicate this with JavaScript.

3

Hi @Nikita_Kurguzov,

I have sorted this one thanks, the Lookup Column was set as indexed but what I found is that it ignores this if you look for Solutions_x0020_Quotations_x0020/Title, whereas if I change this to Solutions_x0020_Quotations_x0020/Id and then change the filter to look for the ID rather than the title it then worked. So it seems the virtual “Title” column of a Lookup field is not indexed even if the main column is.

Thanks

1 Like