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
RajevlsRajevls 

Formula Field Not working

I want to update two formula fields based a value selected in a third field(picklist field)

 

 

1. Status is a picklist field ---  when status is selected as 'Done' 

2. Completed - Formula  Date field -  would have the value

3. Open date  - Formula number field -  when Completed has a value it would populate the no. of days between the created date and Completed date value .

 If completed date has no value then Open date will have value  - Today  - Created date .

 

I have written this formula :

 

1. For Completed : 

 

IF( ISPICKVAL(Status ,'DONE'), Today(), NULL)

 

It is populating the value but not stoppping . IT is crossing the date and updating current date . IT should popualte the dae when Status is selected as DONE .

 

2. For Open Date :

 

IF(
isnull(IF( ISPICKVAL(Status ,'DONE'), Today(), NULL)),
ROUND(NOW()-CreatedDate, 0),
ROUND(((IF( ISPICKVAL(Status ,'DONE'), Today(), NULL))-DATEVALUE(CreatedDate)),0)
)

 

i am using IF( ISPICKVAL(Status ,'DONE'), Today(), NULL) because one firmula field is not called in other .

 

Here the Open date keeps on populating data based on the created date .

 

EX : 

 

If the record is createOCT 20  , status is changed to DONE on oct 22 

 

So the completed field should have OCT 22 as the date and 

Open date should have 2 as the value

 

but it showing the calculation based on todays date (27th OCT)

 

completed has oct 27 as the value and Open date has 7 as the value .

 

PLZ help here

 

egoldenSFDCegoldenSFDC
Hi Rajevls,

Your formula for completed will continue to evaluate the if statement as true and update the field to TODAY each time the record is loaded. Try adding an additional requirement to he IF (via an AND perhaps) that checks whether status has just changed to Done from a "not done" status. In addition, have the false value default to the previous value of the field (an additional surrounding IF to default it to NULL might b necessary as well depending on your use case). Traditionally this may also be accomplished via workflow or trigger. Please let me know if u need more detail. Thanks!
RajevlsRajevls

can i have the formula .. i am not getting it . Thanks.
egoldenSFDCegoldenSFDC

It looks like I was mistaken and PRIORVALUE cannot be used in formula fields.  The best route would probably be to use a workflow rule on the object in question that will update the Completed field when the Status field is changed to Done.

 

HTH