Here's an interesting scenario that was just received from a HelpMaster user via our tech support.  I'm reposting the solution here as it might be of use to others who have implemented the same or similar workflow.

The business workflow

When jobs are logged that relate to a piece of equipment, (asset), the business practice is to link that asset to the job.   This allows the business to track the service and helpdesk history for that item.

The asset is then checked out for service as part of the job / asset lifecycle.  The asset service history is then recorded.

Before the job is closed, the asset should be checked back in to complete the service request.  

The problem !#?

Sometimes the job is closed before the asset is checked back in from it's service status. The asset is left in service limbo with no resolution / service notes added to it.

The challenge

The challenge here is to notify the helpdesk manager via email whenever a job is closed that has a linked asset that has not been checked back in from a service.

The solution - Priority Manager custom SQL

By utilizing the custom SQL feature of the Priority Manager, this scenario can be monitored, managed and email alerts can be sent to the service manager.  Here's how:

1. Create a new Priority Manager profile
2. Give it a name
3. Click on the "Custom SQL"
4. Paste the following SQL.  Note, the SQL will only "look" for jobs that have been closed within the last month.
   
   SELECT tblHelpdeskJobs.pkID
   FROM qryRelationshipsAssetsLinkedToJobs_All INNER JOIN
   tblServiceDetails ON qryRelationshipsAssetsLinkedToJobs_All.AssetPKID = tblServiceDetails.AssetLink INNER JOIN
   tblHelpdeskJobs ON qryRelationshipsAssetsLinkedToJobs_All.JobPKID = tblHelpdeskJobs.pkID
   WHERE (tblServiceDetails.ActualDateBack IS NULL) AND (tblHelpdeskJobs.DateClosed > DATEADD(month, - 1, GETDATE()))
   
   
   
   
   
    
5. On the Email screen, select the "Other" email option
6. Enter the email address of the service manager, and select an appropriate Email template (you might need to create this beforehand).
   
   
   
    
7. Enable the Priority Manager profile and let it do it's thing.
8. The service manager will now get an email whenever a job is closed that has a linked asset that has not been checked by from service.

Problem solved!