0

I have a query on CampaignMember and I would like to get some Tasks for the Contact or Account that is associated with the CampaignMember record. I'm guessing I can't do that since the Tasks relationship is between Task and Contact, not with CampaignMember. Unfortunately I can't do this with Apex as I am producing a Conga Composer document and am limited to SOQL inside Conga Queries.

When I make the Contact the root of the query I can do this (obviously, since there is a Tasks relationship between the Contact and Task) but then I can't pull in the Tasks for the Accounts, or any fields really since the CampaignMember records with Accounts are excluded.

SELECT 
    Id, 
    FirstName,
    LastName, 
    (SELECT 
        Id,  
        Account.Name 
    FROM CampaignMembers
    WHERE CampaignId = '1234567890ABCDEFG'),
    (SELECT 
        Id,
        Who.Name
    FROM 
        Tasks
    WHERE Status = 'Not Started'
)
FROM Contact
WHERE Id IN (
        SELECT ContactId 
        FROM CampaignMember
        WHERE CampaignId = '1234567890ABCDEFG'
    )

What would be cool is if I could do something like this (I know I can't):

SELECT 
    Id, 
    ContactId,
    Contact.FirstName, 
    Contact.LastName, 
    Account.Name,
    (
        SELECT 
            Id,
            Who.Name
        FROM 
            Contacts.Tasks
        WHERE Status = 'Not Started'            
    ),
    (
        SELECT 
            Id,
            Who.Name
        FROM 
            Accounts.Tasks
        WHERE Status = 'Not Started'            
    )
FROM CampaignMember
WHERE CampaignId = '1234567890ABCDEFG'

but clearly I can't since there is no Task relationship to Account. Are there any ways that I can query objects that are related to a record that is related to the root object of my query?

I'm required to build a document that handles Accounts and Contacts together, hence why I went the Campaign route as I can add both objects to a Campaign.

5
  • Maybe you could duplicate the data into a unified table to make the merge work...awkward set of requirements for sure.
    – Adrian Larson
    Dec 9, 2021 at 21:15
  • is this Conga Composer document by any chance a spreadsheet?
    – cropredy
    Dec 9, 2021 at 21:43
  • @cropredy, it is in a Word document (I'm using lots of conditional fields). I'm not opposed to a spreadsheet but since there was lots of formatting I felt Word was more appropriate.
    – kd7iwp
    Dec 9, 2021 at 21:45
  • so -- I had this issue a few years back. I used a multi-tab spreadsheet wherein each tab did one SOQL Conga query and then using excel automation, I could generate client-side the merge of results into a new tab. As I recall, I used PowerBI
    – cropredy
    Dec 9, 2021 at 21:52
  • I certainly could do this with some VLOOKUPS in Excel, thank you for the suggestion.
    – kd7iwp
    Dec 9, 2021 at 23:10

1 Answer 1

0

Here's the outline of a solution that I used many years ago when faced with a similar issue

  • Make your delivery document a spreadsheet rather than a document
  • Add three tabs to the spreadsheet: Summary, Query1, Query2 - you may need additional helper tabs
  • On the Query1 tab, bind this to the first Conga query
  • On the Query2 tab, bind this to the second Conga query
  • Make the Summary tab the default tab when the spreadsheet is opened by the user
  • Use formulas on the Summary tab to join the two other tables in tabs Query1, Query2 to get the result intended. You may need a helper tab for pivot tables to assist

Basically, you are using client-side logic that can execute when the spreadsheet is opened by the user to join the data and present in a form suitable for the end user.

The practical details of the spreadsheet formulas can be addressed with assistance from the SuperUser site and other Excel-oriented sites. I used Microsoft's add-in PowerBI to make this task easier.

You must log in to answer this question.

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