The Birds & the Bees of SOQL


When writing relationship queries in SOQL it is important to first have a good understanding of how relationships are created in Salesforce. When working with Standard objects, the relationships are created behind the scenes so the best place to take a look at relationships and how they are made will be by creating your own custom object relationships.

Let’s first start by creating a basic Invoice object in our Developer Orgs. The following instructions assume that you have Lightning Experience enabled in your Dev Org. Even if you won’t be enabling Lightning in your Production Org just yet, it’s good to start getting some hands on experience.

  1. Log into your Developer Org
  2. Click the Setup Gear icon and select Setup Home
  3. Click on the Create Menu (top right) and select Custom Object
  4. Enter Invoice in the Label field
  5. Enter Invoices in the Plural field
  6. Check the Starts with vowel sound checkbox
  7. Click the Save button leaving all other defaults

InvoiceObject

Now, let’s create the Line Item as a child object to the Invoice object.

  1. Click on the Create Menu (top right) and select Custom Object
  2. Enter Line Item in the Label field
  3. Enter Line Items in the Plural field
  4. Click the Save button leaving all other defaults

LineItems

At this point these 2 objects are completely unrelated, we have to tell Salesforce what the relationship is.

  1. Click on Object Manager from Setup
  2. Click on the Line Item object
  3. Click the New button in the Fields & Relationships
  4. Select Master-Detail Relationship and click the Next
  5. Select Invoice from the Related To dropdown and click Next
  6. Leave Invoice as the Field Label
  7. Change the Field Name to InvoiceId
  8. Click through the rest of the wizard leaving all remaining defaults

InvoiceField

Notice how the Child Relationship Name defaulted to the value entered in the Plural field when creating the Line Item object. The Child Relationship Name is key to querying for child records so it is very important to understand where the default comes from and that it is only a default and can be modified. The value entered in the Field Name field will also come into play later on when querying for parent details.

Schema

Let’s open up the Developer Console and create some data.

  1. Click on the Setup Gear icon and select the Developer Console
  2. Select Open Execute Anonymous Window from the Debug menu
  3. Create an Invoice Record and its related Line Items by executing the following code (make sure to check the “Open Log”  checkbox):
  4. Filter the Execution Log to display only the VARIABLE_ASSIGNMENT items:  ExecutionLog

Notice that when we create the related Line Item records that we are setting the InvoiceId__c to the parent Invoice record’s Id. This is sometimes confusing to new users because when you use the lookup to select the parent record and while viewing the related field the Name is typically displayed even though the content of the field is the parent record’s Id, like Testing1 below.

LineItemDetail

Let’s head back into the Developer Console to create a parent to child relationship query.

  1. Click on the Setup Gear icon and select the Developer Console
  2. Click on the Query Editor tab
  3. Enter the following query to Select the Invoice Record and all related Line Items

Note the following:

  1. The subquery is within parenthesis
  2. The child object is referenced by using the Child Relationship Name
  3. The child object ends in __r
  4. The query result returns the Line Items as a list of Line Item records ParentToChildQueryResults

Now let’s create a child to parent relationship query.

  1. Enter the following query to Select the Line Items along with the details from the parent record

Note the following when retrieving related parent fields:

  1. The parent object name ends in __r
  2. Use dot notation to refer to the parent object fields
  3. The query results returned have the same values in both InvoiceId__c and InvoiceId__r.Id

If we were to change the Plural Label for Line Items to Items but left the Child Relationship Name set to Line Items, what would the query look like? Will it change? What about if you changed the Child Relationship? Play around with the labels and queries to see what happens. For additional help on SOQL relationships, check out the Force.com SOQL and SOSL Reference.

Leave a comment

Your email address will not be published. Required fields are marked *