0

I am trying to create a SOQL query that will give me contact and opportunity information from people in a specific campaign. I have done the following and I can get Contact information, but I am not sure how to make the connection to get the associated opportunity. I tried the following Opportunity.Name in the select but that does not work. Any ideas? I have just being trying this in the query editor but will be using this in an apex code callout.

SELECT CONTACT.FIRSTNAME FROM CAMPAIGNMEMBER
WHERE CAMPAIGNID = 'MYCAMPAIGNID' AND CONTACT.ID !=null

To be more clear I am trying to get the opportunities associated with the Contacts in the Campaign. I am looking at the following objects Contacts, Opportunities and CampaignMembers. I do have Contact__c on the opportunity Object.

2
  • 1
    Are you trying to get the Opportunities associated with Contacts in the Campaign or Opportunities associated with the Campaign? These are two quite different data sets.
    – David Reed
    Jul 22 '19 at 18:36
  • @DavidReed I am trying to get the opportunities associated with the Contacts in the Campaign. The data sets I am looking at are as follows: Contacts, Opportunities and CampaignMembers. I also do have Contact__c on the opportunity Object if that helps
    – MarkC5000
    Jul 22 '19 at 18:41
1

You'll have to start from the contact record in order to get this information. You'll also need to check your custom Contact__c field to determine the correct relationship name. In general, the query will look like this:

SELECT Name, (SELECT Name, Amount, CloseDate FROM Opportunities__r)
FROM Contact
WHERE Id IN (SELECT ContactId FROM CampaignMember WHERE CampaignId = 'MYCAMPAIGNID')
3
  • would I be able to parse the second result in an apex callout? The name comes back from the contact and then I seem to get a JSON object returned from the __r object. I just want to take this result and format it as a CSV string. The result I see now in query editor is the following John [{"Name":"Test","Amount":null,"CloseDate":"2019-05-15"}]
    – MarkC5000
    Jul 22 '19 at 19:24
  • 1
    @MarkC5000 You could make it a CSV in theory, you just need to repeat the contact info for each opportunity: for(Contact record: queryResults) { for(Opportunity oppRecord: record.Opportunities__r) { csvRows.add(record.Name+','+oppRecord.Amount+','+oppRecord.CloseDate); } } Basically, the inner for loop builds each row. You'll have the same contact repeated for each opportunity.
    – sfdcfox
    Jul 22 '19 at 20:07
  • Thanks @sfdcfox
    – MarkC5000
    Jul 23 '19 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.