A great power of Cyrious with its database structure is that macros can be programmed to alert, warn, inform, etc users of situations which might cause errors, missed dates or other problems. Code which keys off of due date intervals must be adjusted for the weekend. This How To shows one way to create a macro alert based on interval days which change based on the day of the week on which the macro is run.

Specifically, this code is designed to notify users if purchase orders they have made which are tied to Work Orders have arrived and notifies the creator if the purchase order has not been marked received and the Order is due within 3 days. This interval is arbitrary (can be easily changed to any desired number) and is intended to provide the PO creator sufficient time to get the material in time to complete the order in time.

Macros which are based on future dates need a logic to handle the weekend interval; so a macro based on just a date interval like DueDate + 3 will not work without an algorithm to increase or decrease this date based on the day of week the macro is running. This code determines the day of the week on which the macro is running and then runs code based on that information. The code can be modified easily to change the interval dates or to test dates from Estimates, Work Orders, or other applications.

Sequel's Case Statement in lieu of more complex if..then..else code is what makes this macro possible with relatively simple code. The Select (“Field Name”) returns the value in that field. In this example, Datepar(dw,Date) returns the day of the week. The When function combined with a number or string sets up the trigger. For example, 3 would be Tuesday and in the example below we add 3 days to get jobs with a due date on Friday. 4 equates to Thursday so in this equation we want Orders with a Due Date of the following Tuesday and the following Then statement looks for Orders with Due Dates 5 days out. The remaining code determines that the PO has not been received or cancelled and finally that the Purchase Order has not arrived on its assigned Due Date.

This does not modify the database so there is no risk; however,macros should be tested carefully as they can hang the system or burden the CPU and degrade performance. We have been running the code below for a month It has been performing flawlessly and has helped keep a number of jobs on time which otherwise would have missed their Due Dates..

  1. This is a new macro; Go to the Macro Setup page and click on New Macro. Enter the title of the Macro and an explanation of what the macro is supposed to do in the Description field.
  2. On the Triggers page uncheck “Run on the server in the background”.
  3. Select the lower radio button, “Use a Sequel Query”.
  4. Enter the following code in the Sequel Field:

<code> SELECT TransHeader.ID, TransHeader.ClassTypeID, DATEPART(dw,GETDATE()) AS DAYNO, TransHeader.PurchaseOrderNumber, TransHeader.ReceivedDate, TransHeader.TransactionType, TransHeader.OrderedDate, TransHeader.DueDate, OrderTransHeader.DefaultOrderID, OrderTransHeader.DueDate OrderDue, OrderTransHeader.OrderNumber, TransHeader.DefaultOrderID

FROM TransHeader TransHeader INNER JOIN TransHeader OrderTransHeader ON TransHeader.DefaultOrderID=OrderTransHeader.ID LEFT OUTER JOIN EMPLOYEE SalesPerson1 ON OrderTransHeader.SalesPerson1ID = SalesPerson1.ID WHERE ( Cast(OrderTransHeader.DueDate AS DATE) =

  CASE  DATEPART(dw,GETDATE())  
       WHEN 6 THEN CAST(GETDATE() + 3 AS DATE ) 
       WHEN 7 THEN CAST(GETDATE() + 1 AS DATE ) 
       WHEN 2 THEN CAST(GETDATE() + 3  AS DATE)
       WHEN 3 THEN CAST(GETDATE() + 3  AS DATE)
       WHEN 4 THEN CAST(GETDATE() + 5  AS DATE)
       WHEN 5 THEN CAST(GETDATE() + 5  AS DATE)
  END

AND TransHeader.StatusText 'Cancelled' AND CAST(TransHeader.ReceivedDate AS DATE) IS NULL AND CAST(TransHeader.DueDate AS DATE) 5. Schedule the macro to run on a daily basis. We have grouped about 12 macros into a single macro call which is run manually each day after the close of UPS Worldship.

Contributor: Steve Gillispie,Acorn Sign Graphics Date: 10/18/2012 Version: Control 4.6_

You could leave a comment if you were logged in.