How I Fixed It: Duplicates of an Object with Two Lookups

This is a series of posts about data cleanup in Salesforce. The goal is to IDENTIFY, FIX, AND PREVENT data issues.

The Problem

We created a custom object that relates Contacts with each other (“Relationship”) using two lookup fields (“Primary Contact” and “Secondary Contact”). Over time, a rogue form created up to 10 of the same record.

Business Implications

The duplicate relationships were frustrating to look at on the page layout for our users.

Additionally, they showed up when running reports and needed to be cleared in Excel before distributing.

Finally, it was unclear when the relationship was actually established in the system (earliest date).

What I Did

Identifying the Records

I created a formula field called “Primary and Secondary Contacts Same” on the Relationship object with the following formula:

CASESAFEID(Primary_Contact__r.Id) = CASESAFEID(Secondary_Contact__r.Id)

Then I created a report and grouped by this field and sorted by Record Count descending.

Fixing the Records

Due to the low number of duplicates, it was easiest for someone to manually go in and delete every record except the oldest. I could have also used Excel to identify which record to keep of each duplicate set.

Preventing the Issue Moving Forward

First, I tried to use a matching rule to detect records with the same values across two lookup fields (for instance multiple records with Bob in field 1 in John in field 2). Unfortunately, this was not possible! Sad!

After some Googling, I came across Gorav Seth’s method of preventing duplicates among junction objects. This includes creating a new field for a unique key, a before-update flow that updates the unique key, a matching rule, and a duplicate rule.

Conclusion

This was a very simple solution that ultimately saved us money on a deduping tool that allows for deduping of custom objects!