Today I implemented some Priority Manager profiles to automate the unsuspending of jobs once a target date is reached. Thought I'd share...
Where to store the date
On the Timeline tab of a Job, there are 3 date fields, one of which is labelled 'Work commencement date'. This seems appropriate and we have no other business automation rules using that field. Alternatively a dedicated date field on the Custom tab of a Job could also work.
Business logic
When a Job has a Job Status of 'Suspended' and the 'Work Commencement Date' has passed we want to:
- Change the job status to 'In Progress'
- Write a short description to the Action Details, eg. This job was unsuspended because the Work Commencement Date date has lapsed.
- Email the current assignee notifying them that the job has become unsuspended
Templates
- Create an email template for assignee notification
- Create an action template for the priority manager to use. This is what changes the job status, adds the note and uses the above email template.
Setting up a new Priority Manager Profile
- Profile Definition: Start by pressing the Launch Job finder button from within the Profile Definition step of a new Priority manager Profile.
- In the Job finder, select a Job Status of 'Suspended' then press 'Return search criteria to Priority Manager'.
- Click on the 'Use custom SQL search criteria' radio button. This shows the SQL generated by the Job Finder and makes it easy to build on the WHERE clause as required.
- The Work Commencement Date is stored in tblHelpdeskJobs, so all you need to do is add "AND
WorkcommencementDate < GetUTCDate()" to the end of the query. (You might like to test this query out with SQL Server Management Studio on a dev system with some test jobs.)
- Actions To Perform: Check 'Complete this action template' and select your new action template.
- Frequency: I chose to run this profile every 5 minutes during business hours. Perform all actions EVERY TIME...
- Save and Enable the rule.
I took this a step further and created two other Profiles:
- Immediately unsuspend jobs that have a null value for the Work Commencement Date.
- Immediately unsuspend jobs that have a Work Completion Date far off in the distant future. We agreed an upper limit of 2 months is a reasonable interval to revisit jobs. For the handful of jobs that are legitimately suspended longer that that, this was, for us, an acceptable trade-off.
Edited: Changed to GetUTCDate()