+ Start a Discussion
Developer.mikie.Apex.StudentDeveloper.mikie.Apex.Student 

Trigger for sorting records into lookup fields Please help

Hi there,

I have three custom objects:

Transactions__c
Office_Commissions__c
Finance Commissions__c

Transactions and finance commissions have a lookup to office_commissions__c. Within Office_commissions__c there are two fields:

Commission_period_start__c and Commission_period_end__c. I was wondering if it is possible to craft a trigger which will use the date_of_payment__c fields on transactions and finance commissions to sort the records into the correct Office_commission__c record where the date_of_payment__c falls between the commission start and end?

Thank you in advance for your help.

Michael
Best Answer chosen by Developer.mikie.Apex.Student
Elie.RodrigueElie.Rodrigue
Its not the most bulkified way of doing it (i hate the for loop in a for loop) but here something that would work : (you also need to do the same trigger but for the transaction object.

 
trigger FinanceCommissions_MapToOfficeCommissions on Finance_Commissions__c (before insert, before update) {
    Date minDate = null;
    Date maxDate = null;
    //Figure out minimum and maximum date
    for(Finance_Commissions__c  fc:trigger.new)
    {
        fc.Office_Commissions__c = null;
        if(fc.Date_of_payment__c!=null)
        {
            if(minDate == null || fc.Date_of_payment__c < minDate)
            {
                minDate = fc.Date_of_payment__c;
            }
            if(maxDate == null || fc.Date_of_payment__c > maxDate)
            {
                 maxDate = fc.Date_of_payment__c;
            }
        }
    }
    System.Debug('Min date is : ');
    System.Debug(minDate);
    System.Debug('Max date is : ');
    System.Debug(maxDate);
    if(minDate != null && maxDate != null)
    {   
        //Get all office commission in that range
        List<Office_Commissions__c> officeCommissions = [Select Id, Commission_Period_Start__c, Commission_Period_End__c from Office_Commissions__c where Commission_Period_Start__c<=:minDate and Commission_Period_End__c>= :maxDate];
        if(officeCommissions.size()>0)
        {
            for(Finance_Commissions__c  fc:trigger.new)
            {
                
                if(fc.Date_of_payment__c!=null)
                {
                    //check if any office commission apply
                    for(Office_Commissions__c oc : officeCommissions )
                    {
                        if(fc.Date_of_payment__c >= oc.Commission_Period_Start__c &&fc.Date_of_payment__c <= oc.Commission_Period_End__c )
                        {
                            fc.Office_Commissions__c = oc.Id;
                            break;
                        }
                    }
                }
            }
        }
    }
}

All Answers

Elie.RodrigueElie.Rodrigue
Its not the most bulkified way of doing it (i hate the for loop in a for loop) but here something that would work : (you also need to do the same trigger but for the transaction object.

 
trigger FinanceCommissions_MapToOfficeCommissions on Finance_Commissions__c (before insert, before update) {
    Date minDate = null;
    Date maxDate = null;
    //Figure out minimum and maximum date
    for(Finance_Commissions__c  fc:trigger.new)
    {
        fc.Office_Commissions__c = null;
        if(fc.Date_of_payment__c!=null)
        {
            if(minDate == null || fc.Date_of_payment__c < minDate)
            {
                minDate = fc.Date_of_payment__c;
            }
            if(maxDate == null || fc.Date_of_payment__c > maxDate)
            {
                 maxDate = fc.Date_of_payment__c;
            }
        }
    }
    System.Debug('Min date is : ');
    System.Debug(minDate);
    System.Debug('Max date is : ');
    System.Debug(maxDate);
    if(minDate != null && maxDate != null)
    {   
        //Get all office commission in that range
        List<Office_Commissions__c> officeCommissions = [Select Id, Commission_Period_Start__c, Commission_Period_End__c from Office_Commissions__c where Commission_Period_Start__c<=:minDate and Commission_Period_End__c>= :maxDate];
        if(officeCommissions.size()>0)
        {
            for(Finance_Commissions__c  fc:trigger.new)
            {
                
                if(fc.Date_of_payment__c!=null)
                {
                    //check if any office commission apply
                    for(Office_Commissions__c oc : officeCommissions )
                    {
                        if(fc.Date_of_payment__c >= oc.Commission_Period_Start__c &&fc.Date_of_payment__c <= oc.Commission_Period_End__c )
                        {
                            fc.Office_Commissions__c = oc.Id;
                            break;
                        }
                    }
                }
            }
        }
    }
}
This was selected as the best answer
Developer.mikie.Apex.StudentDeveloper.mikie.Apex.Student

Hey Ellie,

First of all I just want to thank you for the assistance you have been giving me lately, you have been amazing. This is very impressive code:

I tried to save the code as a trigger but was greeted with this error:

Error: Compile Error: sObject type 'Office_Commissions__c' is not supported. If you are attempting to use a custom object, be sure to append the '__c' after the entity name. Please reference your WSDL or the describe call for the appropriate names. at line 27 column 57.


A similar error to what I received on my sorry excuse for a trigger:

trigger Transaction_GrabReference on Transaction__c (before insert, before update) {
for (Transaction__c  tra: trigger.new) {
       if(tra.Office_Commission__c != null) {
      
       DateTime refDate1 = OffCom.Commission_Period_Start__c;
       DateTime refDate2 = OffCom.Commission_Period_End__c;
      
      
           List<Office_Commission__c> OffCom = new List<Office_Commission__c>();
           OffCom = [Select Id from office_commission__c
           WHERE Transaction__c.Date_of_Payment__c > :refDate1  ORDER BY Id LIMIT 1];
              if (OffCom.size() > 0){
                 Tra.Office_Commission__c = OffCom[0].Id;
              }        
       }
    }
}

please dont judge me, haha it is based on a trigger that I created ages ago that uses a picklist selection to auto select the lookup field.

Thank you so much for your help

Elie.RodrigueElie.Rodrigue
Its my pleasure to help you out.

Just validate the api name of your office_commissions object
from your trigger i would say its office_commission__c instead of Office_Commissions__c.

Issue with your trigger is that its not bulkified, which mean that if it would be working, your soluton wouldnt scale well... If you would be to update 200 transactions in a single operation, it would failed with a too many soql query exception as you are doing one in the for loop.

There also some other logic issue in there but it may have to do with your picklist that i didnt see.


Developer.mikie.Apex.StudentDeveloper.mikie.Apex.Student

Ellie,

You are a genius my friend. It works perfectly, thank you so much. 

I have a request, would you perhaps be able to recommend how i may increase my apex knowledge. I feel it has increased alot just from posting on the forums, but I obviously still have a lot to learn.

Would you possibly be able to have a look at my old trigger (I have never had problems with it, but then again nor have I ever tried to mass insert or mass update records which it applies to), give me some notes on it, perhaps i could study your trigger (as they do similar things and attempt to bulk it up a bit).

 

Basically the trigger is designed to take note of the picklist choice upon record creation and set the lookup accordingly. This means that I can take the lookup from the record creation page and all dependent picklists stages and formula fields can be satisfied simultaneously. It references an object which holds information on the services (e.g. prices, descriptions, etc).

 

Trigger Service_GrabReference on Service__c (before insert, before update) {
    for (Service__c  dp: trigger.new) {
       if(dp.Service_Name__c != null) {
           List<Destiny_Products_and_Services__c> dpas = new List<Destiny_Products_and_Services__c>();
           dpas = [Select Id from Destiny_Products_and_Services__c WHERE Destiny_Products_and_Services__c.Name = :dp.Service_Name__c ORDER BY Id LIMIT 1];
              if (dpas.size() > 0){
                 dp.Destiny_Service__c = dpas[0].Id;
              }        
       }
    }
}

Thank you for all your help and please do not feel obligated with the trigger if you are busy.

 

 

Elie.RodrigueElie.Rodrigue
The best way to learn apex vary by how you learn thing :)
I come from early mobile device development so i know how to deal with low limits and why those are there and learned my way around from trial and error. My first few triggers were not following most of the best pratices but worked. Over time, they broke and I needed to fixed them, so the next one I built was made using that it mind.

If you learn from reading and following instructions, you can go through the apex workbook (Which might be below your level) : http://www.salesforce.com/us/developer/docs/apex_workbook/apex_workbook.pdf

Dan Appleman's Advanced Apex Programming is a really good / technical book you need to go through : http://advancedapex.com/

Other than that I like toying with ideas in developer editions. Usually its stuff like I'm thinking that on my local business would benefit from using salesforce, so i create a complete environment that they could use, just for fun; without even talking with them. It makes me play around features I wouldnt have touch in other situation.

I just wrote a blog post basically about that  (thanks for the inspiration!) : http://elierodrigue.com/2014/02/20/how-to-learn-get-better-at-apex-programming/

The main issue with your trigger is that you have an soql query in your for loop. Try to get it out of there. Figure out every records you'll need to query. Query them all once, then get back in your service__c list and find back the object you need from your query results.

Developer.mikie.Apex.StudentDeveloper.mikie.Apex.Student
Thank you for your advice Ellie, I will definitely look into it.

Did you mean something along these lines:

Trigger Service_GrabReference on Service__c (before insert, before update) {
    List<Destiny_Products_and_Services__c> dpas = new List<Destiny_Products_and_Services__c>();
           dpas = [Select Id from Destiny_Products_and_Services__c WHERE Destiny_Products_and_Services__c.Name = :dp.Service_Name__c ORDER BY Id LIMIT 1];
for (Service__c  dp: trigger.new) {
       if(dp.Service_Name__c != null) {
       
              if (dpas.size() > 0){
                 dp.Destiny_Service__c = dpas[0].Id;
              }       
       }
    }
}