)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
Salesforce’s custom report type structure creates record duplication when objects have multiple relationship paths to the same parent, generating duplicate parent records for each child relationship.
Here’s how to eliminate this duplication and get clean, accurate data from your optional lookup relationships.
Eliminate record duplication with precise SOQL queries using Coefficient
Coefficienteliminates report record duplication through custom SOQL queries that use LEFT JOINs and proper grouping. When Object D relates to Object A both directly and through an optional lookup chain, you can structure queries to return each D record only once with conditional fields showing data from whichever relationship path exists.
How to make it work
Step 1. Write custom SOQL with LEFT JOINs.
Salesforce’sUsecustom SOQL feature in Coefficient to craft queries that explicitly handle optional lookup relationships while maintaining data integrity. Structure your query to return each record only once regardless of multiple relationship paths.
Step 2. Import data from multiple relationship paths separately.
Use the Objects & Fields import method to pull data from each relationship path as separate datasets. This gives you complete control over how the data is structured before combining it.
Step 3. Apply spreadsheet deduplication functions.
Use Excel or Google Sheets’ native UNIQUE, REMOVE DUPLICATES, or VLOOKUP formulas to merge data from multiple relationship paths intelligently. Create logic that prioritizes certain relationship paths when duplicates exist.
Step 4. Set up Formula Auto Fill Down for new records.
Ensure your deduplication logic automatically applies to new records during scheduled refreshes. Place your formulas in the column immediately to the right of your imported data for automatic application.
Step 5. Configure advanced filtering for clean data.
SalesforceUsefiltering with AND/OR logic to segment data appropriately before it reaches your spreadsheet, reducing the need for complex deduplication formulas.
Get clean data without the headaches
Start using CoefficientThis approach provides much greater control over optional lookup relationships compared to Salesforce’s rigid report types that force you to accept duplication.to build reports with clean, deduplicated data.
