We needed to create a flag which told us whether an email had been sent to a particular customer. So when the macro was triggered it would look to the flag and, if not sent, send the email and set the flag to true. If already sent once, exit the macro.

Concept

The concept was to create a UDF – SentFlag – and include SQL code with the email macro to set the UDF to true when the first email was sent and otherwise have thee macro return false and do nothing if the flag was “true” and an email already sent.

This concept requires us to use the SQL Update TransHeaderUserField table. We consider the UPDATE /Set command one of the most dangerous in SQL. If the conditions are wrong, it is possible to overwrite all the variables in your table with your SET command. NOTE THAT there is no recovery if you overwrite records by mistake and have not made a backup. It is very important to have a condition (where) statement for every Update command set and to test it carefully before using the SET command which can be done with sql_reports_referenceor better MySequel.

Steps

1. The first step is how_to_set_up_and_use_udfs field to serve as the “flag”. In this case we created a field called “SentFlag”
2. macro_trigger_changes was our next step. For this we created a new Order Macro called “Proof Approval”
3. We unchecked “Run on the server in the background” as we want this to be activated on the machine of the user entering the Proof Date (we use this field for when the Proof is approved)
4. In the box below labelled Indirect “An Indirect Change Event we paste the sql_reports_reference tested in an SQL Editor like MySequel or with Cyrious' sql_reports_reference The actual code is shown below. If you study this code, we have created this macro to trap actions for a single customer whom we identify with the Customer.AccountNumber table value. Because we occasionally get burnt with Null fields and overridden fields which appear null but are not perceived that way by Sequel, we include specific conditions for null and non-null fields. (There may be better code for this but as we said, we are novices.)

You will also note that code following the UPDATE command is identical to the Select statement's code.

1. For those who datamine primarily with Crystal, how_to_create_sequel_code_for_reports_macros which is what we do to save time and reduce mistakes. Paste the SQL query from Crystal into a text or SQL editor, Find/Replace the double quotes with nothing. You can do the same for the StoreData.dbo. text which Crystal creates but is not required in a Cyrious macro. There is usually some additional small cleanup work to be done such as replacing double quotes around text elements with single quotes (sometimes this has to be done in Cyrious as it does not like some Word characters, etc., adding the % character for wildcards, etc. A little trial and error and lookups on Microsoft's Sequel site which is quite good for looking up syntax issues and you will have good working code.

-- Run the ID and ClassTypeID to conform to Cyrious macro standards
select
TransHeader.ID,
TRansHeader.ClassTypeID
FROM TransHeader TransHeader
LEFT Outer Join TransHeaderUserField TUF On TransHeader.ID = TUF.ID
LEFT Outer Join Account Customer On TransHeader.AccountID = Customer.ID
WHERE
TransHeader.ID =
AND Customer.AccountNumber=7904
AND (TUF.SentFlag IS NULL OR TUF.SentFlag = 0)
AND TransHeader.ProofDate IS NOT NULL
--Run the update to set the flag
UPDATE TransheaderUserField
SET
--Set the Sequence ID so Control knows the table has been updated
TransHeaderUserField.SeqID = TransHeaderUserField.SeqID+1,
TransHeaderUserField.SentFlag = 1
FROM TransHeaderUserField
LEFT Outer Join TRansHeader TransHeader On TransHeaderUserField.ID = TransHeader.ID
LEFT Outer Join Account Customer On TransHeader.AccountID = Customer.ID
WHERE TransHeaderUserField.ID =
AND Customer.AccountNumber=7904
AND (SentFlag IS  NULL  OR TransHeaderUserField.SentFlag = 0)
AND TransHeader.ProofDate IS NOT NULL
--Call the Sequel Bridge Chapi Refresh to update the records of loaded transactions in the system for UDFs (ClassTYpe=10002) and Orders (ClassType=10000)
select dbo.csf_chapi_refresh((Select ID From TransHeaderUserField with(nolock) Where ID = ),10002,-1) --ID=
select dbo.csf_chapi_refresh((Select ID From TransHeader with(nolock) Where ID = ),10000,-1)  --ID =

Contributor: Steve Gillispie, Acorn Sign Graphics

Date: 05/01/2013 updated May 13, 2016 by SG to incorporate Sequel Bridge addition and other edits.

Version: Control 4.6/5.7