Apex Code Development You need to sign in to do that
Don't have an account?
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
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
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
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;
}
}
}
}
}
}
}
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
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.
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.
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.
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;
}
}
}
}