Vista Customization-Equipment Maintenance Due Notifier

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 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] in the Select field
  6. Enter 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 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.

Leave a Reply

Your email address will not be published. Required fields are marked *