Pagination in COQL Queries

If you need to retrieve more than 200 records, make sure you know how to paginate using COQL. It’s fairly simple and you can fetch up to 10,000 records at a time.

Look at the below code for how to use the pagination feature. Essentially, you can call 200 records at a time and you need to increase the ‘offset’ by 200 each time in order to be able to call the next 200 records. We accomplish this by creating a simple repeated list of 49 items (49 * 200 = 9800, our maximum offset)

We use a ‘for each’ loop on that list to calculate the offset for the next COQL call, but, we only want to run the COQL call if the last one did retrieve a full 200 records. So, we check for that as well.

The below code retrieves Contact records in batches of 200. Note that you must have a WHERE clause but it could be something simple like saying “where mandatory_field is not null,” which doesn’t actually filter anything out.

repeats = repeat(",",49);
info repeats;
offset = 0;
iteration = 0;
allContacts = list();
iterationComplete = false;
for each  r in repeats
{
	if(iterationComplete == false)
	{
		queryMap = Map();
		queryMap.put({"select_query":"select Last_Name, First_Name from Contacts where Last_Name is not null limit " + offset + ", 200"});
		response = invokeurl
		[
			url :"https://www.zohoapis.com/crm/v3/coql"
			type :POST
			parameters:queryMap.toString()
			connection:"crmcoql"
		];
		contacts = response.get("data");
		info contacts.size();
		if(contacts.size() < 200)
		{
			iterationComplete = true;
		}
		allContacts.addAll(contacts);
		info allContacts.size();
		iteration = iteration + 1;
		offset = iteration * 200;
	}
}
Back to Lesson