How to Write SOQL for Custom Objects with Shared Fields in Salesforce

Learn how to effectively write `SOQL` queries for two custom objects with the same fields in Salesforce. This guide details the limitations and provides alternative solutions for finding unmatched records.
---
This video is based on the question https://stackoverflow.com/q/64734554/ asked by the user 'Carolyn Cordeiro' ( https://stackoverflow.com/u/12934138/ ) and on the answer https://stackoverflow.com/a/64741057/ provided by the user 'David Reed' ( https://stackoverflow.com/u/1159783/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to Write SOQL for 2 custom objects which have same fields Level_1__c, Level_2__c,Level_3__c

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Getting to Know SOQL and Its Limitations

Salesforce Object Query Language, or SOQL, is a powerful tool that allows you to query data stored in Salesforce objects. However, it comes with its own set of rules and limitations. One common challenge arises when working with custom objects that share the same fields but cannot be easily joined, specifically when trying to cross-reference records between two custom objects.

In this post, we’ll explore a specific use case involving two custom objects — Case_Type_Data__c and ERT_Case_Type__c — that share fields Level_1__c, Level_2__c, and Level_3__c. We will address the issue of identifying which records from ERT_Case_Type__c did not successfully copy over to Case_Type_Data__c.

The Problem at Hand

Carolyn, a Salesforce user, faced a problem when writing an SOQL query to identify unmatched records. She received an error message when attempting to use a semi-join that required Level_3__c:

[[See Video to Reveal this Text or Code Snippet]]

The error message indicated that semi join subselects can only query ID fields, which means Carolyn was unable to perform the intended query across the two custom objects because Level_3__c is not an ID field.

SOQL Query Limitations

Why Can't You Join Arbitrary Fields?

The inability to join arbitrary fields in SOQL can be frustrating. In Carolyn’s case, the following points clarify the constraints:

Non-ID Field Limitation: You cannot perform joins on fields unless they are ID fields.

Field Type Impact: If the queried field is a text field (like Level_3__c), it cannot be used for joins, as indicated by the error message.

This limitation means a direct comparison between non-ID fields across different custom objects cannot be achieved through a traditional SOQL query.

Alternative Solutions

Despite the limitations of SOQL, there are alternative approaches Carolyn could take to solve her problem:

1. Local Data Analysis

Extract Data: Export data from both Case_Type_Data__c and ERT_Case_Type__c using Salesforce Data Loader or another export tool.

Perform Local Analysis: Use spreadsheet software or a database tool to compare the exported records and identify the ones that did not copy over.

2. Apex Code Solution

If local analysis is not feasible (e.g., due to data volume or resource constraints), consider utilizing Apex code:

Query Both Objects Separately: Write Apex code to query data from both Case_Type_Data__c and ERT_Case_Type__c.

Process Data in Apex: Iterate through the results to determine which records exist in ERT_Case_Type__c but not in Case_Type_Data__c.

However, it should be noted that the second option can be more complex to implement and might require advanced knowledge of Salesforce development.

Conclusion

In summary, while SOQL provides an excellent method for querying Salesforce data, its limitations necessitate exploring alternative methods for data comparison when dealing with custom objects with shared fields. Whether you choose to analyze data locally or employ Apex, the key is to recognize the constraints of SOQL and find creative solutions to work within them.

Carolyn's situation highlights a common challenge in Salesforce data management, but with the right approaches, you can overcome these barriers and ensure your data is accurate and complete.

By understanding the rules and finding creative workarounds, Salesforce users can effectively manage their data and overcome potential obstacles.