Scenario: Looking to receive an email notice when equipment is due for maintenance.
Requirements: These steps assume that you own the WF Module.
- Open the WF Notifier Queries form
- Create a new Record by entering CTMEMMaintDue in the Query Name Field
- Enter EM Maintenance Due in the Title Field
- Enter Returns one row for each piece of equipment with Maintenance Due Alert in the Description Field
- Enter SQL Query #1 (found at the bottom) in the Select field.
- Enter SQL Query #2 (found at the bottom) in the From where clause field
- Press the Test Query button to test the query.
- If the query tests ok, save and close the form.
- Open WF Notifier Job Manager
- Enter EM Maintenance Due in the Job Name & Description Fields.
- Set Query type to 0-WF Notifier Query
- Select CTMEMMaintDue as the query.
- Set the desired schedule
- On the email tab, enter the desired recipient, subject, and header.
- In the Line field, you can insert Email Fields by pressing the email fields button at the bottom of the form. Example: [Equipment]: [EquipDescription] [ModelYr] [Manufacturer] [Model] [StdMaintGroup] [ItemDesc]
- Check the Consolidate Notifications field at the bottom of the form.
- On the Consolidations Group tab, add [EMCo] to the Grouping Criteria.
- On the Email tab, press
sendemail button to test the notifier to make sure it functions as expected.
SQL Query #1
select e.EMCo as [EMCo], e.Equipment as [Equipment], e.Description as [EquipDescription], e.Manufacturer as [Manufacturer], e.Model as [Model], e.ModelYr as [ModelYr], e.VINNumber as [VINNumber], s.StdMaintGroup as [StdMaintGroup],s.Description as [Description],i.Description as [ItemDesc]
SQL Query #2
FROM bEMSH s (Nolock) LEFT OUTER JOIN bEMEM e (Nolock) on s.Equipment = e.Equipment and s.EMCo = e.EMCo LEFT OUTER JOIN bEMSI i (NoLock) on s.Equipment = i.Equipment and s.EMCo = i.EMCo and s.StdMaintGroup = i.StdMaintGroup where e.Status <> 'I' and 1 = (case when s.Basis = 'H' and e.HourReading > (i.LastHourMeter + s.Interval - s.Variance) then 1 when s.Basis = 'H' and getdate()> (isnull(i.LastDoneDate, e.PurchDate) + s.IntervalDays) then 1 when s.Basis = 'M' and e.OdoReading > (i.LastOdometer + s.Interval - s.Variance) then 1 when s.Basis = 'M' and getdate()> (isnull(i.LastDoneDate, e.PurchDate) + s.IntervalDays) then 1 END)D