Duplicating & Merging

Deduplicating & merging Salesforce Records

Copy

Over time you may find that duplicate records build up in your Salesforce database.

The following workflow imagines a scenario whereby new leads being created need to be checked for pre-existing duplicates:

1. The Listen for Lead Creation triggers the workflow when a new lead is created in Salesforce

2. Get New Lead uses the 'Find records' operation, using the $.steps.trigger.events[0].Id jsonpath to retrieve the newly created lead, including all the relevant associated fields (FirstName, LastName, Email, Phone, Lead ID, Company etc.)

3. We then conduct a series of 3 checks to see if this lead already exists as a duplicate (explained in detail below)

4. If any of these checks find a duplicate, the Salesforce id for the duplicate lead is stored using a Data Storage set id step ('Set Value operation'), and the workflow moves on to the de-duplicating stage

5. If all 3 checks fail to find a duplicate then the workflow is terminated, as no action needs to be taken

6. If deduplicating is required, the Data Storage get id step uses 'Get Value' to retrieve the id of the duplicate lead

7. Get Old Record is a 'Find Records' operation which uses $.steps.storage-3.value to retrieve the duplicate lead, including all the relevant associated fields (FirstName, LastName, Email, Phone, Lead ID, Company etc.)

8. Build Merged Lead Values is a script which then replaces all the old duplicate values for name, email, etc. with the values from the new lead. If they are not present for the new lead then the original old values will be used

9. Merge Leads finally uses the Salesforce 'Merge Records' operation with the new Lead ID ($.steps.trigger.events[0].Id) as the Master record ID and the old Lead ID ($.steps.storage-3.value) as the 'Record to merge'. The 'Fields to populate' come from the result of the merged values script ($.steps.script-1.result)

The duplicate checks explained

Copy

When making the above duplicate checks, we are making a series of checks of varying degrees of certainty.

The first check is by email:

Looking at the properties panel, we can see how this check is set up:

You can see that while a check is being made to see if any existing leads match this new lead by email, we are also checking for First Name, Last Name and Vertical (i.e. automotive, banking, consumer etc.)

The subsequent checks by phone and company use the same conditional setup, replacing email with phone and company.

By the end of this process we will have checked with approx 99% degree of certainty for pre-existing duplicates (these aren't absolute failsafes as there could be a typo in each field we are checking)

Merging the new and duplicate record

Copy

After using the Data Storage get id step to retrieve the id of the identified duplicate, we fetch the duplicate lead itself:

The Build Merged Lead Values script step then pulls in the old Lead values and new Lead values as variables:

The script itself is simple:

1
// You can reference the input variables using input.NAME
2
// Parsed JSON files could be referenced as fileInput
3
exports.step = function(input, fileInput) {
4
 let values = [];
5

6
 for(let key in input.newLead){
7
  if(key !== "Id"){
8
   if(input.newLead[key] === null){
9
    values.push({
10
     "name": key,
11
     "value": input.oldLead[key]
12
    });
13
   } else {
14
    values.push({
15
     "name": key,
16
     "value": input.newLead[key]
17
    });
18
   }
19
  }
20
 }
21

22
 return values;

It will replace all values from the old lead with values from the new lead.

If the new lead does not have a particular value, then the old lead value will be retained.

The output is then in a format which will be accepted by the Salesforce schema for merging records. For example:

1
{
2
 "result": [
3
  {
4
   "name": "FirstName",
5
   "value": "Roger"
6
  },
7
  {
8
   "name": "LastName",
9
   "value": "Ramjet"
10
  },
11
  {
12
   "name": "Phone",
13
   "value": "(850) 777-2436"
14
  },
15
  {
16
   "name": "Id",
17
   "value": "00Q1QxxxxxxYGUA3"
18
  },
19
  {
20
   "name": "Vertical__c",
21
   "value": "Automotive"
22
  },
23
  {
24
   "name": "Company",
25
   "value": "Ramjet and co"
26
  },
27
  {
28
   "name": "Email",
29
   "value": "info@ramjet.net"
30
  },
31
  {
32
   "name": "MobilePhone",
33
   "value": "(850) 755-3555"
34
  }
35
 ],
36
 "console": []
37
}

The output of the script step can then be used in Fields to populate for the Merge leads step: