+ Start a Discussion

How to achieve behavior similar to Campaign Hierarchy for a Custom field(Associated Accounts)?

I need to add additional related list 'Associated Accounts' to Campaign and show the 'Number Of Associated Accounts' in Campaign Detail Page. So far it was pretty straight forward to achieve.
I also need to maintain the associated accounts count for complete Campaign Hierarchy. I started writing the code, however with my limited Apex skills, I could not achieve this without a SOQL (Select) stmt in the for loop, it was required to get the details of the parent Campaign.
Is there any other way of achieving it with some join SOQL queries, so I do not need to search for parent campaign in for loop?
trigger Campaign_Accounts_After on Campaign_Accounts__c (after insert, after delete, after undelete) {

//1. Update Campaign Number of Accounts in Campaign and Parent Campaigns
if(trigger.isInsert || trigger.isUpdate){
    //List<Campaign> parentCampaignsUpdate = new List<Campaign>();
    Set<Id> associatedCampaignIds = new Set<Id>();
    for(Campaign_Accounts__c campaignAccounts : Trigger.new){

    List<AggregateResult> campaignAccountsCount = [SELECT Campaign__c, COUNT(Account__c) FROM Campaign_Accounts__c WHERE Campaign__c IN : associatedCampaignIds GROUP BY Campaign__c];

    List<Campaign> campaignsUpdate= new List<Campaign>();
    for(AggregateResult ar : campaignAccountsCount){
        Campaign c = new Campaign();
        c.Accounts_in_Campaign__c = (Integer)ar.get('expr0');
        //c.HierarchyNumberOfAccounts__c = c.Accounts_in_Campaign__c; //for this also I need to iterate all the child campaigns
        c.Id = (Id)ar.get('Campaign__c');

    update campaignsUpdate;

    //Maintain the hierarchy, search for parents
    for (Campaign campaign : [SELECT Accounts_in_Campaign__c, HierarchyNumberOfAccounts__c, ParentId FROM Campaign WHERE Id IN :campaignsUpdate])
        Campaign childCampaign = new Campaign();
        if(campaign.ParentId != null){
            // In this foor loop: I need to write SOQL to get the Parent Campaign Details and check if Parent has further a parent Campaign or not.
            // I am lost here.

Best Answer chosen by Dev_Arya
Solved the problem: Here it goes.
I achieved the behaviour, using a custom object (with lookup relation to both campaign and account) and three custom fields on Campaign.
Custom Object: Campaign Account (Campaign_Account__c) 
Fields: Campaign__c Lookup(Campaign)
        Account__c Lookup(Account) 

Custom fields on Campaign Object:
Accounts_in_Campaign__c Number(18, 0)
Accounts_in_Child_Campaigns__c Number(18, 0)
Hierarchy_Number_Of_Accounts__c Formula (Number) (Accounts_in_Campaign__c + Accounts_in_Child_Campaigns__c)

Trigger on Campaign_Account__c:
It updates the Accounts_in_Campaign__c field on the associated campaign with the count of associated records in Campaign_Account__c.

Trigger on Campaign Object:
It populates the Accounts_in_Child_Campaigns__c field of a parent campaign with the sum of Hierarchy_Number_Of_Accounts__c field of all the child campaigns.

It maintains the whole hierarchy. I can post the code, in case some needs it.

Cheers, Happy coding.