function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
HamptonHampton 

Rollup Summary APEX Trigger

Good Afternoon:

 

Here is my situation:

 

I have one custom object Sites__c

 

I have a second custom object Subscriber__c.

 

Subscriber__c has a custom lookup field Address_ID__c

 

Address_ID__c.Fiber_Release__r is the same value as Site__c.Name although the two objects are not related.

 

What I want to do is to count the number of Subscriber__c records there are for a given Site__c using the logic above but without doing lookups and Master-Details.

 

Subscriber__c records are updated monthly via a bulk upsert process using the Apex Data loader and requires too much manual work for lookups.

 

I have no idea where to begin. Any help is appeciated.

 

Thanks,

 

Matt

Jerun JoseJerun Jose
Check out the discussion below for a sample code.
You will need an apex trigger which will perform the rollup calculation

http://boards.developerforce.com/t5/Apex-Code-Development/HELP-Calculate-the-Sum-of-a-RelatedList/m-p/471483#M86395
HamptonHampton

Thanks, I am reading through it now. Some of it looks pretty foreign to me. 

 

In order for that to work, so I have to have a relationship between Sites__c (where I want the aggregate value updated) and Subscriber__c (where I want the value to come from)?

 

Hampton

Jerun JoseJerun Jose
Just read through your problem statement once again now. My apologies, reading the subject line made me quote the solution again.

But yeah, it would be ideal if we could establish relationships between the two objects so that we can ensure there is no many-many mappings between the two objects.
HamptonHampton

No worries...just trying to avoid Master-Detail and lookup relatiosnhips if at all possible due to how we are updating/importing the data.

 

I appreciate all your help.

 

Hampton

Jerun JoseJerun Jose

Although I said its good have relationships, its not impossible to acheive using just the names.

 

I have modified the regular code to handle names. Its still not complete as it needs some work to be done, but maybe you can start off with this.

 

trigger RollUp on Resume_Subscriptions__c (after insert, after update, before delete) {

    // Set to hold the IDs of the Contacts of the Subscriptions
    Set<String> ContactNames = new Set<String>();
    // Set to hold the IDs of the Subscriptions
    Set<ID> SubIDs = new Set<ID>();
    // Set to hold the IDs of the Contacts where the values need to be reset
    Set<ID> ContactNamesForReset = new Set<ID>();

    if(trigger.isupdate || trigger.isInsert){
        for(Resume_Subscriptions__c rsSub : trigger.new ){
            // If the contact of a subscription has changed, then the old contact also needs to be updated 
            if(trigger.isUpdate)
				if( rsSub.User__c != trigger.oldmap.get(rsSub.ID).User__c)
					if(trigger.oldmap.get(rsSub.ID).User__c != null )
						ContactNames.add(trigger.oldmap.get(rsSub.ID).User__c);
            if(rsSub.User__c!=null){
                ContactNames.add(rsSub.User__c);
            }
        }
        ContactNamesForReset.addAll(ContactNames);

        // The list of Contacts where the update has to take place
        List<contact> ContactsToUpdate = new List<contact>();    

        // Querying the database to get the Number of Subscriptions under an contact and the sum of the deal values of the Subscriptions under an contact
        AggregateResult[] Subscriptionsum = [select count(ID) NoOfSubscriptions, User__c ContactName from Resume_Subscriptions__c where Active__c = true AND User__c in: ContactNames group by User__c];

        for( AggregateResult ContactDetail : Subscriptionsum ){
            contact ContactToUpdate = new contact(Name = (String)(ContactDetail.get('ContactName')));
            ContactToUpdate.Active_Resumes__c = integer.valueOf(ContactDetail.get('NoOfSubscriptions'));
            ContactsToUpdate.add(ContactToUpdate);
            // The contact being handled should not be reset. Hence removing it from the ResetSet
            ContactNamesForReset.remove((String)(ContactDetail.get('ContactName')));
        }
        
        for( String nm : ContactNamesForReset ){
            // Resetting the summed up values to 0 if the contact no longer has a subscription.
            contact ContactToUpdate = new contact( name = nm );
            ContactToUpdate.Active_Resumes__c = 0;
            // ContactsToUpdate.add(ContactToUpdate);
        }
        
		// some more logic is needed to identify these records.
        // if(ContactsToUpdate.size() > 0)
            // update ContactsToUpdate;

		ContactsToUpdate.clear();
    }

    if(trigger.isdelete){
        for(Resume_Subscriptions__c rsSub : trigger.old ){
            if( rsSub.User__c != null)
                ContactNames.add(rsSub.User__c);
            SubIDs.add(rsSub.ID);
        }
        ContactNamesForReset.addAll(ContactNames);

        // The list of Contacts where the update has to take place
        List<contact> ContactsToUpdate = new List<contact>();    
        
        // Querying the database to get the Number of Subscriptions under an contact excluding the Subscriptions being deleted
        AggregateResult[] Subscriptionsum = null;
        if(ContactNames.size() > 0){
            Subscriptionsum = [select count(ID) NoOfSubscriptions, User__c ContactID from Resume_Subscriptions__c where User__c in: ContactNames and ID not in: SubIDs group by User__c ];

            for( AggregateResult ContactDetail : Subscriptionsum ){
                contact ContactToUpdate = new contact(Name = (String)(ContactDetail.get('ContactID')));
                ContactToUpdate.Active_Resumes__c = integer.valueOf(ContactDetail.get('NoOfSubscriptions'));
                ContactsToUpdate.add(ContactToUpdate);
                // The contact being handled should not be reset. Hence removing it from the ResetSet
                ContactNamesForReset.remove((String)(ContactDetail.get('ContactID')));
            }

            for( String nm : ContactNamesForReset ){
                // Resetting the summed up values to 0 if the contact no longer has a subscription.
                contact ContactToUpdate = new contact( Name = nm );
                ContactToUpdate.Active_Resumes__c = 0;
                ContactsToUpdate.add(ContactToUpdate);
            }
        }

		// some more logic is needed to identify these records.
        // if(ContactsToUpdate.size() > 0)
            // update ContactsToUpdate;

        ContactsToUpdate.clear();
    }
}

 

