We're looking to auto increment a number value field on Cases by 1 for every day the case has been open (i.e. auto-increment as long as the case is not closed).
What would be the best way to go about this? A time based workflow action wouldn't get me what I need. I tried a formula field, but, I'm not sure that it would work without referencing the formula field itself.
Does anyone have any ideas?
I would simply use a formula field to calculate this. Should be simple enough for calculating Case age regardless of business hours. An example from the salesforce docs:
https://na14.salesforce.com/help/doc/en/useful_advanced_formulas.htm#CaseAgingAllCases
If you need business hours involved, then a more complex formula is needed like in the link below.
https://success.salesforce.com/questionDetail?qId=a1X30000000HYXJEA4
You can create a formula field to do this
IF(IsClosed, ROUND(ClosedDate - CreatedDate, 0), ROUND((NOW() - CreatedDate),0))
https://success.salesforce.com/questionDetail?qId=a1X30000000HYXJEA4
