The independent daily for Salesforce admins, developers and architects

Curated by Abhinav (opens in a new tab)

Recommended

Finding missing and duplicate records after a migration by matching external keys (opens in a new tab)

You will learn a method to reconcile two CSV exports on a shared External ID, using spreadsheet formulas to flag records that are missing, extra, duplicated or have blank or mismatched keys.

Why read this

You will also see why to check parent objects first.

  1. Export source and target with the shared key
  2. Count blank and duplicate keys per sheet
  3. Match each row in both directions
  4. Review missing, extra and duplicate statuses
  5. Repeat per object, parents first
Reconciling source and target exports by external key, in order

After a migration, record counts can look right while hiding records that never arrived, records loaded twice, and records with no source. The article argues that the Salesforce Id cannot be used for matching because it is regenerated on insert, so you need a key present on both sides, such as a legacy ID held in an External ID field like Legacy_ID__c.

The method uses two exports and a spreadsheet. Keys are first checked for blanks and duplicates with a case-sensitive EXACT-based count, since COUNTIF ignores case. A status formula on each sheet then labels each row as OK, Missing in target, Extra in target, Duplicate key, Blank key or similar, which is checked in both directions so that a duplicate in the target shows up only when both sides are compared.

The author recommends running this per object, starting with Accounts and then Contacts and Opportunities, because a missing parent explains gaps underneath it. The method does not compare field values or verify parent relationships; the article points to separate posts for those checks and mentions a paid tool it sells.

Read the original at dev.to (opens in a new tab)

Worth reading?