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
chubsubchubsub 

Opportunity Trigger Help needed

Hello, I have a requirment to update the opportunity name based on the number of opportunities that have been created that day for that client.  I'm fairly new to apex triggers and wanted to find out how to get started constructing this, any advice or direction would be awesome.  Here is some more details:

 

Opportunity is created on 11/11/2011 for Client A.  The users enters "Big Wammy" into the Opportunity Name field.  Before Insert, the trigger will update the opportunity Name to  "Big Wammy - 11/11/2011 - 001"  Then, another opportunity is created on the same day for the same Account and the user enters "Small Deal" into the opportunity name field, now the trigger will update the name to "Small Deal - 11/11/2011 - 002". 

 

Now, the next day, on 11/12/2011, the user creates another opportunity for Client A and enteres "Big Deal" in the Opportunity Name field.  Now, the trigger will update the Name to "Big Deal - 11/12/2011 - 001".  The number on the end will need to reset back to 001 every day.  

 

My trigger will be on the Opportunity and will need to query for all opportunities created that day for that account, and put them in order.  

 

Any suggestions on how to get started?  Thanks in advance!

Best Answer chosen by Admin (Salesforce Developers) 
BritishBoyinDCBritishBoyinDC

Ah, I worked out what's happening...the code assumes there are no Opportunities for today already in the system...but if there are, it is trying to set the tracking number to a null value...thus the error.

 

So you either update the existing records for today to have a sequential number, or change line 9 to read:

Opportunity [] todaysoptys =  [Select Id, Daily_Tracking_Number__c from Opportunity where CreatedDate = TODAY AND Daily_Tracking_Number__c != null order by Daily_Tracking_Number__c Desc];

 Any existing opportunities for today will remain blank, but new ones will start at 1...

 

All Answers

BritishBoyinDCBritishBoyinDC

Create a field on Opportunity  called Daily Tracking Number...this code will then set that number for each day and increment it for each new opportunity, and you can then add some code to update the name in the final loop, or do it via workflow, which ever is easier...

 

Trigger:

 

trigger ManageOpportunities on Opportunity (before insert) { 

    if(Trigger.isInsert && Trigger.isBefore){
        ManageOpportunities.beforeInsert(Trigger.New); 
    }     
} //end trigger

 

 

Managing Class

 

public with sharing class ManageOpportunities {

//Tracking Numbers are reset each day, so we'll query for the current number and add to it for the optys
public static void beforeInsert(Opportunity [] optys) {

Decimal todaystrackingnumber;
//get the current number - there might not be one, so we'll query into a list

Opportunity [] todaysoptys =  [Select Id, Daily_Tracking_Number__c from Opportunity where CreatedDate = TODAY order by Daily_Tracking_Number__c Desc];

//if there is a case, then use the first one, since it is ordered by number
    if (todaysoptys.size() > 0) {
        todaystrackingnumber = todaysoptys[0].Daily_Tracking_Number__c + 1;
    }
    else {
        todaystrackingnumber = 1;
    }

//Now loop through the new cases and allocate a number

for (Opportunity o: optys) {
o.Daily_Tracking_Number__c = todaystrackingnumber;
todaystrackingnumber ++;
}
    
} //end before insert


} //end class

 
chubsubchubsub

Many thanks for the snippet BrithisBoyinDC, I received this error message, any ideas?

 

 execution of BeforeInsert caused by: System.NullPointerException: Attempt to de-reference a null object: Class.ManageOpportunities.beforeInsert: line 13, column 1
BritishBoyinDCBritishBoyinDC

Ah, I worked out what's happening...the code assumes there are no Opportunities for today already in the system...but if there are, it is trying to set the tracking number to a null value...thus the error.

 

So you either update the existing records for today to have a sequential number, or change line 9 to read:

Opportunity [] todaysoptys =  [Select Id, Daily_Tracking_Number__c from Opportunity where CreatedDate = TODAY AND Daily_Tracking_Number__c != null order by Daily_Tracking_Number__c Desc];

 Any existing opportunities for today will remain blank, but new ones will start at 1...

 

This was selected as the best answer
chubsubchubsub

That line was the culprit, it works now, but is applied to all Opportunities created regardless of the Account.  Now I need to figure out how to only reset the daily tracking number by Date and by Account.  So, when I create another opportunity against another account, it will start back to 1. I'm thinking the Account ID needs to be added to the query.  Thanks again for getting me started, this is great! 
chubsubchubsub

