How to Modify a SOQL Query with Two Objects

A SOQL query pulls certain records and fields into Apex Code. This post explains a SOQL query that includes Contacts and their related Opportunities from the Year End Tax Receipt app, but I aim to help you understand SOQL in general.

For those of you that want to modify the Year End Tax Receipt for your specific donors:

• Follow along with this post to modify which records will be displayed in the table of gifts last year. We’ll look at an example of only included gifts marked tax deductible.
• Look at the original post to learn how to modify which columns appear in the gift table.

Understand the SOQL

Here’s my SOQL query. This is how I get the records and fields that I’m going to do something with later on in the code.

1 [SELECT LastName, id,Gifts_Last_Year__c, 

2 (SELECT Id, CloseDate, Amount FROM Opportunities 
                                                                                  
3 WHERE CALENDAR_YEAR(CloseDate) =:year 

4 AND IsWon = True 

5 ORDER BY CloseDate) 

6 FROM Contact WHERE npo02__OppAmountLastYear__c > 0 

7 WITH SECURITY_ENFORCED]          
    

Let’s see what’s happening line by line.

[SELECT LastName, id,Gifts_Last_Year__c, 

1. Line 1 I am asking for fields on the Contact record that I want to use in the apex. I don’t think I actually use LastName or Id, so I probably could have left those out. My goal in the apex code is to change the value of Gifts_Last_Year__c field so I need to get its current value here. If you want any more contact fields, pop them in here followed by a comma.

(SELECT Id, CloseDate, Amount FROM Opportunities 

2. I am asking for the fields I want returned from a specific object. I am going to create a table that lists the Amount and CloseDate of an opportunity so I ask for those fields here. Here’s where I could ask for Campaign.Name (this is using a special thing called dot notation to reach to another related record) or A_Custom_Field__c (written using the API name or the Field Name) to include a field in the table for the Year End Tax app. (Learn more about how to include additional columns in the Year End Tax app here.)

WHERE CALENDAR_YEAR(CloseDate) =:year  

3. After “where” I say which specific records I want to return. So the previous two lines were which fields I want. Now I’m saying which specific records. This first part is a little tricky and I’m going to gloss over it. I want opportunities where the Calendar_Year of the CloseDate equals the value of the variable year that I defined earlier in the code. Just roll with it.

AND IsWon = True 

4. I use AND to specify more criteria. I want all of my opportunities to be closed won so I say isWon = true. IsWon is a hidden field that is true if the opportunity is won! Cool! So lines 3 and 4 are like my filters in a report. This is where I say which records I want. Lines 1 and 2 are like the columns in my report – which fields show up in the results.

ORDER BY CloseDate) 

5. This says I want the results returned in order of the CloseDate. Learn more here. My parenthesis says this is the end of what I have to say about opportunities.

FROM Contact WHERE npo02__OppAmountLastYear__c > 0 

6. I started out my code by saying what fields I wanted, but it’s not until line 6 after that closed parenthesis where I specify that I want those fields to be for Contacts. Note that “Contact” is singular and “Opportunities” is plural! I’ve got my Where clause here to specifically return only contacts whose Opportunity Amount Last Year was greater than 0. This is pulling donors who gave something last year. Notice I use the api name of the field here.

WITH SECURITY_ENFORCED]          

7. And I threw that in for good measure to only return fields and objects that the user is allowed to see. Learn more.

Modify the SOQL

Jennifer sent me a message through the Trailblazer community (well done!) to ask how she can only include gifts last year that are tax deductible and only contacts who made at least one tax deductible gift. We need to add two criteria for the Opportunity and change it for the Contacts.

1. A checkbox on the Opportunity: Tax_Deductible__c
2. A rollup summary field on the Contact: Tax_Deductible_Gifts_Last_Year__c.

So WHERE are we going to add this criteria? That’s right… WHERE.

AND Tax_Deductible__c = True

Let’s sneak in another line of code between lines 4 and 5 to add our Opportunity criteria.

And let’s change the criteria in line 6 to the other roll up field.

FROM Contact WHERE Tax_Deductible_Gifts_Last_Year__c > 0 

So our new code looks like:

1 [SELECT LastName, id,Gifts_Last_Year__c, 

2 (SELECT Id, CloseDate, Amount FROM Opportunities 
                                                                                  
3 WHERE CALENDAR_YEAR(CloseDate) =:year 

4 AND IsWon = True 

5 AND Tax_Deductible__c = True

6 ORDER BY CloseDate) 

7 FROM Contact WHERE Tax_Deductible_Gifts_Last_Year__c > 0 

8 WITH SECURITY_ENFORCED]   

And without the lines for the numbers or extra spacing:

[SELECT LastName, id,Gifts_Last_Year__c, 
(SELECT Id, CloseDate, Amount FROM Opportunities                                                                                  
WHERE CALENDAR_YEAR(CloseDate) =:year AND IsWon = True AND Tax_Deductible__c = True ORDER BY CloseDate) 
FROM Contact WHERE Tax_Deductible_Gifts_Last_Year__c > 0 
WITH SECURITY_ENFORCED]   

Next Step…Modify Your Test Class

Follow the instructions in the git hub files below to make changes to the code in your sandbox. Update and save both files.

Then on the apex class class “YearEndTestBatch” hit “Run Test.” Then go to the “Tests” tab to see if you have all green checkmarks. If so, you did it correctly!

Deploy to Production

Create an outbound change set that includes:

1. Apex Classes:
   • YearEndTestDataFactory
   • YearEndGiftBatchTest
   • YearEndGiftBatch
2. Flow (included in latest version of the package): YearEndGiftBatchFlow
3. Permission set: Year End Tax Donation Letter
4. Custom Field: Gifts Last Year
5. Classic Email Template: YearEndTaxSend

Celebrate

You did it! You modified apex code! That’s pretty awesome!