Understanding the Salesforce Relationship and Query Execution using Health Cloud Objects

Relationship in Salesforce

Photo by Nathan Dumlao on Unsplash

Salesforce Objects and Relationships

Salesforce uses parent-child relationships to link objects together. There are two main types of relationships:

1. Lookup Relationship — A loose connection between objects; deletion of a parent does not affect children.
2. Master-Detail Relationship — A strict connection where the child (detail) object inherits security and ownership from the parent (master).

Take an example:

SELECT Id, Name, PersonEmail,
 (SELECT Id, Name, ICC_DataUsePurpose__c FROM AuthorizationFormConsents),
 (SELECT Id, Name, Product_ICC__c FROM Care_Program_Enrollee_Products__r) 
FROM Account 
WHERE PersonEmail='example@gmail.com' 
LIMIT 1

We have following objects in Salesforce

1. Account (Parent)
2. AuthorizationFormConsent (Child)
3. Care_Program_Enrollee_Products (Child)

Each child object contains a foreign key reference (a lookup or master-detail field) to the Account object.

How This Query Works

1. Fetches fields from the Account object:

• Id, Name, and PersonEmail (basic Account fields).

1. Subqueries fetch child records using relationship names:

• (SELECT Id, Name, ICC_DataUsePurpose__c FROM AuthorizationFormConsents)
• Fetches related AuthorizationFormConsent records where the AccountId matches the parent.
• (SELECT Id, Name, Product_ICC__c FROM Care_Program_Enrollee_Products__r)
• Fetches related Care_Program_Enrollee_Products records where the AccountId matches the parent.

1. Filters Account records by email:

• WHERE PersonEmail='example@gmail.com' ensures only one specific account is fetched.

1. Limits results:

• LIMIT 1 ensures only one Account record is retrieved.

Understanding the Relationships

Each child object has a relationship name (used in subqueries). Here’s how they map:

• Relationship names are plural in SOQL queries and end with __r if they are custom objects.
• These names are set when the lookup/master-detail field is created.

Understanding the Query Result

Returned Data

[
  {
    "attributes": {
      "type": "Account",
      "url": "/services/data/v48.0/sobjects/Account/XXXXXXXXXXXIAA"
    },
    "Id": "XXXXXXXXXXXIAA",
    "Name": "example example",
    "PersonEmail": "example@gmail.com",
    "AuthorizationFormConsents": null,
    "Care_Program_Enrollee_Products__r": {
      "totalSize": 2,
      "done": true,
      "records": [
        {
          "attributes": {
            "type": "CareProgramEnrolleeProduct",
            "url": "/services/data/v48.0/sobjects/CareProgramEnrolleeProduct/XXXXXXXXXXXXTIAQ"
          },
          "Id": "XXXXXXXXXXXXTIAQ",
          "Name": "Tuna - Example - CPE-01010101",
          "Product_ICC__c": "Tuna"
        },
        {
          "attributes": {
            "type": "CareProgramEnrolleeProduct",
            "url": "/services/data/v48.0/sobjects/CareProgramEnrolleeProduct/XXXXXXXXXXXX45IAA"
          },
          "Id": "XXXXXXXXXXXX45IAA",
          "Name": "CareMe Oncology - exampledrug - CPE-01010101",
          "Product_ICC__c": "exampledrug"
        }
      ]
    }
  }
]

Key Insights

1. The Account record has Id, Name, and PersonEmail.
2. AuthorizationFormConsents is null → meaning no records exist for this Account in AuthorizationFormConsent.
3. Care_Program_Enrollee_Products__r contains two records:

• Id:XXXXXXXXXXXXTIAQ with Product_ICC__c: Tuna
• Id:XXXXXXXXXXXX45IAA with Product_ICC__c:exampledrug

Conclusion

• The subqueries use relationship names to fetch child objects related to Account.
• The relationship name is based on the lookup/master-detail field name but ends with __r for custom objects.
• If a related object has no records, it returns null (as seen in AuthorizationFormConsents).
• If related records exist, they are returned as a list (Care_Program_Enrollee_Products__r).