Table of Contents

Most of us average users are rendered glassy-eyed by the idiosyncrasies of Sequel code where a misplaced comma or errant apostrophe can render the code useless or the sequence of joins and their “equal to” requirements overwhelm us. This How To shows a method where anyone with moderate capability with Crystal reports can use Crystal to create the code necessary for a simple macro or sequel report.

Concept

This method assumes the user has Crystal Reports and can construct a report which requires the joining of several tables combined with a simple select formula. The approach is to create a Crystal report which returns the information you are seeking. You then use the Crystal “Show SQL Query” feature to view and copy the Query Crystal creates. You then use Word or similar text editor with Find/Replace to copy the Crystal query and correct it for compatibility with Cyrious' Sequel report features.

Cautions

This method works to construct the base for almost all Sequel reports or macros one would need. However, date algorithms must be manually added with the Cast(“Date” as DATE) function of Sequel. There are occasionally other fields which the user will have to add manually to the Where statement. However, we have found that even in these cases this method provides a quick bases and good head start for even a complex query,

Test all Sequel code carefully as bad code can hang or degrade your system performance. Sequel reports usually complete in blink time; so if you are not getting the result you expect very quickly, don't hesitate to Force Quit and simplify your code to where you are sure it is error free. Then add Select statements, Joins, or Where conditions until you have identified the errant code.

Note: If you are using Cyrious Sequel reports capability for reports or report testing, you will often find that sequel reports with errors do not respond to your corrections. Cyrious appears to store these reports in such a way that changes in the code are not updated in the stored code. If you have this problem, deleting the report entirely and reentering your code with a different report name will usually fix the issue.

Steps

  1. Open Crystal Reports and construct a report with the information you are seeking using the Crystal GUI table linking capabilities – drag the cursor from the field in one table to the desired field in the joined table and then set the join as Left Outer Join or more rarely an Inner Join. For this How To example we are creating a macro which will be triggered (return a TRUE) if the user has not selected one of four Primary Stations – “Engraving”, “Printing”, “Assembly”, or “Vinyl”. That will then be used to create a Message Macro stating “You Have Not Selected a Valid Primary Station.”
  2. In the Crystal Reports Data Page select from Storedata, Tables the TransHeader table and the Station table. Click Next to go to the Links page.
  3. In the TransHeader table find the StationID field and drag your cursor from that to the Station.ID field of the Station table. With the link between the tables still selected, click the button “Link Options” and check the radio button “Left Outer Join”. Click Finish
  4. Drag the fields you will be using in this query to the data line in the Cyrious report: TransHeader.ID, TransHeader.ClassTypeID, TransHeader,TransactionType,TransHeader.SatusText, Station,StationName. The order of the first two fields is important since this is how they will be listed in the Select statement and Cyrious wants the TransHeader.ID and TransHeader.ClassTypeID fields as the first two in your query.
  5. Open the Select Expert and Select TransHeader,TransactionType=1, TransHeader,StatusText = “WIP”, and Station.StationName is not one of “Engraving”, “Printing”, “Assembly”, or “Vinyl”. [Substitute the Station Names your Store is using for Primary Stations]
  6. Preview the report to verify that you are getting the result you are seeking and modify the Select Expert formula to get the correct result.
  7. Click on the Database dropdown and select “Show SQL Query”. Select and copy the query you find there. The code should look like the code below
 SELECT "TransHeader"."ID", "TransHeader"."ClassTypeID", "Station"."StationName", "TransHeader"."TransactionType", "TransHeader"."StatusText"
 FROM   "StoreData"."dbo"."TransHeader" "TransHeader" LEFT OUTER JOIN "StoreData"."dbo"."Station" "Station" ON "TransHeader"."StationID""Station"."ID"
 WHERE  "TransHeader"."TransactionType"=1 AND "TransHeader"."StatusText"=N'WIP' AND ("Station"."StationName"N'Engraving' OR "Station"."StationName"N'Printing' OR "Station"."StationName"N'Vinyl' OR "Station"."StationName"N'Assembly')

8. Open a Text Editor like Word and paste this code into it. You must now convert it to code compatible with Cyrious' requirements for Sequel code. This means eliminating the quotation marks, the StoreData.dbo. and the N designation before the Stations.

For the first Find/Replace you will enter the quotation marks and for the replace you enter nothing. Click Replace All and the quotation marks will be removed with a message as to how many were found. Next enter "StoreData.dbo.", replace with nothing and click replace all. Finally enter "=N" and replace with"=" and click Replace All. .The final result will look like the following: 

SELECT TransHeader.ID, TransHeader.ClassTypeID, Station.StationName, TransHeader.TransactionType, TransHeader.StatusText
 FROM   TransHeader TransHeader LEFT OUTER JOIN Station Station ON TransHeader.StationID=Station.ID
 WHERE  TransHeader.TransactionType=1 AND TransHeader.StatusText'WIP' AND (Station.StationName'Engraving' OR Station.StationName'Printing' OR Station.StationName'Assembly' OR Station.StationName'Vinyl)

9. You could run this code as is in a sequel report but for a macro which would be triggered on the creation of a new Order or the Edit of an Order you must add the =TransHeader.ID code to your Where statement. This is easily done by typing this code in as the first Where condition followed by AND. See Below. 

SELECT TransHeader.ID, TransHeader.ClassTypeID, Station.StationName, TransHeader.TransactionType, TransHeader.StatusText
 FROM   TransHeader TransHeader LEFT OUTER JOIN Station Station ON TransHeader.StationID=Station.ID
 WHERE   = TransHeader.ID and TransHeader.TransactionType=1 AND TransHeader.StatusText='WIP' AND (Station.StationName='Engraving' OR Station.StationName='Printing' OR Station.StationName='Assembly' OR Station.StationName='Vinyl)

10. To make this a macro you would go to Macro Setup, select Order Macros, Select the Trigger tab and uncheck “Run on the server in the background”, select New Order, and click the radio button for “An Indirect Change Event”. Check the box at the bottom “Allow this macro to be manually run…” and then paste your code in the indirect change event sequel code box.

11. Click on the Actions tab and select Message Action. Enter the message you want presented. In most cases you will probably want to delete the Prompt to Begin and Notify of Completion messages.

12. Go to an Order Explorer list and find an Oder you know has an “invalid” station. Click on the Run Macros drop down button (usually on the right hand side) and click on the macro you have just created. Your message will appear if you have done everything correctly.

Source

Contributor: Steve Gillispie, Acorn Sign Graphics

Date: 10/19/2012

Version: Control 4.6