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
lizmijohnlizmijohn 

Apex code to get count of child records whose value is not equal to 0

Hi,

 

I have a Parent object by name 'Project' and child related list by name 'Project Analysis'.

I have set the trigger on 'Project Analysis' so that I get the rollup summary on Project field 'Total Eq' & 'Total FM'.

The code is as follows and it works fine.

 

trigger EqSumTrigger on Project_Analysis__c(after delete, after insert, after undelete, after update) {
Decimal sumTotalEq = 0;
Decimal sumTotalFM = 0;
Decimal countFM = 0;
Decimal countEq = 0;
//sid = scl[0].Project_Analysis__c;
Project_Analysis__c[] cons;
if (Trigger.isDelete)
cons = Trigger.old;
else
cons = Trigger.new;

// get list of accounts
Set<ID> ProjectIds = new Set<ID>();
for (Project_Analysis__c con : cons) {
ProjectIds.add(con.Project1__c);
}

Map<ID, Project_Analysis__c> EqValue = new Map<ID, Project_Analysis__c>([select Id,Amount_per_month__c,FM__c from Project_Analysis__c where Project1__c in :ProjectIds]);

Map<ID, Project1__c> projectToUpdate = new Map<ID, Project1__c>([select Id,Total_Eq__c,Total_FM__c from Project1__c where Id in :ProjectIds]);



for (Project1__c coun : projectToUpdate.values()) {
Set<ID> conIds = new Set<ID>();
for (Project_Analysis__c con : EqValue.values()) {
sumTotalEq += con.Amount_per_month__c ;
sumTotalFM += con.FM__c;

}

coun.Total_Eq__c = sumTotalEq;
coun.Total_FM__c = sumTotalFM;

}

update projectToUpdate.values();

}

 

Now, I have 2  fields in Project by name 'EqCount' & 'FMCount'. This field should take the count of only those child records of which 'Amount_per_month__c' and 'FM__c ' are not equal to 0.

 

Please help.

Best Answer chosen by Admin (Salesforce Developers) 
lizmijohnlizmijohn

Hi,

 

I found a solution to this. Not sure if this is a long route.

 

I created 2 fields by name 'Eqtest' and 'FMtest' and activated a workflow on these fields.

 

Eqtest will return 1 if 'Amount_per_month__c' is greater than 0, else 1

FMtest will return 1 if 'FM__c' is greater than 0, else 1

 

Then take a rollup summary using trigger to get the total number of '1''s in each field.

 

The modified code is as follows:

 

trigger EqSumTrigger on Project_Analysis__c(after delete, after insert, after undelete, after update) {
    Decimal sumTotalEq = 0;
    Decimal sumTotalFM = 0;
    Decimal CountEq = 0;
    Decimal CountFM = 0;
   Project_Analysis__c[] cons;
    if (Trigger.isDelete) 
        cons = Trigger.old;
    else
        cons = Trigger.new;

    // get list of accounts
    Set<ID> ProjectIds = new Set<ID>();
    for (Project_Analysis__c con : cons) {
            ProjectIds.add(con.Project1__c);
    }
    
    Map<ID, Project_Analysis__c> EqValue = new Map<ID, Project_Analysis__c>([select Id,Amount_per_month__c,FM__c,Eqtest__c,FMtest__c from Project_Analysis__c where Project1__c in :ProjectIds]);

    Map<ID, Project1__c> projectToUpdate = new Map<ID, Project1__c>([select Id,Total_Eq__c,Total_FM__c,Eq_Count__c,FM_Count__c from Project1__c where Id in :ProjectIds]);
    
                                                               
    
    for (Project1__c coun : projectToUpdate.values()) {
        Set<ID> conIds = new Set<ID>();
        for (Project_Analysis__c con : EqValue.values()) {
            sumTotalEq += con.Amount_per_month__c  ;
            sumTotalFM += con.FM__c;
            CountEq += con.Eqtest__c;
            CountFM += con.FMtest__c;
        }
         
          coun.Total_Eq__c  = sumTotalEq;
          coun.Total_FM__c  = sumTotalFM;
          coun.Eq_Count__c  = CountEq;
          coun.FM_Count__c  = CountFM;
       } 

    update projectToUpdate.values();

}

Thanks,

Liz