Creates an email to our Routing Department (Tyler Proffitt) that material he has ordered has been received when the Purchase Order is marked “received”.

We are finding increasingly that the data in Cyrious can be used when combined with macros to reduce time, facilitate production through-put, and improve internal communications between departments. In this case we have a broad range of materials arriving every day from different sources, Personnel doing the receiving are charged with checking packing slips and material for damage and changing the PO status to Received which works well. They are also supposed to notify those who ordered that their shipment is in; but this step can go awry. To assure that the Routing Dept is notified when its materials have arrived, we wanted to supplement our manual procedures with an email generated from the Received Status in Cyrious. Scott St.Cyr created a macro which does just that. We added this macro to the one we use to report UPS shipments which is run at the end of the day. The code is shown below for those enterprising souls who might want to modify it for similar but different criteria.

Bad macros can affect system performance quite substantially; so if you are uncertain about how to create them or whether you have one which will not generate an error, we advise confirming your code with Cyrious Tech Support. This particular macro is “manually” generated – in this case by another macro. If that one is not run, it will not execute. Additionally, it pulls only those POs which have been received on the day it is run. We assume you can widen this date range with math on the startdate and enddate but we have not tested that.

1. Create a new macro and for the trigger select “SQL Query”. Paste the code below into the query and modify the parts which are clearly company specific – i.e. Proffitt is the name of the originator of the PO and “Piedmont Plastics” is the specific company to whom the PO is issued. (This query will return all POs issued by Proffitt or directed to Piedmont Plastics.

Declare @StartDate datetime;
Declare @EndDate datetime;
Set @StartDate = GETDATE();
Set @EndDate = GETDATE();
-- Convert @startdate and @enddate to a date with no time
Set @StartDate = Cast(@StartDate as Int);
Set @EndDate = Cast(@EndDate as Int);
 SELECT TransHeader.ID, 
        TransHeader.ClassTypeID,  
        ReceivedDate, 
        @StartDate,
        PurchaseOrderNumber, 
        Account.CompanyName, 
        OrderedBy.LastName
 FROM   TransHeader 
        LEFT OUTER JOIN Account ON TransHeader.AccountID = Account.ID
        LEFT OUTER JOIN Employee OrderedBy ON TransHeader.OrderedByID=OrderedBy.ID
 WHERE  Cast(ReceivedDate as int) between @StartDate and @EndDate
        AND TransactionType = 7
        AND (Account.CompanyName LIKE '%Piedmont Plastics%' OR OrderedBy.LastName = 'Proffitt')
 ORDER BY ReceivedDate

1. For the Action select Email Macro and enter the fields as appropriate.
2. Create a PO which would meet the criteria and test the code. If you are not using a program like Click Yes Pro by ContextMagic which bypasses Outlook's security intercept, you will be asked to authorize an email being generated by another program.

Contributor: Steve Gillispie based on code from Scott St.Cyr, Acorn Sign Graphics

Date: 05/23/2010

Version: Control 04.40.1003.3102