function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Andrew Morales 1Andrew Morales 1 

Donation Question: SOQL Query Contact ID Opportunities

Im sure this is super basic for some of you so hoping I can save some hours in my day by asking the larger community. 

The question I am trying to answer is: How many Contacts have made a Donation with the Opportunity Name = "Foundation Donation" in 2017?

The nuance is:
  • We dont use Accounts
  • We dont use Contact Roles for Opportunities
  • We use a custom lookup field on the Opportunity Object called 'Donor_Contact'
  • I dont need to return dollar amount (although would be helpful)
  • I dont need to return any Contact meta data, strictly 'number of donors'. 
A lot of the solutions I have found are looking up the Contact via the Account which we dont use and/or go through the Contact Role which we dont use. I have not found a solution that utilizes a custom field lookup on the Opportunity Object to get to the Contact. I am looking for some tips on structuring the query. Everything I have tried has returned 'malformed' errors. 

Any help would be appreciated. Thank you!
 
LBKLBK
Hi Andrew,

If you are just looking for the count,
  
SELECT count() FROM Contact WHERE Id IN (SELECT Donor_Contact__r.Id FROM Opportunity WHERE Name = 'Foundation Donation' AND CALENDAR_YEAR(CloseDate) = 2017)
If you want the details of the Contacts, 
SELECT Id, FirstName, LastName, Email FROM Contact WHERE Id IN (SELECT Donor_Contact__r.Id FROM Opportunity WHERE Name = 'Foundation Donation' AND CALENDAR_YEAR(CloseDate) = 2017)
Let me know how it goes.
Andrew Morales 1Andrew Morales 1
Hi LBK. Many thanks for the response. Your query format makes sense to me however I am getting an error when I try to run it in Workbench or dataloader.io. 

Query:
SELECT count()
FROM Contact
WHERE Id IN (SELECT Donor_Contact__r.Id FROM Opportunity WHERE Name = 'Foundation Donation' AND CALENDAR_YEAR(CloseDate) = 2017)

Error:
ERROR at Row:1:Column:72
The inner select field 'Donation_Contact__r.Id' cannot have more than one level of relationships

Any thoughts?