Description

We want to create a popup which informs the users about information in an order when created so that any errors can be corrected before the order goes into production. In this case we wanted a popup which would trigger on the creation of a new Order or an Order's being marked Sale. The variable popup would contain a summary of the instructions and any special warnings or requests associated with sending an invoice. We wanted computer code to sort the Cyrious selections, overrides if any, and associated UDF instructions in a simple statement saying who the invoice should go to, if copies were required, who should get them and what their email address is as well as some other billing related information depending on the company, the location of the sign project, and the Contact ordering.

Since we were replacing a practice of loading all this information in a company flag, we created a system of Company and Contact UDFs to enhance the rich address permutations Cyrious supports so that SQL code could divine the correct instructions. But how to present that to the user when working with an Order for verification? The solution has been the Cyrious Macro using the SQL report action. This has had some unanticipated benefits in that the “popup” remains while the order is being processed and invoices printed and emailed (unlike the fixed message popups which disappear as soon as OK'd) ; and the email addresses in the html are hot links.

The result is the screen shot below

Basic Discussion of Approach

For this popup we used the Control Macro feature to create 2 macros - one to trigger on a New Order and One to trigger when Order Status is converted to Sale. For the Action we selected the SQL Report. In the SQL query for the report, we included the HTML codes necessary to concatenate the records for the Invoice Goes to Column and we added the Flag record as it comes out of the Account table as a second column for a quick reference.

This macro and query does not alter any data; so the only downsides might be system degradation from faulty SQL. Cyrious has advised that all From and Join statements include the with(nolock) function.

1. Create a new Macro selecting the Trigger event with any additional filtering required in the “An Indirect Event” field. For this type of query the change event will probably be all that is needed since the only thing being passed to the Report Query is the EstimateID or the OrderID or whatever ID is required to link the report query with the transaction.
2. Select The SQL Report as the action. On the General Setup page

   Select **Quick Execute** as the Print Option.

On the Options page

1. Set the report type to HTML
2. Check “Open File after saving”;
3. Uncheck “Prompt before overwriting”;
4. Select the Parameter being passed – E.g. Order will pass Order_OrderID, Estimate will passs Estimate_EstimateID, etc.
5. Select the Parameter to be passed by the Macro, For an Order, select Order.
6. Save your macro and test.

Stucturing the SQL

If you paste in a normal SQL query, Control will pass the variable names returned as Column Names. So, a query to return OrderNumber, DueDate, Customer, Description, will be returned in HTML format with that information in columns each named by the variable Name in the order in which they appear in the query. See Code below and result in the scrrenshot.

Select
OrderNumber,
CompanyName,
Format(DueDate,'MM/dd/yy') DueDate,
Description
From Transheader TH with(nolock)
LEFT Outer Join Account Cust with(nolock) on TH.AccountID= Cust.ID
Where
TH.StatusText = 'WIP'

To concatenate data in an column, simply use the + operator and create the string for each column name. Insert any HTML code such as for line breaks into the string. Note that you must cast the string with the approximate number of characters to avoid the HTML “WIDE FORMAT” suppression of long strings. Also, since a string is being constructed in SQL, the code must convert any numbers or dates to text.

The example below illustrates the same query and its result shown above with each Order's information in one column named OrderInformation.

Select
OrderInformation =
                  CAST
                   (
                   'Order Number:'+ Convert(nvarchar(10),OrderNumber) + ''+
                   'Company' + Cust.CompanyName + '' +
                   'Due Date' + Format(DueDate,'MM/dd/yy')+ ''+
                   Description AS Varchar(2000)
                   )
From Transheader TH with(nolock)
LEFT Outer Join Account Cust with(nolock) on TH.AccountID= Cust.ID
Where
TH.StatusText = 'WIP'

Contributor: Steve Gillispie. Acorn Sign Graphics

Date: 2/22/2017

Version: 06.00.1702.1401