Hey BritishBoyinDC, I was wondering if you could offer me some guidance on updating the daily tracking number field on opportunities records from the past.  I'm trying to wright a before update trigger that updates the daily tracking number on old opportunities in order by the date it was created and by account.

 

I was also wondering if you were looking for full-time employment?  I noticed you're in the DC area, our company is in Annapolis.

 

Scenario:

 

Client A:

Opportunity 1 created 1-5-2011 - Trigger will update Daily tracking number to 1

Opportunjity 2 created 2-5-2011 - Daily tracking number will equal 2

Opporuinty 3 created 3-5-2001 (but a date is in the SOF_Requested_Date__c) - Daily tracking number will remain 0 or Null

Opportunity 4 created 4-5-2011 - Daily Tracking number will equal 3

 

Client B

Opportunity 1 created 2-5-2011 - Daily tracking number will equal 1

Opportunjity 2 created 4-5-2011 - Daily tracking number will equal 2

 

Below is my Before Update code I've attempted so far, but it's not working properly.  I don't think it's returning any results.

 

public static void beforeUpdate(list<Opportunity> optys)
    {
        set<Id> accids = new set<id>();
        set<Date> createdDates = new set<Date>();
        
        for (Opportunity o : optys)
        {
            if (o.SOF_Requested_Date__c == null)
            {
                accids.add(o.AccountId);
                createdDates.add(o.CreatedDate.date());
            }
        }
        
        //this will include the opps we need to count
        
        Id thisaccid;
        Date thisdate;


        list<Opportunity> allopps = [select Id, AccountId, CreatedDate
            from Opportunity
            where AccountId in :accids
                and CreatedDate in :createdDates 
            order by AccountId, CreatedDate];


        map<Id, Integer> oppid2seqnummap = new map<Id, Integer>();
        
        //reset placeholders: last Account, last Date, current counter
        if(allopps.size()>0){
                 thisaccid = allopps[0].AccountId;
                 thisdate = allopps[0].CreatedDate.date();
                    }
        Integer counter = 0;


        //keep track of all sequence numbers
        for (Opportunity o : allopps)
        {
            
            //same account as the last one?
            if (thisaccid == o.AccountID)
            {
                //same date as the last one?
                if (thisdate == o.CreatedDate.date())
                {
                    counter ++;
                }
                else //new date
                {
                    counter = 1;
                }
            }
            else //new account
            {
                counter = 1;
            }
            
            //store counter for this Opp
            oppid2seqnummap.put(o.Id, counter);
            
            //reset to compare to next Opp
            thisdate = o.CreatedDate.date();
            thisaccid = o.AccountId;
        }
        
        //now go back through passed Opp list
        for (Opportunity o :optys)
        {
            Integer seqnum = oppid2seqnummap.get(o.Id);


o.Daily_Tracking_Number__c = seqnum;




        }
        
    } //end before update

 
BritishBoyinDCBritishBoyinDC

Ah, I have my own consulting business in Arlington, VA, but thanks for the offer! 

 

Re this requirement, you don't say what happens when there are two opportunities for the same client on the same day? 

 

E.g. for Client A:

 

Opportunity 1 created 1-5-2011 - Trigger will update Daily tracking number to 1

Opportunjity 2 created 2-5-2011 - Daily tracking number will equal 2

Opportunity 2a also created 2-5-2011 - Daily tracking number will equal ?

Opporuinty 3 created 3-5-2001 (but a date is in the SOF_Requested_Date__c) - Daily tracking number will remain 0 or Null

Opportunity 4 created 4-5-2011 - Daily Tracking number will equal 3

 

Either way, I would use Aggregation Queries for this - if you haven't used them, take a look in the DOCS (APEX and SOQL)  - that will let you group by date and account, and order the results, so you can then just loop through results, and probably create a Map of Maps - for each Account, a map of Date to Tracking Number - then you can loop back through results, and for each account, look for the map for created date of the Opty you are processing, and get the tracking number you need...

 

 
chubsubchubsub

Thanks again, I'll look into the Aggregation queries, that's how I was logically thinking I could accomplish this, ordering the query by Account and Created Date and assign the tracking number.  The Created Date has a Time stamp as well, so if 2 were created in the same day, hopefully they were'nt created exactly the same time.