I want to set up some automation for Assets but I need some help. Rather than sending a support email through, I thought I'd share the development process on the forums.
Background
We have an equipment loans process built on asset records.
- The asset status is "On Loan" when checked out and "In stock" when checked in.
- The asset is linked to the client who has made the loan and unlinked when the item is returned.
- The asset also has a custom date field indicating when the equipment is due to be returned.
I built a light-weight webapp that directly updates the database to streamline this for our staff.
Business Logic
When equipment is overdue, we want to start by sending a reminder email to the client. In principle we don't want a job logged yet because there's no work to be done by our staff.
However, if the item is not returned some 3 business days after the due date we are going to lock their AD account. While this could in theory be fully automated, at this stage we just want to log a job for the helpdesk so that they can consider the circumstances of the loan (VIPs, special events, etc.)
So, two actions:
- When the custom date field on the asset is in the past and the asset status is 'On Loan', send an email to the asset's Primary Client.
- When the custom date field on the asset is more than 3 days in the past, create a job & assign to helpdesk.
Triggered Events
Triggered events are all about logging a new job when the conditions are met, but I think we can also use it to send the email with the stored procedure sp_send_dbmail(). The question then is: What do I put in to the SQL field to make this happen? Unfortunately there is no pregenerated SQL to build on.