Automatic Record Matching

Updated 2 weeks ago by Copado Solutions

If you have worked with Copado Data Deploy, you may already know that Copado uses the upsert method to deploy records. If the record already exists in the destination organization, Copado will simply update it, and if it doesn’t exist, it will create the record.

To match records and determine if a record already exists in the destination organization, Copado uses an external Id field. Therefore, if you don’t have an external Id field, you will need to create one for each of the objects you will be migrating records from and populate a value to prevent Copado from creating duplicate records while migrating the data.

While you can create a process builder to generate a value for the external Id field or users can manually add a value, this is time-consuming and users also can forget to add the value, so with that in mind, Copado has developed a record matching functionality that allows you to build a formula that will populate this value for you.

Let’s see how this feature works.

When you build a data template and navigate to the Object Fields tab to see the fields that are included in the main object, you can now see a drop-down menu under the Field Content Update column for the external Id field:

To trigger Copado’s record matching functionality, select Auto Generate For Empty Values from the drop-down menu. If you already have a process in place to populate a value for the external Id field in a given object and don’t want Copado to generate one for you, set it to None.

A pop-up window will be displayed. Click on Configure Formula to proceed with the formula:

Copado allows you to select up to three fields to build your formula. You can select fields from the main object you are building the formula for or from a related parent object included in the main template. This can come in handy when working with junction objects where there are not many fields or they are not unique.

Please make sure you select fields for your formula that when combined form a unique set of characters. For instance, if you are creating a formula for the external Id field on the Contact object, you could combine the Email and the Mobile Phone fields.

If you are using more than two fields and feel your formula might exceed the external Id field character limit, select the Hash External ID Formula checkbox. Copado will replace the original value with a random set of 255 characters:

Once you are done, click on Save Formula.

If you want to edit the formula, go back to the Object Fields tab and click on the new tab icon next to the Field Content Update column. Here you will be able to edit the formula or confirm it if you are satisfied with the result:

Testing and Validating Your Formula

Once you have built your formula to generate a value for the external Id field, and before you start migrating your data, you may want to test the formula and confirm the generated external Id values are truly unique. You can do this by following the steps below:

  1. Create a data deployment with the same source and destination org.
  2. Select a data template which includes the object you have built the formula for.
  3. Deploy your template.
  4. Review the results. You can do so using either of these two options:
    1. Create a list view for the object you have deployed records from and include an External Id column so that you can check the Id values that were generated.
    2. Review the generated Ids in the Copado deployment results.
  5. If you don’t get a unique value, edit your formula and consider using a different set of fields. Mass update the existing external Id field and clear the value so that Copado can generate a new one.

Additional Considerations

  • You can only build one formula per object. 
  • Copado’s record matching functionality will generate a value for the external Id field for all the records in the org, regardless of any filters or record limits you may have in the data template.
  • Copado will only generate a value for the external Id field only if the field is empty. If you want to replace a value (for example if your formula did not generate accurate Ids and you want to update it), you will first need to clear the existing value.

How did we do?