ZohoCRM: Process all records of a module

: Joel Lipman; Zoho; 24 August 2021; Hits: 3543

What?
A quick article to document a method of looping through all the records of a module and processing these with the ability auto-resume without processing the same record twice.

Why?
Whether you have a few hundred records, or a few hundred thousand records, we sometimes want to write a function that will check each record and mark it so that the process doesn't repeat on records that have already been checked.

Some solutions have worked in the past where you could simply add a checkbox and do a search where this value is false; but lately this hasn't been working for me. To this end, I have thought of an alternative that I now use frequently in client systems.

How?
The gist is that we add a checkbox called "Processed" which will have a datatype Boolean. Our function will then loop through each record and do what it has to do. The workaround here is that we order this by modified time. When the checkbox gets updated, this modifies the record and puts it at the bottom of the list.

In a nutshell:

[Optional]: Deactivate any workflows that this process would affect
Go to the Module Layout and drag a "Checkbox" field on to the module
We'll call ours "Processed" but it really doesn't matter as long as it's a field to update
In your code, sort a list by "Modified_Time", loop through and update each iterated record. (see sample code below).
Go to the list view in CRM and filter by "Processed" is "Selected", check after each run of the function that the number has incremented.
Once all records are done, return to the Module Layout and delete the "Processed" field.
[Optional conditionally] Reactivate any workflows you deactivated in step 1.

Sample Code
Consider the following standalone function which loops through every contact and deletes the duplicate lead (if a lead exists with the same email as a contact):

copy raw

// init
v_Count = 0;
v_CountDeleted = 0;
//
// sort criteria for our selection
m_Criteria = Map();
m_Criteria.put("sort_order","asc");
m_Criteria.put("sort_by","Modified_Time");
//
// get 100 records
l_Contacts = zoho.crm.getRecords("Contacts", 1, 100, m_Criteria);
//
// loop through each contact record
for each r_Contact in l_Contacts
{
    v_Count = v_Count + 1;
    if(!isnull(r_Contact.get("Email")))
    {
        v_Email = r_Contact.get("Email");
        //
        // find all lead records matching this email
        l_Leads = zoho.crm.searchRecords("Leads","Email:equals:" + v_Email);
        for each  r_Lead in l_Leads
        {
            // check this is a record with an ID
            if(!isnull(r_Lead.get("id")))
            {
                // delete this lead record
                m_Delete = Map();
                m_Delete.put("module","Leads");
                m_Delete.put("id",r_Lead.get("id"));
                r_Delete = zoho.crm.invokeConnector("crm.delete",m_Delete);
                v_CountDeleted = v_CountDeleted + 1;
            }
        }
    }
    //
    // update this contact record irrespective of whether a lead was matched
    m_UpdateContact = Map();
    m_UpdateContact.put("Processed",true);
    r_UpdateContact = zoho.crm.updateRecord("Contacts", r_Contact.get("id").toLong(), m_UpdateContact);
}
return "Deleted " + v_CountDeleted + " of " + v_Count;

// init
v_Count = 0;
v_CountDeleted = 0;
//
// sort criteria for our selection
m_Criteria = Map();
m_Criteria.put("sort_order","asc");
m_Criteria.put("sort_by","Modified_Time");
//
// get 100 records
l_Contacts = zoho.crm.getRecords("Contacts", 1, 100, m_Criteria);
//
// loop through each contact record
for each r_Contact in l_Contacts
{
v_Count = v_Count + 1;
if(!isnull(r_Contact.get("Email")))
{
v_Email = r_Contact.get("Email");
//
// find all lead records matching this email
l_Leads = zoho.crm.searchRecords("Leads","Email:equals:" + v_Email);
for each r_Lead in l_Leads
{
// check this is a record with an ID
if(!isnull(r_Lead.get("id")))
{
// delete this lead record
m_Delete = Map();
m_Delete.put("module","Leads");
m_Delete.put("id",r_Lead.get("id"));
r_Delete = zoho.crm.invokeConnector("crm.delete",m_Delete);
v_CountDeleted = v_CountDeleted + 1;
}
}
}
//
// update this contact record irrespective of whether a lead was matched
m_UpdateContact = Map();
m_UpdateContact.put("Processed",true);
r_UpdateContact = zoho.crm.updateRecord("Contacts", r_Contact.get("id").toLong(), m_UpdateContact);
}
return "Deleted " + v_CountDeleted + " of " + v_Count;

Additional Notes
As a preliminary measure, I deactivate any workflows in CRM that this process would affect. In the example above, all workflows relating to the Contacts and Leads module.

Lastly, I'm usually asked to update all records and there are at least 10 thousand if not 100 thousand records. The code I use is a for loop within a for loop or in other words 5 pages (as each page is limited to 200) and 1000 records is the sweet spot in which case the loading bar disappears but the function usually completes all 1000 every 5-10 minutes:

copy raw

// init
v_CountFound = 0;
v_CountUpdated = 0;
l_Pages = {1,2,3,4,5};
v_PerPage = 200;
for each  v_Page in l_Pages
{
	m_SortCriteria = Map();
	m_SortCriteria.put("sort_order","asc");
	m_SortCriteria.put("sort_by","Modified_Time");
	l_Records = zoho.crm.getRecords("Contacts",v_Page,v_PerPage,m_SortCriteria);
	for each  r_Record in l_Records
	{
		if(!isnull(r_Record.get("id")))
		{
			// re-init
			m_Update = Map();
			v_RecordID = r_Record.get("id");
			v_CountFound = v_CountFound + 1;
			// 
			// other field update
			if(!isnull(r_Record.get("Account_Name")))
			{
				// do other stuff
				v_CountUpdated = v_CountUpdated + 1;
			}
			// 
			// update the record irrespectively
			m_Update.put("Processed",true);
			r_Update = zoho.crm.updateRecord("Contacts",v_RecordID,m_Update);
		}
	}
	if(l_Records.size()<v_PerPage)
	{
		break;
	}
}
return "Updated " + v_CountUpdated + " of " + v_CountFound;