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, 2019 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, 2019 at 18:41

1 Answer 1

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, 2019 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, 2019 at 20:07
  • Thanks @sfdcfox
    – MarkC5000
    Jul 23, 2019 at 12:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .