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
ppiyushppiyush 

How to detect changes in formula field value?

Hello!

 

I am trying to figure out a way through which I could trigger a workflow based on the value contained inside a formula field. I obviously can create a normal workflow, but that would allow me only to check the value at the instance when a record is created or edited. Is there any way of doing it EVERY time the value of a particular field changes?

 

Is there a way to do this in Apex or through custom fields / workflows?

 

Best,

Pranav

SurekaSureka

Hi,

 

As of now, I could think of doing by Apex Triggers. Below is the sample code:

 

trigger CheckFormual on Account (before insert, before update) 
{
    for(Account a:Trigger.new)
    {
        if(system.trigger.OldMap.get(a.Id).FormulaFeild__c != system.trigger.NewMap.get(a.Id).FormulaFeild__c)
        {
            a.AnotherFeild__c = a.FormulaFeild__c;
        }
    }
}

 

In the above code, whenever there is a change in the formula feild I am populating that value to another feild. Likewise you can track.

 

 

Hope this helps.

 

Thanks
ppiyushppiyush

Sureka,

 

this is good - just a technical question - when a formula field value changes itself (based on the input), does this count as an "update" on the record?

 

Also, will the last modified date change because of a formula field recalculation?

 

Thanks,

Pranav
ca_peterson_oldca_peterson_old
No, a formula field changing doesn't change the updated by, and doesn't count as a record update. Behind the scenes formula fields aren't stored as part of the record in the database, instead they use logic embedded in SQL at query time, so the values don't persist, instead they're generated on the fly when the object is queried.
ppiyushppiyush

I thought so as well...

 

In this case, do you know how my original problem can be solved? I basically want to update a Picklist Field based on the Formula Field's value. That too, not just when a record is created or edited, but every time the formula fields value changes!
Jeremy_nJeremy_n

As stated, you can't hook into changes in the value of the formula field itself, because it it's stored as a formula, not a value. However, you can look for changes in what makes that field change.

 

For example, if you have a formula on Opportunity that will change on the basis of a change in the associated Account, then you *can* look for changes in that Account field. Set a Trigger on the object that holds the changing data, and do whatever you need to from there.

 

Does this help you? If not, maybe you can give more details on the field you want to track.

 

Jeremy
ppiyushppiyush

Jeremy,

 

thanks for your help. Here is my requirement:

 

On the opportunity object, we have two fields = Start_Date, and End_Date. We use these fields to indicate the beginning and end of a project we sell to our clients.

 

Now for all our reporting purposes, we have a classification called "Freshness", which is currently a formula field based on the following formula:

 

- if Start_Date's month is current month, Freshness = New

- if End_Date's month is current month, Freshness = Expiring this month

- if Start_Date<This Month && End_Date > This Month, Freshness = Ongoing

 

Now, when I chart the Opportunity, and stack the group using Freshness (formula field), I am unable to define specific colors / order of the stacks. I know that you can assign colors to picklist values, and maintain order in which they are displayed in any dashboards / charts.

 

Hence, I want to achieve the result of the Freshness field but in a PickList field.

 

I hope my query is clear?
Jeremy_nJeremy_n

It's possible you could do it through Time-Based Workflows. I think this will only work if the End Date is within a year of the workflow trigger date, though.

 

Set a Workflow Rule to fire when Opportunity is saved and Start Date is current month. This will

1) set the Freshness picklist to "New" immediately, and

2) register a time-dependent field update to change the Freshness to Ongoing on the Start Date

3) register a time-dependent field update to change Freshness to Expiring this Month one month before the End Date

 

 

If that won't work for you, you could write a scheduled Apex batch that goes through Opportunities and changes the picklists as necessary, running every night. This would be a little more complicated, but could handle any date ranges.

 

Good luck,

Jeremy
ppiyushppiyush

Jeremy,

 

The first option works - but not in all cases. I think I will have to write the apex batch code as you mentioned. Can you point me to some examples, if you know of any?

 

I have written some apex code around batches before - but was triggering the batche updates using buttons. How do you create a schedule to run the batches on a nightly basis?

 

Best,

Pranav
Jeremy_nJeremy_n

If you've got an Apex batch process already created, you just need to set up a class that implements the Schedulable interface. This class just needs one method, execute(). Here's a basic schedulable class I've got:

 

 

global class MonitorNightlyTasks implements Schedulable{
    
    global void execute(SchedulableContext sc)
    {
        NightlyTasksBatch nt = new NightlyTasksBatch();
        database.executebatch(nt);
    }
}

 Then you can schedule when this will run from the UI in Setup/Develop/Classes, then click "Schedule Apex".

 

Jeremy