De-duplication of Records using Custom Function and Schedules

De-duplication of Records using Custom Function and Schedules

Overview :

Data Migration Wizard allows you to migrate data into Zoho CRM, and at times there are instances where the CRM already has data which is manually created in Zoho CRM which is similar or identical to the data that you are migrating. Data Migration will create new records in this case, which is not a desirable outcome for businesses.

Partner wants to perform a phased migration for a Client where they import Contacts and Deals in more than one phase. Each phase had same contacts associated with different set of Deals and each time the Contact had some updates as the data was fetched from their live environment where they are migrating from.

Requirement :

Contacts module contains duplicates with each phased migration bringing in the same Contacts with a different set of Deals. We need to de-duplicate the Contacts and maintain the association with the Deals from each migration. Their unique identifier for Contacts is the Email field which would be used to identify the duplicates.

Merging Records using Deduplication or Find & Merge can be a tedious task that requires a lot of manual work when the volume of duplicate records is high. 

Solution :


To use Custom Function to merge the Contact Records using Merge Record API on the Contacts under a Custom View and search the Contact -> Fetch Duplicates -> Merge against the Contact in the Custom View.

Expected result is the Contacts merged as one using the unique identifier and the Associated Deals migrated during the data migration to be associated with the Merged Contact.

Action Plan :

  1. Newly migrated Contacts will be considered as Master Records by adding a extra column for Custom Checkbox which will be True for all Newly Migrated Records
  2. Create a Custom View in Contacts Module using the Custom Checkbox to pull the Master Contacts separately.
  3. Create a Scheduler to run every 2 hours to execute the Custom Function


Info
Key Points :
  1. To check and determine the API credits available and manage the execution within the limit to avoid failures due to API exhaustion
  2. To update the Custom Checkbox field so after the merge is completed, the Contact will be moved out of the Custom List View
  3. Send an Email as fail safe if the execution fails and copy the Contact ID which fails.
  4. Plan the schedule execution outside the business hours to avoid being updated in between.

Setup / Configuration :

Create a schedule to run after business hours on Friday for every 2 hours associate and write your own Custom Function script as below


Create the Custom Checkbox field in the Layout Editor :


Create the Custom List View :


Custom Function Script : 


  1. void schedule.mergeContacts()
  2. {
  3. listrec = invokeurl
  4. [
  5. url :"https://www.zohoapis.com/crm/v7/Contacts?cvid=638969*******7354017&page=1&per_page=100"
  6. type :GET
  7. connection:"zohocrm"
  8. ];
  9. // get the master records from the custom list view
  10. master_reclist = listrec.get("data");
  11. for each  master_rec in master_reclist
  12. {
  13. uniqueemail = master_rec.get("Email");
  14. uniqueemailid = master_rec.get("id");
  15. info "master record id : " + uniqueemailid + " unique email : " + uniqueemail;
  16. searchrec = invokeurl
  17. [
  18. url :"https://www.zohoapis.com/crm/v7/Contacts/search?email=" + uniqueemail
  19. type :GET
  20. connection:"zohocrm"
  21. ];
  22. // search the child record using the email address of the master record
  23. totalrec = searchrec.get("data");
  24. totalsize = totalrec.size();
  25. if(totalsize > 0)
  26. {
  27. for each  elem in totalrec
  28. {
  29. recid = elem.get("id");
  30. info uniqueemailid;
  31. if(recid != uniqueemailid)
  32. {
  33. info "Execute for - RecId : " + recid + " & Email : " + uniqueemail;
  34. fieldList = Map();
  35. fieldList.put("api_name","Phone");
  36. // fieldList is the values from the new records that needs to be overwritter
  37. overwritefields = List();
  38. overwritefields.add(fieldList);
  39. master_idslist = Map();
  40. master_idslist.put("id",recid);
  41. master_idslist.put("_fields",overwritefields);
  42. master_rec_det = List();
  43. master_rec_det.add(master_idslist);
  44. m = Map();
  45. // m.put("master_record_fields",master_rec_det);
  46. m.put("data",master_rec_det);
  47. dat = List();
  48. dat.add(m);
  49. mergedata = Map();
  50. mergedata.put("merge",dat);
  51. mergedataJson = mergedata.ToString();
  52. // info "MergeData : " + mergedataJson;
  53. try 
  54. {
  55. mergerec = invokeurl
  56. [
  57. url :"https://www.zohoapis.com/crm/v7/Contacts/" + uniqueemailid + "/actions/merge"
  58. type :POST
  59. parameters:mergedataJson
  60. connection:"zohocrm"
  61. ];
  62. info mergerec;
  63. // record merge and response
  64. fieldupdmp.put("customCheckBox","");
  65. fieldupdmp = Map();
  66. updrec = zoho.crm.updateRecord("Contacts",uniqueemailid,fieldupdmp);
  67. // to finally update the Custom Checkbox so the Contact will no longer be available under the Custom List View
  68. }
  69. catch (e)
  70. {
  71. status = mergerec.get("code");
  72. info "Status" + status + " Reason :" + mergerec;
  73. if(status != "success")
  74. {
  75. emailmessage = "<p>Execution Failed Master Id : " + uniqueemailid + " & Child Id : " + recid + "---- Status : " + status + "</p>" + e;
  76. sub = "Migration Failure Ids - Failsafe";
  77. sendmail
  78. [
  79. from :zoho.adminuserid
  80. to :"admin1@xyz.com,admin2@xyz.com,admin3@xyz.com"
  81. subject :sub
  82. message :emailmessage
  83. ]
  84. // send email as try and catch with the failed record ids
  85. }
  86. }
  87. }
  88. }
  89. }
  90. }
  91. }

Modifications to the Custom Function Script :
  1. Create a custom connection "zohocrm" with the scope "ZohoCRM.modules.Contacts.ALL"
  2. Use the correct API name of the CustomCheckbox from your Zoho CRM Org
  3. Line no 5:, please use the correct Custom List View ID which you can fetch from the URL

Expected Results :


  1. Merge Successful :
    {"merge":[{"code":"SUCCESS","details":{"id":"5434640000015154110"},"message":"The records have been merged successfully","status":"success"}]}

    Above message is printed in the logs when the Merge Contact record was successful.

​
  1. No Duplicate Contact found with the Email from Master Contact Record :
    {"code":"INVALID_DATA","details":{"resource_path_index":1},"message":"Master record is not found","status":"error"}

    Above message is printed in the logs when there is no Contact with the same email as the Master Contact Record.


  1. Failure Emails :


  1. CustomCheckbox was updated from false to true




  1. You can also check the logs by going to Setup > Developer Hub > Functions > My Functions > Schedules > Choose the Custom Function > Logs > Choose the Timeframe > Click on the Execution instance > find the logs printed or any error that is encountered.



NotesNote : This is a sample log executed as standalone, not schedule for showcasing the execution log.

Completion :

Once all the Child Contacts are merged after execution, you can delete the CustomCheckbox field and stop the scheduler. Relax the usage of the API Credits and check the Merged Contact Records.


If you need any further clarifications, please don’t hesitate to contact partner-support@zohocorp.com.

NotesAdditionally, we kindly ask all Europe and UK partners to reach out to partner-support@eu.zohocorp.com.