function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
David JahnkeDavid Jahnke 

Update Opportunity Stage with Workflow Trigger

I am looking for the easiest way to update the opportunity stage based on how many days we are away from the close date.

So if the opportunity has a stage of "Quoted" and
If close date is less than 30 days away set stage to HOT
If close date is 30 to 120 days away set to WARM
If close date is greater than 120 days away set to COLD

Thanks!
@anilbathula@@anilbathula@
HI David Jahnke,

If you want to achieve this with workflow you need to write 3 workflows.
Because Stage is a picklist field ,if you want a trigger then try this code.
 
trigger upd_stage on opportunity (before insert, before update) {

    for(Opportunity o : Trigger.new){
        If (o.stagename=='Quoted' &&( o.closedate-System.today())<30) {
            o.Stagename='Hot';
        }  elseif(o.stagename=='Quoted' &&( o.closedate-System.today())>30 &&( o.closedate-System.today())<120){
                o.Stagename='Warm';
       }elseif(o.stagename=='Quoted' &&( o.closedate-System.today())>120) {
                o.Stagename='Cold';
       }
    } 

}

There might be some typo errors please check before you use this code.

Thanks
Anil.B
David JahnkeDavid Jahnke
Thanks! I am very new to the whole workflow/trigger world. So this looks to me like it makes sense and that it would update the stage by itself. Does there still then need to be a workflow and what would it be? Does this run all the time in the background? If no opportunity is updated or viewed or edited and it crosses a date threshold does this get updated once a day or something else?
@anilbathula@@anilbathula@
Hi David,

There is no need of workflow if you use this trigger.
This will fire when ever a record is edited or created and if the conditions are meet then it will update the concerned field.

Thanks
Anil.B

 
Frédéric TrébuchetFrédéric Trébuchet
Hi,

Using a trigger is not the best option cause you have to update the records to change the field value.
If you don't update a record, you can encounter the situation in which Stagename is COLD and delay before closing date is only 1 day!
A formula field should be better cause it's evaluated each time you query the record, giving you the correct value even if the record have not been updated for a long time.
For example, you can define a custom field (let's say newStagename) as a text formula field with this kind of code:
IF(AND(ISPICKVAL(Stagename, "Quoted"), closedate -  TODAY() < 30), "Hot",
  IF(AND(ISPICKVAL(Stagename, "Quoted"), closedate -  TODAY() >= 30), "Warm",
    IF(AND(ISPICKVAL(Stagename, "Quoted"), closedate -  TODAY() > 120), "Cold", "N/A")))
Hope this helps,
Fred
11c-at-sfdc11c-at-sfdc
Fred is correct, David. You don't need any workflow. Just create a new formula field in Opportunity Object (Setup->Customize->Opportunities->Fields, then create a new formula custom field, and enter Fred's formula. You should be good to go!
Frédéric TrébuchetFrédéric Trébuchet
Hi,

Maybe there is a better way to write this formula but at least I think we have to arrange it like this if we want the correct result:
IF(AND(ISPICKVAL(Stagename, "Quoted"), closedate -  TODAY() < 30), "Hot",
  IF(AND(ISPICKVAL(Stagename, "Quoted"), closedate -  TODAY() > 120), "Cold",
    IF(AND(ISPICKVAL(Stagename, "Quoted"), closedate -  TODAY() >= 30), "Warm", "N/A")))
Hope this helps and happy new year!
Fred
 
David JahnkeDavid Jahnke
Hi All. Thanks for the help. What you are saying makes sense to me in part. I read this to say that this formula field will update on any query of the record. How does the actual stagename field get updated when this new formula field changes? Do I just set up a workflow to update stagename if this formula field changes?
11c-at-sfdc11c-at-sfdc
Can you live with creating a new formula field, say newstagename, and define the formula on that. The downside is that you will have stagename still out there with previous values such as 'quoted' etc. The up side is that a) it is a cleaner solution b) you are not messing up with the standard salesforce field, stagename, which may come handy in your future needs. All you need to is to train your staff in using the new , and starting using newstagename for your internal purposes.

If not, you have two (not so clean) options:
1. write an apex trigger but you have to figure out the exact trigger even that should fire off your change
2. define a workflow rule(s) on Opportunity Object, set formula (that evaluates to true or false) and update stagename to a pre-determined value. The downside is that you the worklow rule only fires in a create or edit situation.
 
David JahnkeDavid Jahnke
Thanks again. This is a great community. Avi-at-dfdc... We can certainly use a seperate formula field that would update on all queries 'thanks Frederic' to track a status but we have to have the actual stagename field update when that formula field changes. Would it work to have a workflow that updates "on saves or status changes' to the seperate formula field? Or is there a way to have a batch process of some kind run say once a day that would cause the workflows to then run if that field value changed? Updating once a day would be frequent enough for our purposes.
Frédéric TrébuchetFrédéric Trébuchet

Hi David,

You have to go with a daily batch cause no trigger fires when a formula field is evaluated (there is no update).

Good luck!
Fred
David JahnkeDavid Jahnke
I have successfully created a formula field that I call Days_from_close that does exactly that, enters the number of days from now to close date. Then I created three workflows to set the stage if less than 30 hot, 30 to 120 warm, over 120 cold. It works great! when I update any opportunity record close date the stage changes as needed.

How do I create a 'batch' to force this to update the records that have not been edited once per day?
Frédéric TrébuchetFrédéric Trébuchet
Hi,

Have a look at schedulable apex http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_scheduler.htmor System.scheduleBatch method http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_batch_interface.htm#apex_batch_scheduleBatch_section.
The idea is to to insert a record or update a field to fire your workflow at the scheduled time.

Fred
David JahnkeDavid Jahnke
Yes thanks but the reality is that this is the level at which I get lost. Not a software developer obviously. I do have a thought though. We have this application called Rollup Helper that I can configure fairly well. Could I just create a rollup number field called something like "days_toclosing_rollup" that queries my existing "days away from close date" formula field and enter the new value? If I were to schedule that to run once per day on the opportunity object would that in effect 'trigger' the record to save (because the value would indeed change every day) and thus create the result we are looking for? What do you think?
David JahnkeDavid Jahnke
No that does not work because you can only rollup data from related objects, not the same object.. oh well. Might have to hire someone.