We want to provide automated notifications to various associates when events occur which they need to know to properly administer an account, job or event such as an installation. There are a variety of events which occur during the sale, production, installation, invoicing, and collecting for a sign job which require someone be notified. Examples include the arrival of materials, the imminence of an installation which requires an advance appointment, notifications that materials need to be ordered, etc. Since people are prone to forget, whenever possible we want Cyrious to send emails or to provide message pop-ups of certain events. The Cyrious macros provide an abundance of ways to do this; but inevitably there are situations which have not been anticipated in the Macro triggers. For these Cyrious has provided an enormously powerful tool which allows users to extract virtually any information available in any Cyrious (“storedata”) table to use for such a purpose. These are Sequel queries which can be embedded in macros as Triggers. This page describes how to create such a trigger to send emails to those who created Purchase Orders that their PO has been marked received and their order is in the shop. A custom query is required because Cyrious has not yet implemented triggers for Purchase Order macros. It is hoped that this “model” will help others (1) to know what is possible and (2) to create triggers for similar kinds of situations or needs. Note that for a nominal fee, Cyrious Tech Support will usually write a query for a customer. This is an excellent way to learn to do it yourself as we have used queries developed by Cyrious Support to adapt to additional applications.

Create a Sequel Query which will return a list of “found” Purchase Orders as the basis for emails to the originators.

There is some remote danger theoretically of creating a query which might tie up resources; however, the only real risk here is a loss of a lot of time as you become familiar with Sequel Select statements which are neither intuitive, logical, or forgiving. We use Cyrious Sequel Report feature to test the query before putting it in a macro. For this purpose, go to Reporting Setup, Reports on Main Menu. Select a folder like Test Reports, and Add a new Sequel Report. Check the Radio Button SQL and enter your Sequel Code there. Check the Radio Button, “Create Report and Save. Save and then Print your report. If there are no problems, your default browser will appear with blinding speed with the result of your query. If you have a report writer like Crystal Reports, Tableau or any product used for Sequel data mining, you can get a head start by creating the query in that program. This will help identify the tables and field names you will need and to test your logic. In Crystal, for example, under the Database menus, you can select view sequel query. You can not use Crystal's syntax verbatim, but the tables, field names, and joins will all be the same.

1. Prepare your Select Statement. We use a program like Notepad or Word. Cyrious uses a convention that requires the first two lines to be The ID and ClassTypeID of the main table your are querying. Most of the time this will be the Transheader table.
2. Go To the Macro Setup screen and create a new Macro for the type of activity you want. For this example, we used a Transaction macro to get the Purchase Order received notification. Purchase Orders, Estimates, and Work Orders all use the Transheader table as their “main” table. The difference is the ClassTypeID number. Our assumption which proved correct was that we could use the Transaction macros to do what we needed. This macro queries for Purchase Orders received “today” (the day it is run). It returns a list of all Purchase Orders received. For the Action, we then pass this list to an email macro which generates emails, in this case to the “Entered By” person, as that field is the same for both Work Orders and Purchase Orders.
3. We named this macro “POs Received”. On the Trigger menu we unchecked the field to run on the server in the background. We checked the radio button, “Use a Sequel Query”. We then entered the following code: You can infer that the ClassTypeID code for Purchase Orders is 11000. GetDate() returns the current date. The cast function works like the Declare in CFL or the Local/Global/Shared declaration in Crystal. In English this Select statement says return records where the ClassType ID = 11000 and the Received Date = Today. For a disclaimer, we must acknowledge that we are rank amateurs with Sequel and there may be a more elegant way to write this, but it works.

-- This is an Order Macro but Cyrious can be "fooled" into a PO Macro because
-- POs, Orders and Estimate all use Transheader as their main table; POs have ClassTypeID = 11000
-- POs and Transactions share the EnteredBy field so this can be used for the email address
-- Query returns POs with Received Date of Current Date (Today)
Select Transheader.ID,
 Transheader.ClassTypeID
  
FROM Transheader
WHERE Transheader.ClassTypeID = 11000 AND
CAST(Transheader.ReceivedDate as DATE) = CAST(GETDATE() AS DATE)

Next, as the action we select email macro. Then, it's a matter of completing the email macro setup. We have the To field replaced by “Order/Estimate/Service Ticket Entered By:. In the message field we use the Merge Fields to create the following message:

PO Number: 
Received Today
Your friendly Cyrious Cyber Assistant

Contributor: Steve Gillispie, Acorn Sign Graphics)

Date: 3/10/2011

Version: 04.50.1103.0402