Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
John L Schneider IIJohn L Schneider II 

How can I select the AccountId from CampaignMember (Account List Controller)

I am looking to get a list of accounts that do not have any contacts who are members of a specific campaign.

Here is the code I have, but I cannot figure the way to get the list. 
public class AccountListController6 {
    
    private String sortOrder = 'Name';
    public List<Account> getAccounts() {
        String strEt6 = '701220000001Q1GAAU';
        List<Account> results = Database.query(
            'SELECT Id,Name ' +
            'FROM Account ' +
            'WHERE ID ' +
            'NOT IN ' +
            '(SELECT Contact.AccountId ' +
            'FROM CampaignMember ' +
            'WHERE CampaignId = :strEt6) ' +
            'ORDER BY ' + sortOrder + ' ASC ' +
            'LIMIT 10'
        );
        return results;
    }
}

 
UC InnovationUC Innovation
Hi John,

Try this:
  
public class AccountListController6 {
    
    private String sortOrder = 'Name';
    public List<Account> getAccounts() {
        String strEt6 = '701220000001Q1GAAU';
        List<Account> results = Database.query(
            'SELECT Id,Name ' +
            'FROM Account ' +
            'WHERE ID ' +
            'NOT IN ' +
            '(SELECT AccountId ' +
            'FROM Contact) ' +
            'AND CampaignId = :' + strEt6  +
            'ORDER BY ' + sortOrder + ' ASC ' +
            'LIMIT 10'
        );
        return results;
    }
}

 
John L Schneider IIJohn L Schneider II
I get this error:

"The inner select field 'Contact.AccountId' cannot have more than one level of relationships"

Also, I'm not understanding why you moved the CampaignId outside of the subquery as I am only looking for accounts not represented in that specific campaign.
UC InnovationUC Innovation
Hi John,

If I now understand you correctly you are looking for accounts that arent in that campaign you specified. If So try this out. 
  
public class AccountListController6 {
    private String sortOrder = 'Name';
	
    public List<Account> getAccounts() {
        String specifiedCampaignId = '701220000001Q1GAAU';
        
		Set<Id> accountIdsBelongingToSpecifiedCampaign = Database.query('SELECT AccountId ' +
																		'FROM Contact' +
																		'WHERE CampaignId = :' + specifiedCampaignId);
															   
		List<Account> results = Database.query('SELECT Id, '
													  'Name ' +
											   'FROM Account ' +
											   'WHERE Id NOT IN + :' + accountIdsBelongingToSpecifiedCampaign
											   'ORDER BY ' + sortOrder + ' ASC ' +
											   'LIMIT 10');
        
		return results;
    }
}

I broke your query into two seperate queries to make it more clear and avoid the previous error.

Hope this helps!

AM
John L Schneider IIJohn L Schneider II
I think this is the right direction, but I got an error on line 07 
Illegal assignment from List<SObject> to Set<Id>
John L Schneider IIJohn L Schneider II
I tried tweaking it some to look like this. 
public class AccountListController6 {
    private String sortOrder = 'Name';
    String specifiedCampaignId = '701220000001Q1GAAU';
    String str2 = '2017';
    public List<Account> getAccounts(){    
        Set<Account> accountIdsBelongingToSpecifiedCampaign = new Set<Account>(Database.query('SELECT Contact.AccountId ' +
                                                                                              'FROM CampaignMember ' +
                                                                                              'WHERE CampaignId = :' + specifiedCampaignId));
    }
    public List<Account> getAccounts2() {    
        List<Account> results = Database.query('SELECT Id, ' +
                                               'Name ' +
                                               'FROM Account ' +
                                               'WHERE Id NOT IN + :' + accountIdsBelongingToSpecifiedCampaign +
                                               'Number_of_Active_Plans__c > 0 AND ' +
                                               'Migration_Date__c = :str2 ' +
                                               'ORDER BY ' + sortOrder + ' ASC ' +
                                               'LIMIT 10');
        return results;
    }
}
but get this error on line 14:
Variable does not exist: accountIdsBelongingToSpecifiedCampaign 
SwarnaSankhaSinghSwarnaSankhaSingh
Hi John,

I checked the code you wrote and found issues with the syntax as well as the logic. When you query the CampaignMember object in SFDC, you will never be able to get a reference to the Account associated with the Contact by using Contact.AccountId; and it will be confusing because you will not get a Syntax error either.

You can try executing this code snipper in the anonymous code block and you will understand what I am trying to point out: 
String specifiedCampaignId = '701220000001Q1GAAU';

List<CampaignMember> contactMemberIdsBelongingToSpecifiedCampaign = (Database.query('SELECT Contact.AccountId ' +
                                                                               'FROM CampaignMember ' +
                                                                               'WHERE CampaignId =\'' + specifiedCampaignId + '\''));
System.Debug('contactMemberIdsBelongingToSpecifiedCampaign ---->' + contactMemberIdsBelongingToSpecifiedCampaign);
What will be needed here is the following:
  • Query the CampaignMember for the Campign ID to get the associated Contacts.
  • Use the Contact list to query their associated Accounts.
  • Use the Account list as the filter in the final query.
I have added the code snippet which will fetch the desired result for you with the corrected syntax: 
public class AccountListController6 
{
    public static String sortOrder = 'Name';
    public static String specifiedCampaignId = '701220000001Q1GAAU';
    public static String str2 = '2017';
    
    public static List<Account> getAccounts() 
    {    
        
        List<CampaignMember> contactMemberIdsBelongingToSpecifiedCampaign = (Database.query('SELECT Id, ContactId, CompanyOrAccount ' +
                                                                                            'FROM CampaignMember ' +
                                                                                            'WHERE CampaignId =\'' + specifiedCampaignId + '\''));
        
        List<Id> contactIdsBelongingToSpecifiedCampaign = new List<Id>();
        
        for(CampaignMember recCM : contactMemberIdsBelongingToSpecifiedCampaign)
        {
            contactIdsBelongingToSpecifiedCampaign.add(recCM.ContactId);
        }
        
        Set<Id> accountIdsBelongingToSpecifiedCampaign = new Set<Id>();
        
        if(contactIdsBelongingToSpecifiedCampaign != NULL && contactIdsBelongingToSpecifiedCampaign.Size() > 0)
        {
            for(Contact recCon : [Select Id, AccountId From Contact WHERE ID IN: contactIdsBelongingToSpecifiedCampaign])
            {
                if(recCon.AccountId != NULL)
                {
                    accountIdsBelongingToSpecifiedCampaign.add(recCon.AccountId);
                }
            }
        }
        
        List<Account> accResultList = Database.query('SELECT Id, Name ' +
                                                     'FROM Account ' +
                                                     'WHERE Id NOT IN : accountIdsBelongingToSpecifiedCampaign' +  
                                                     ' AND Number_of_Active_Plans__c > 0 AND ' +
                                                     'Migration_Date__c = :str2 ' +
                                                     'ORDER BY ' + sortOrder + ' ASC ' +
                                                     'LIMIT 10');
        
        System.Debug('accResultList ---->' + accResultList);
        return accResultList;

    }
}
You can make the needed changes in terms of the fields you would like to fetch or additional logic you would need to add to the filters.

I hope this helps you; please do let me know how it turns out. If you feel that your question was answered then do flag the appropriate answer as the solution to your query.

Kind Regards,
Swarna.