MT-ing My Head

Welcome to my soapbox of Salesforce technical writing, music, and miscellaneous stuff

How I Fixed It: Duplicates of an Object with Two Lookups — November 8, 2020

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

This is a series of posts about data cleanup in SalesforceThe 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.

Screenshot 2020-02-08 at 7.40.06 AM.png
Screenshot from


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

How I Fixed It: Improperly Formatted Contact Names — November 7, 2020

How I Fixed It: Improperly Formatted Contact Names

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

The Problem

We have a series of forms (for making a donation or signing up for a program, for instance) that create Contact records in Salesforce. Some people type in all caps (e.g. BOB ROSS) or all lowercase (e.g. bob ross) and the records end up reflecting that.

Business Implications

  • Merge Fields in Mailers – A mass email or appeal letter will end up saying “Dear bob,” rather than “Dear Bob,”
  • Reporting to Funders – When we need to report program data to funders on a granular (constituent-based) level, the development team has to spend time formatting the names properly before sharing the report.

What I Did

Identifying the Records

After some exploratory data analysis and trial and error, I created two checkbox formula fields on the Contact called “Contact First Name Bad Format” and “Contact Last Name Bad Format” (I know, I know…silly field labels).

First Name Field


Last Name Field


These formulas looks for Contacts with the First Name or Last Name in all uppercase or lowercase. It ignores initials such as “DJ” for first names or “C.” for last names. People putting initials instead of their full last names into fields is a different issue….

Fixing the Records

I ran a report of all of the Contacts where the “Contact First Name Bad Format” OR “Contact Last Name Bad Format” fields were true. I made sure to include the 18-digit Case-Safe Contact ID in this report.

Then I exported the report, used the PROPER() function on Excel to change the first and last names to proper formatting, and used Apex Data Loader to update these (400 or so) records.

Preventing the Issue Moving Forward

To prevent this issue from happening in the future., I created two workflow rules using the a) the criteria in the above formula fields and b) field updates witht he solution mentioned in this post.

This workflow rule will detect if a person’s name is in all uppercase or lowercase letters and edit it accordingly:


See formula fields above

Evaluation Criteria

Evaluate the rule when a record is created, and any time it’s edited to subsequently meet criteria


  • Field Update: Update First Name (Proper)
    • Field: First Name
    • Code: UPPER(LEFT(FirstName, 1)) & LOWER(RIGHT(FirstName, LEN(FirstName) – 1))
  • Field Update: Update Last Name (Proper)
    • Field: Last Name
    • Code: UPPER(LEFT(LastName, 1)) & LOWER(RIGHT(LastName, LEN(LastName) – 1))

Testing the Workflow Rules

First I manually created Contacts with the above names:

Contact NameExpected Result
JJ AbramsJJ Abrams
audre lordeAudre Lorde
F. FITZGERALDF. Fitzgerald

Other Tests

TestExpected Result
Edit Contact manually with one of above namesFix name
Fill out the donation form with one of the above namesFix name and send acknowledgement with fixed name included
Fill out a program form with one of the above namesFix name
YESSSS! It worked.

What’s Left to Do

Tweak the field update to be more precise – There’s still some room for error here, since the formula only capitalizes the first letter of each field. Some exceptions that this would not update properly would be:

  • Gordon-Levitt
  • McDonald
  • DiFranco


Overall, this took about two hours to figure out and will likely save more than two hours of annoyance and manual editing in the future.

Feel free to comment with any questions or other ways you’ve solved this.