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
SeanOSeanO 

Help with adding up a field

We run advertising campagins and sometimes they get paused.  I am trying create a field that tells me how many days a campagin has been Paused

So I created 2 fields

"Pause Date" and "Un-Pause Date"

I created Workflows that when the Stage changes to Paused it puts todays date in the "Paused" Field and when its Unpaused it puts today's Date in the "Unpaused" Field.  

I then Created a Formula Field "Days Paused" which subtracts the Unpaused Date from the Paused Date.  This all works perfectly.

 

My Problem is what happens if it gets paused more then once.  How do I keep adding to the "Days Paused" field because the pause date and unpause date fields will be changeing as things get paused and unpaused

Best Answer chosen by Admin (Salesforce Developers) 
ashishkrashishkr
Also in your object settings, keep the default value of paused_time__c field as 0. The above formula would fail if it encounters a null value in the paused_time__c field. A more robust formula would be this:
if( ISNULL( PRIORVALUE( paused_time__c ) ),0,PRIORVALUE( paused_time__c ) )+ (TODAY() - Pause_Date__c )

This first checks if the previous paused_time__c is null, if so, use 0 for the calculation.

All Answers

ashishkrashishkr

Your doubt is legit. The formula would only show the latest duration of the pause/unpause events. Use another workflow instead if you just want a count of paused days.

This workflow would run when formula evaluates to true. formula would be just to check whether the Stage has changed from Paused to Unpaused.(use  PRIORVALUE for this.) This workflow would result in a field update to a number field as below:

 

x = x + today()-paused_date.
SeanOSeanO

I am new to salesforce can you please give me more insight on exactly how this formula should be written.  Thanks in advance
ashishkrashishkr
PRIORVALUE( numberField__c ) +( TODAY() - Pause_Date__c )

Here numberfield__c is of type number.
SeanOSeanO

Sorry if I am being a pain but When i put that formual in I get an error that says 

 

 

Error: Formula result is data type (Number), incompatible with expected data type (true or false).

 

Not sure what to do.  

 

Thanks again in advance
ashishkrashishkr

The field that you choose to update must also be a number type.
SeanOSeanO

i can't seem to get this to work.  i keep getting that error it definatly is a number field
ashishkrashishkr

Ok. Perhaps I misunderstood where you were getting the error.

These are the steps to create a workflow:

 

Step 1: Select Object 

- Select the object you want your workflow to act on (You would select the Campaign object)


Step 2: Configure Workflow Rule

Give it a name & description
Evaluate Rule "everytime a record is created or edited"

Formula Evaluates to True:

Formula here: AND(ISPICKVAL(PRIORVALUE( Stage__c  ), 'Paused'),ISPICKVAL( Stage__c  , 'Unpaused'))  (Assuming you Stage is a picklist.) This formula will return a true or false value.

click on Check Syntax

Save & Next


Step 3: Specify Workflow Actions

Immediate Workflow Action
Add Workflow Action: New Field Update

Name the new field update
select the field to update: paused_time__c (a number field)
formula here:  PRIORVALUE( paused_time__c ) + (TODAY() - Pause_Date__c )  This would return a number.

 

Hope this solves your issue.
ashishkrashishkr
Also in your object settings, keep the default value of paused_time__c field as 0. The above formula would fail if it encounters a null value in the paused_time__c field. A more robust formula would be this:
if( ISNULL( PRIORVALUE( paused_time__c ) ),0,PRIORVALUE( paused_time__c ) )+ (TODAY() - Pause_Date__c )

This first checks if the previous paused_time__c is null, if so, use 0 for the calculation.
This was selected as the best answer
Shivanath DevnarayananShivanath Devnarayanan

There are couple of solutions that come to my mind.

1) As you probably may know, salesforce keeps track of field histories, you could run a report off the histories table for this particular object to understand when all were the values changed and advantage is you could also know from what value it was changed. Make sure those fields are marked on Track history for that object. 

2) this is a more detailed way : it is to implement your own histories object, you could create this custom object and downside is you will have to have a trigger to create new record when the values change.

If your business agree with the report i'd suggest that would save you a lot of time.

hope it helps.
SeanOSeanO

That worked thank you for are you help.  I really learned a lot from that.