Explains how to write a macro with code that uses a different search criteria for different days of the week. We have a variety of informational email macros to notify if customers have a credit hold, whether POs for Orders have been received, that a Salesperson has an installation planned, deposits for orders near completion have not been received, etc. For these to be effective, we want to have them triggered x “working” days before the Order is due to be completed. We can not use a single formula - E.g. DUEDATE = CurrentDate + 3 because the weekend interferes. This requires that the Macro know the day it is running the code and adjusts the formula accordingly. For example, if run on Monday and Tuesday, looking for Orders due in 3 days, CurrentDate + 3 will not work. When we reach Wednesday, we need to change the formula to CurrentDate + 5 for Orders due the following Monday. Once we have trapped Orders Due on the day desired, we can then solve for a credit hold UDF, POs received, etc.

Sequel has a formula for determining the day of the week from the Current Date, named DATEPART(dw,date). To get the current day of the week we would use the formula DATEPART(dw,GETDATE()). However, when used in a Macro, Cyrious will not compile the DATEPART command in a WHERE statement. The workaround for this is to create a variable named “@Matchdate” which we then set to the day of the week with the DATEPART command. We then use a sequel CASE statement to select the correct code for the day the macro is run.

There are no known risks with this code since the database is unaffected other than hanging the system which can be remedied with a Force Quit.

1. The following code will return Orders to which a PO is attached which has not been received. This is set to run as an Order Macro to enable the use of Cyrious' email addressing capabilities in the Email Macro;so the Primary table is the Purchase Order table and it is inner joined to the Order table, named here OrderTransHeader, to test that the PO is attached to an Order The CASE statement is based on Sunday's being Day 1.

DECLARE @Matchdate INT;
SET @Matchdate = DATEPART(dw,GETDATE());
SELECT
TransHeader.ID,
TransHeader.ClassTypeID
FROM   TransHeader TransHeader
-- Use INNER JOIN to validate that PO is attached to an order -- does not return a record if no PO is attached
 INNER JOIN TransHeader OrderTransHeader ON TransHeader.DefaultOrderID=OrderTransHeader.ID
WHERE
TransHeader.DueDate =
CASE @Matchdate
       WHEN 2 THEN CAST(GETDATE()+3 AS DATE)
       WHEN 3 THEN CAST(GETDATE()+5 AS DATE)
       WHEN 4 THEN CAST(GETDATE()+5 AS DATE)
       WHEN 5 THEN CAST(GETDATE()+5 AS DATE)
       WHEN 6 THEN CAST(GETDATE()+3 AS DATE)
END
AND CAST(TransHeader.ReceivedDate AS DATE) IS NULL

Contributor: Steve Gillispie, Acorn Sign Graphics

Date: 09/09/2012

Version: Control 4.6