Aggregate functions like min and max in lookup relationship

Question

I had two custom objects Organization__c (parent) and Employee__c (child) which were related using lookup relation ship [lookup:orgnitation__c].

fields for Organization__c: name, total_slary__c, min_salary__c,max_salary__c

fields for employee__c: Salary__c

Now for example, if i have records like

EmployeeAutoNumber ---------Organization Name------Salary__c

emp01-----------------------------------H.R---------------------100
emp02-----------------------------------H.R---------------------200
emp03-----------------------------------H.R----------------------50
emp04-----------------------------------H.R----------------------25
emp05-----------------------------------Labour------------------625
emp06-----------------------------------Labour------------------825
emp07-----------------------------------Electricity-------------925
emp08-----------------------------------Electricity-------------250

how can i write a trigger to update the min_salary__c or max_salary__c in organization__c based on the salaried of the respective organization? In the above example the min_salary__C for H.R organization is 25 and max_salary__c is 200.How can i update in the HR organization using triggers ?

Answer 1

I changed my trigger like this to make it work for both insert and delete.

trigger rollupTrigger on Employee__c (after insert, after update, after delete) 
{
    Set<Id> orgIds = new Set<Id>();
    List< Organization__c> updates = new List<Organization__c>();
    Employee__c[] emp = null;

    if(Trigger.isInsert||Trigger.isUpdate)
    {
        emp = Trigger.new;
    }
    else if(Trigger.isDelete)
    {
        emp = Trigger.old;
    }

    for (Employee__c e : emp) 
    {
        if(e.Organization__c != null)
            orgIds.add(e.Organization__c);
    }

    for (AggregateResult ar : [select Organization__c i, sum(Salary__c) su, 
        min(Salary__c) mi, max(Salary__c) ma,count(id) ids from employee__c 
        where Organization__c in :orgIds group by Organization__c])
    {
        updates.add(new Organization__c(Id = (Id)ar.get('i'),
            totalsalary__c = (Decimal)ar.get('su'),min_salary__c = (Decimal)ar.get('mi'),
            max_salary__c = (Decimal)ar.get('ma'),total_emp_in_org__c = (Decimal)ar.get('ids')));
    }

    update updates;
}

Answer 2

trigger EmployeeTrigger1 on Employee__c (after insert,after update,after delete,after undelete)
{
    list<Organization__c> Orgvalues = new list<Organization__c>();
    set<ID> ORGIds = new set<ID>();
    map<id,Decimal> Sumvalues= new map<id,Decimal>();
    map<id,Decimal> MinValues= new map<id,Decimal>();
    map<id,Decimal> Maxvalues= new map<id,Decimal>();

    if(trigger.isinsert || trigger.isupdate || trigger.isundelete)
    {
        for(Employee__c Emprecords:trigger.new)
        {
            ORGIds.add(Emprecords.Organization__c);
        }    
    }
    if(trigger.isdelete || trigger.isupdate)
    {
         for(Employee__c Emprecords1:trigger.new)
         {
             ORGIds.add(Emprecords1.Organization__c);
         }  
    }
    list<AggregateResult > Outputvalues = [select Organization__c Acc,sum(Salary__c) Sumdata,min(Salary__c) MIndata,Max(Salary__c) Maxdata from Employee__c where Organization__c IN:ORGIds group by Organization__c];
    for(AggregateResult Ar:Outputvalues)
    {
        Sumvalues.put((id)Ar.get('Acc'),(Decimal)Ar.get('Sumdata'));
        MinValues.put((id)Ar.get('Acc'),(Decimal)Ar.get('MIndata'));
        Maxvalues.put((id)Ar.get('Acc'),(Decimal)Ar.get('Maxdata'));

    }
    for(Organization__c acc1:[select id,Name,Max_Salary__c,Min_Value__c,Sum_Of_Salary__c from Organization__c where ID IN:ORGIds])
    {
       acc1.Sum_Of_Salary__c = Sumvalues.get(acc1.ID);
       acc1.Min_Value__c = MinValues.get(acc1.ID);
       acc1.Max_Salary__c = Maxvalues.get(acc1.ID);
       Orgvalues.add(acc1);

    }
    update Orgvalues;
}

Answer 3

Trigger Handler:

public class EmployeeTriggerHandler {
    public static void handler(List<Employee__c> emp) {
        Set<Id> empIds = new Set<Id>();
        List< Account> lacc = new List<Account>(); 
        List<Account> alis=new List<Account>();
        for (Employee__c e : emp) {
            if(e.Account__c != null)
                empIds.add(e.Account__c);
        }
        for (AggregateResult ar : [select Account__c i, max(Salary__c) ma from Employee__c where Account__c in :empIds group by Account__c]) {
            lacc.add(new Account(Id = (Id)ar.get('i'), max_salary__c = (Decimal)ar.get('ma'))); 
        }
        update lacc;  
        for(Account a:[select id,max_salary__c,Costly_Employee_name__c,(select Name,Salary__c,Account__c from Employees__r) from Account where id in :empIds]) {                    
            for(Employee__c e:a.Employees__r) {   
                if(a.max_salary__c==e.Salary__c) {
                    a.Costly_Employee_name__c=e.Name;                   
                    alis.add(a);    
                }
            }
        }
        update alis;              
    }
}