Print

ZohoCRM: Process all records of a module

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:
  1. [Optional]: Deactivate any workflows that this process would affect
  2. Go to the Module Layout and drag a "Checkbox" field on to the module
  3. We'll call ours "Processed" but it really doesn't matter as long as it's a field to update
  4. In your code, sort a list by "Modified_Time", loop through and update each iterated record. (see sample code below).
  5. 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.
  6. Once all records are done, return to the Module Layout and delete the "Processed" field.
  7. [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):
copyraw 
// 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;
  1.  // init 
  2.  v_Count = 0; 
  3.  v_CountDeleted = 0; 
  4.  // 
  5.  // sort criteria for our selection 
  6.  m_Criteria = Map(); 
  7.  m_Criteria.put("sort_order","asc"); 
  8.  m_Criteria.put("sort_by","Modified_Time"); 
  9.  // 
  10.  // get 100 records 
  11.  l_Contacts = zoho.crm.getRecords("Contacts", 1, 100, m_Criteria); 
  12.  // 
  13.  // loop through each contact record 
  14.  for each r_Contact in l_Contacts 
  15.  { 
  16.      v_Count = v_Count + 1; 
  17.      if(!isnull(r_Contact.get("Email"))) 
  18.      { 
  19.          v_Email = r_Contact.get("Email"); 
  20.          // 
  21.          // find all lead records matching this email 
  22.          l_Leads = zoho.crm.searchRecords("Leads","Email:equals:" + v_Email); 
  23.          for each  r_Lead in l_Leads 
  24.          { 
  25.              // check this is a record with an ID 
  26.              if(!isnull(r_Lead.get("id"))) 
  27.              { 
  28.                  // delete this lead record 
  29.                  m_Delete = Map(); 
  30.                  m_Delete.put("module","Leads"); 
  31.                  m_Delete.put("id",r_Lead.get("id")); 
  32.                  r_Delete = zoho.crm.invokeConnector("crm.delete",m_Delete); 
  33.                  v_CountDeleted = v_CountDeleted + 1; 
  34.              } 
  35.          } 
  36.      } 
  37.      // 
  38.      // update this contact record irrespective of whether a lead was matched 
  39.      m_UpdateContact = Map(); 
  40.      m_UpdateContact.put("Processed",true); 
  41.      r_UpdateContact = zoho.crm.updateRecord("Contacts", r_Contact.get("id").toLong(), m_UpdateContact); 
  42.  } 
  43.  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:
copyraw 
// 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;
  1.  // init 
  2.  v_CountFound = 0; 
  3.  v_CountUpdated = 0; 
  4.  l_Pages = {1,2,3,4,5}; 
  5.  v_PerPage = 200; 
  6.  for each  v_Page in l_Pages 
  7.  { 
  8.      m_SortCriteria = Map(); 
  9.      m_SortCriteria.put("sort_order","asc"); 
  10.      m_SortCriteria.put("sort_by","Modified_Time"); 
  11.      l_Records = zoho.crm.getRecords("Contacts",v_Page,v_PerPage,m_SortCriteria); 
  12.      for each  r_Record in l_Records 
  13.      { 
  14.          if(!isnull(r_Record.get("id"))) 
  15.          { 
  16.              // re-init 
  17.              m_Update = Map(); 
  18.              v_RecordID = r_Record.get("id"); 
  19.              v_CountFound = v_CountFound + 1; 
  20.              // 
  21.              // other field update 
  22.              if(!isnull(r_Record.get("Account_Name"))) 
  23.              { 
  24.                  // do other stuff 
  25.                  v_CountUpdated = v_CountUpdated + 1; 
  26.              } 
  27.              // 
  28.              // update the record irrespectively 
  29.              m_Update.put("Processed",true); 
  30.              r_Update = zoho.crm.updateRecord("Contacts",v_RecordID,m_Update); 
  31.          } 
  32.      } 
  33.      if(l_Records.size()<v_PerPage) 
  34.      { 
  35.          break; 
  36.      } 
  37.  } 
  38.  return "Updated " + v_CountUpdated + " of " + v_CountFound; 
Category: Zoho :: Article: 758