HamptonHampton

I will play around with this and see if I can make heads and tails of it :)

Jerun JoseJerun Jose
Glad to help. Do comeback if you have any doubts.
HamptonHampton

Here is what I have so far, I hope I am close :)

trigger SubscriberCount on Subscriber__c (after insert, after update, before delete) {

    // Set to hold the names of Address records
    Set<String> AddressNames = new Set<String>();
    // Set to hold the IDs of the Subscribers
    Set<ID> SubIDs = new Set<ID>();
    // Set to hold the IDs of the Sites where the values need to be reset
    Set<ID> SitesNamesForReset = new Set<ID>();

    if(trigger.isupdate || trigger.isInsert){
        for(Subscriber__c Sub : trigger.new ){
        
        
        // Querying the database to get the Number of Subscribers for each site
        AggregateResult[] Subscribersum = [select count(ID) NoOfSubscribers, Address_ID__c Address
        from Subscriber__c where IPTV_C_Flag__c>0
        AND Address_ID__c in: AddressNames group by Address_ID__c];

        for(AggregateResult AddressDetail : Subscribersum ){
            site SiteToUpdate = new site(Name = (String)(AddressDetail.get(Address)));
            SiteToUpdate.Units_New__c = integer.valueOf(AddressDetail.get('NoOfSubscribers'));
            SitesToUpdate.add(SiteToUpdate);
    }
   }
  }
 }

 I am getting Compile Error: Variable does not exist: Address at line 20 column 76

 

Based on my original problem statement, am I heading down the right path?

 

Thanks,

 

Hampton

AdyAdy

Hi Hampton,

 

To solve that compile error:

Repalce Address in "site SiteToUpdate = new site(Name = (String)(AddressDetail.get(Address)));" with AddressDetail.Address

 

May be you like to consider these before going ahead:

1. The first thing: Are the name unique? If so, the solution of using trigger will be helpful, else you need a unique identifier between the two tables and write the trigger based on it.

2. If you run this trigger for bulk, it will throw an error, because, select query is inside the for loop

 

Happy Coding

Jerun JoseJerun Jose
You need to wrap the Address word within quotes.

site SiteToUpdate = new site(Name = (String)(AddressDetail.get(Address)));
==>
site SiteToUpdate = new site(Name = (String)(AddressDetail.get('Address')));
HamptonHampton

I was thinking about his on my way into work this morning.

 

The records in the Subscriber__c object only get Upserted once a month. And it is a total of ~350k records each time. Would it make more sense or even be possible to do this update via a scheduled batch job?

 

I have one scheduled for the 6th of each month to delete out Subscriber__c records that were not updated with the data load. I could run this one a day later and update the Unit counts on Subscriber__c?

 

Thoughts? Possible? Better solution given the additional details?

 

Thanks,

 

Hampton

HamptonHampton

I almost have this I think... I am getting an error stating:

 

Error: Compile Error: Field is not writeable: Site.Name at line 20 column 47

 

trigger SubscriberCount on Subscriber__c (after insert, after update, before delete) {

    // Set to hold the names of Address records
    Set<String> AddressNames = new Set<String>();
    // Set to hold the IDs of the Subscribers
    Set<ID> SubIDs = new Set<ID>();
    // Set to hold the IDs of the Sites where the values need to be reset
    Set<ID> SitesNamesForReset = new Set<ID>();

    if(trigger.isupdate || trigger.isInsert){
        for(Subscriber__c Sub : trigger.new ){
        
        
        // Querying the database to get the Number of Subscribers for each site
        AggregateResult[] Subscribersum = [select count(ID) NoOfSubscribers, Address_ID__r.Sac_Box__c Address
        from Subscriber__c where IPTV_C_Flag__c>0
        AND Address_ID__c in: AddressNames group by Address_ID__r.Sac_Box__c];

        for(AggregateResult AddressDetail : Subscribersum ){
            site SiteToUpdate = new site(Name=(String)(AddressDetail.get('Address')));
            SiteToUpdate.Video_Subs_New__c = integer.valueOf(AddressDetail.get('NoOfSubscribers'));
            SitesToUpdate.add(SiteToUpdate);
    }
   }
  }
 }

 What it should be doing there is pulling back all of the Site__c records where Site__c.Name=Address.

 

Thanks,

 

Hampton

 

Jerun JoseJerun Jose
You must have the Name field as an autonumber, so you cannot write values into it.

Change that to point to the field which stores the address values.

As for your initial query, I would still go with a trigger as it would be a more elegant solution which will work on the monthly load as well as on ad-hoc changes.