Scenario: Looking to receive an email notice when equipment is due for maintenance.

Requirements: These steps assume that you own the WF Module.

Example Solution:

  1. Open the WF Notifier Queries form
  2. Create a new Record by entering CTMEMMaintDue in the Query Name Field
  3. Enter EM Maintenance Due in the Title Field
  4. Enter Returns one row for each piece of equipment with Maintenance Due Alert in the Description Field
  5. Enter SQL Query #1 (found at the bottom) in the Select field.
  6. Enter SQL Query #2 (found at the bottom) in the From where clause field
  7. Press the Test Query button to test the query.
  8. If the query tests ok, save and close the form.
  9. Open WF Notifier Job Manager
  10. Enter EM Maintenance Due in the Job Name & Description Fields.
  11. Set Query type to 0-WF Notifier Query
  12. Select CTMEMMaintDue as the query.
  13. Set the desired schedule
  14. On the email tab, enter the desired recipient, subject, and header.
  15. 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]
  16. Check the Consolidate Notifications field at the bottom of the form.
  17. On the Consolidations Group tab, add [EMCo] to the Grouping Criteria.
  18. On the Email tab, press send email 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