function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
SabrentSabrent 

Rollup summary (Count) - Lookup Relationship

I have a lookup relationship between Programs__c (parent) and Requirement__c (child)

On Requirement__c  I have a picklist field called waived__c
On Programs__c  I have a picklist field called Status__c and a Text Field called Accept__c

 

I want to create a Roll up field on Programs__c called Count_Numberof_Waived_Requirements__c based on the following criteria -

 

result = SELECT Count(Id) FROM Requirements__c Where Waived__c = 'yes' AND Status__c IN ('Active', 'Enrolling')

 

and then populate the Accept__c field,

 

If (result >0){

    Accept__c = 'Yes';

}

 

 

Any suggestions or help will be appreciated.


Thanks.

Best Answer chosen by Admin (Salesforce Developers) 
TheIntegratorTheIntegrator

A sample would be something like this

trigger requirementsTrigger on Requirements__c (after insert, after update) {
    List <Id> progIds = new List<Id> ();
    List <Programs__c> programs = new List<Programs__c>();
    List <AggregateResult> requirements = new List<AggregateResult>();
    
    for(Requirements__c req:trigger.new){
        progIds.add(req.Programs__c);
    }
    
    programs = [Select Id, Count__c From Programs__c Where Id In :progIds];
    requirements = [Select Programs__c, Count(Id) From Requirements__c Where Programs__c IN: progIds
            AND Waived__c = 'Yes' AND Status__c IN ('Active', 'Enrolling')
            Group By Programs__c];
    for(AggregateResult ar: requirements){
        for(Programs__c p:programs){
            if(ar.get('Programs__c') == p.Id){
                p.Count__c = Decimal.ValueOf(String.ValueOf(ar.get('expr0')));
            }
        }
    }
    update(programs);
}

 

You will need to modify this code to update count when a requirement record was previously matching criteria but now no longer does.

 

All Answers

TheIntegratorTheIntegrator

You cannot do this using roll-up summary when the relationship is lookup. You either have to change the relationship to master-detail and acheive this using trigger.

 

If you change to master-detail, you can simply create a roll-up summary field on programs with summarized object requirements, roll-up type COUNT then filter criteria with "Only records meeting certain criteria should be included in the calculation" , select Waived equals Yes and Status equals Active, Enrolling

 

Also, I'm assuming the status field is on requirements and not on programs as you point out.

 

You can then create Accept on programs object as a text formula

IF( roll__c >0, 'Yes', 'No') , where roll__c is the rollup summary
SabrentSabrent

THanks for the response. 

 

I have used Rollup for Master-Detail many times in the past, In this case it's a look up relationship and given the complexity of our application, I don't want to change it to Master-Detail. 

 

I know this is possible only with a trigger. Saw some examples of trigger with SUM but not any with COUNT 

 

I would appreciate if some one can help me out or point me in a right direction to create a trigger that can  do a summary COUNT.

 

THanks.

 

 
TheIntegratorTheIntegrator

A sample would be something like this

trigger requirementsTrigger on Requirements__c (after insert, after update) {
    List <Id> progIds = new List<Id> ();
    List <Programs__c> programs = new List<Programs__c>();
    List <AggregateResult> requirements = new List<AggregateResult>();
    
    for(Requirements__c req:trigger.new){
        progIds.add(req.Programs__c);
    }
    
    programs = [Select Id, Count__c From Programs__c Where Id In :progIds];
    requirements = [Select Programs__c, Count(Id) From Requirements__c Where Programs__c IN: progIds
            AND Waived__c = 'Yes' AND Status__c IN ('Active', 'Enrolling')
            Group By Programs__c];
    for(AggregateResult ar: requirements){
        for(Programs__c p:programs){
            if(ar.get('Programs__c') == p.Id){
                p.Count__c = Decimal.ValueOf(String.ValueOf(ar.get('expr0')));
            }
        }
    }
    update(programs);
}

 

You will need to modify this code to update count when a requirement record was previously matching criteria but now no longer does.

 

This was selected as the best answer
SabrentSabrent

Thanks a lot, I really appreciate your help. 

 

Will try and tweak as needed. Will post here how I go. 
SabrentSabrent

Thank a lot . This worked out well. Provided a great baseline for my requirement.

I will tweak it further to exactly match my requirement.

Once again Thanks!!!!
ItsMeSolItsMeSol

Hi Rov,

 

Can you help me to solve the problems in Trigger in Junction Object.

Here is the scenario: I have a 3(A,B,C, B is Junction object) custom objects, I want to get the sum of all record from object A . Can you give me an idea. I just new in coding in salesforce.