Problem with Formula Field

[bjrenko]

We have a field called Status, that is a picklist, i.e. New, Hold, Assigned, Completed. We have cooresponding date/time fields (formula), called new_date, hold_date, assigned_date, completed_date. What we need is when the status changes, to set each of the date/time fields with the exact time that it changed. So if the status is set to new, the new_date field would be set to NOW. If the status changes to Hold, the hold_date field gets set to NOW, but the new_date field is left alone.

IF(ISPICKVAL(Status__c ,"Hold"),NOW(),NULL) 

Is the code that support provided, but what happens here is that when the status changes, the value gets Nulled out, not a good thing. What could we replace NULL with so that when the status changes to something other than what is defined in the formula, it does not reset the field?

Thanks.

[Jake Gmerek]

You can not use a formula field for this.  The easiest way would be to use workflow rules and field updates.  

Basically the workflow rule would fire when the status field changes and then you could potentially use the formula that you have to actually update the field instead of using a formula field.

[Tanvir Ansari]

You can do this by running javascript attached to a side bar component. Not an ideal solution. The javascript should attach even on the picklist field and populate the data values appropriately. This solution is only if your client wants to see the dates in the appropriate field populated before saving. Otherwise as suggested by Jake, you can do through workflow as well as triggers.

[Jake Gmerek]

I've gone down the JS sidebar route before, what I find tends to happen is that when you start adding functionality this way it becomes easy to keep adding functionality this way which results in a ton of JS running on every page load even the ones where it is not really doing anything, it still has to check to see if it should do something and can even slow down the overall speed of SF.  As a result, I tend to do this as a last resort, but I do agree that it is a viable option in this instance.

[Tanvir Ansari]

I agree that is why I wrote that it is not ideal solution. Although to prevent it from running on all pages, you can do a simple check to see if it should run on that page or not. That should be part of javascript.

[bjrenko]

We used workflow rules and that is the way to go for this issue. Simple but effective.

Thanks all.