3

I have a formula field (Hours_After_Case_Updated__c) which calculates hours after the the case was last modified excluding weekends (Saturday and Sunday). I want to trigger a time based workflow which triggers if the formula field is greater than or equal to 24 hours and if the case is open. Workflow action : Email Alert

I tried using the following rule criteria:

AND( 
    OR (ISPICKVAL(Status, 'New'), ISPICKVAL(Status, 'In Progress')),
    Hours_After_Case_Updated__c >=24
)

And used Time Trigger as: Snapshot of time trigger

But workflow is not working.

Formula for calculating Hours_After_Case_Updated__c is as follows :

ROUND( 24 * (
( 5 * FLOOR( ( DATEVALUE( NOW() ) - DATE( 1900, 1, 8) ) / 7) +
MIN(5,
MOD( DATEVALUE( NOW() ) - DATE( 1900, 1, 8), 7) +
MIN( 1, 24 / 24 * ( MOD( NOW() - DATETIMEVALUE( '1900-01-08 7:00:00' ), 1 ) ) )
)
)
-
( 5 * FLOOR( ( DATEVALUE( LastModifiedDate ) - DATE( 1900, 1, 8) ) / 7) +
MIN( 5,
MOD( DATEVALUE( LastModifiedDate ) - DATE( 1996, 1, 1), 7 ) +
MIN( 1, 24 / 24 * ( MOD( LastModifiedDate - DATETIMEVALUE( '1900-0107:00:00' ),       
1) ) )
)
)
),
0 )

1 Answer 1

1

What are you doing to make your formula recalculate? I don't think it will just keep adding an hour onto the number every hour and then checking if it needs to prompt the workflow.
1. The formula will only be calculated when you view the record
2. The workflow will only fire when you save the record

So you could maybe run a batch that UPDATEs all the relevant records every hour, as this would kick the workflow.

But I think a better way is to remove Hours_After_Case_Updated__c from the criteria, and change your Timed Workflow so that it runs 24 hours after the LastModifiedDate. When the record is modified, a task will be queued for "24 hours from now". If the record is modified again before the 24 hours is up, the existing task goes away and a new one is queued for "24 hours from now". So there is always a task waiting to happen if the 24 hours do ever expire. There will only ever be one task waiting to happen for any particular Case. As soon as the status is changed so it is not 'New' or 'In Progress' the record does not meet the criteria any more, so the waiting task goes away.

4
  • Hi Dominic. Thanks for the reponse. Actually I am using formula field Hours_After_Case_Updated__c for calculating hours after the the case was last modified excluding Weekends.So a normal time based workflow is not suitable.Therefore, I have used the formula field in the criteria. I have modified the question appropriately.
    – Priyanka
    Jun 20, 2014 at 5:16
  • Could you have a separate rule if the LastModified date was a friday? With a Timed Workflow of 72 hours?
    – Dominic
    Jun 20, 2014 at 12:11
  • But then we will also need separate workflow for Wednesday & Thursday also
    – Priyanka
    Jun 24, 2014 at 6:10
  • Would you? Only Friday has a weekend within 24 hours of it. Wednesday and Thursday don't. I may be misunderstanding the requirement. I do think that if you absolutely need to use your formula, you need to touch the records to ensure it is recalculated and the workflow is started. This requires a batch job.
    – Dominic
    Jun 26, 2014 at 6:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.