How to check for similar Phone numbers in multiple fields upon entering a new record

Last published at: November 21st, 2023
Delete

This article explains how to check if there are any similar values between several fields when entering a new record. 

For example: If record 1 has "088 8888" as primary phone number, and a user creates a new record 2 with the same "088 8888" as an alternative phone number, Duplicate Check duplicate prevention will trigger.

Delete

In order to cross-check two or more different fields and their values, you need to create a combined field that contains all these values.

1. Create a Combined Phone formula field

First, create a formula field for the combined phone fields. This field will be used in the duplicate prevention scenario to cross-check the values of the different phone fields. In this example the fields Phone1 and Phone2 will be cross-checked for similar values.

  1. Go to the Salesforce Setup menu > Object Manager.
  2. Select the Object where you want to add the Combined Phones field.
  3. At left, go to Fields & Relationships.
  4. Click New.
  5. From the given options for the new field, choose Formula and click Next.
  6. Enter a field label and field name:
    • At Field Label, enter Combined Phones
    • At Field Name, enter Combined_Phones
  7. At Formula Return Type select "Text" and click Next
  8. As a formula, insert the following formula, including the API names of the different fields you want to check. In the example below, those are the “Phone1” and “Phone2" fields.
    Example of Combined Phone field formula:
    SUBSTITUTE(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( Phone , "(", ""), ")", ""), " ", ""), "-", ""), "+", "")
    + " " +
SUBSTITUTE(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( MobilePhone , "(", ""), ")", ""), " ", ""), "-", ""), "+", "")
  9. At the Advanced tab, at Blank fields handling, select "Treat blank fields as blanks". Click Next.
  10. Apply the field-level security of your choice and click Next. 
  11. Click Save.

2. Create a scenario with the Combined Phone fields

  1. Go to the DC Setup page.
  2. Select the Object for which you want to do a cross-check comparison in duplicate prevention.
  3. Add the newly created field by clicking Add New Field. In the drop-down menu, select "Combined Phones".
  4. For Matching Method, choose "Partial Exact".
  5. Set the Threshold level to 100%.
  6. Apply the scenario to "Manual Insert Prevention" and "Manual Update Prevention".
    Example Scenario
Delete

By adding the formula field to the scenario and choosing 'Partial Exact' as the matching method, Duplicate Check can do a cross comparison between the Phone Numbers included in the formula field.

3. (Re-)Create the Search Index

Create or Recreate the Search Index. This applies both if the new field you are going to cross-check is custom and has never been indexed before by the Duplicate Check Search Index, and if you already have created a Search Index in the past.

  1. Go to DC Setup.
  2. On the left hand side, under Object Setup, select the Object you want to (re-)create a Search Index for.
  3. On the *Object* Settings tab, make sure the Search Index option is enabled, and the search index is configured in the Advanced Settings > Index. Read more here‍.
  4. Go to the Index Batch tab.
  5. Click the Start button and select Create Search Index.

Click here to watch a short video of how to enable and create the Search Index.