I hope that, after having read, and tried, the approaches to developing ACL scripts in the Part #1 post, you are anxious to learn more. Certainly, the Part #1 suggestions will allow you to build simple scripts to re-run analysis, but there is more to scripting: more power, flexibility and control. In particular, you may want to control the ACL environment to allow the scripts to run without, for example, prompting the user to ask if they want to overwrite an existing table and data file. Note: while the concepts and commands can be applied to AX version of ACL, the discussion below is based on the AN version of ACL.
Controlling the script environment can be accomplished through the use of SET commands, some of which are described below.
SET Command:
The SET commands are usually executed at the beginning of a script to set certain operating conditions and at the end of the script to return the setting to original state.
- SET SAFETY OFF – turns Safety Off so user is not prompted if file is being over written
- SET SAFETY ON – turns Safety On
- SET ECHO NONE – processing (commands and results) are NOT written to LOG file.
- SET DATE – sets the display date format
- SET FILTER filter_name – set a global filter
- SET FILTER – turns off current global filter
- SET FOLDER /folder_name – creates a folder in the ACL project or sets folder to existing folder
- SET LOG Log_name – creates a new log
- SET LOG – returns to default log
- SET INDEX Index_Name – applies an existing Index (SET INDEX – turns off index)
- SET INDEX – turns off index
- SET DELETE_FILE – Specify ON to automatically delete the associated data file when you delete a table layout
DELETE Command:
You should also strive to maintain a ‘clean’ environment. This usually includes deleting temporary files at the completion of the script. The DELETE command can be used to delete files, tables, fields, variables, indexes, external files, and more. Below are some of the more common DELETE commands that will improve your scripts:
- DELETE File_Name.FIL OK – deletes the ACL data file
- DELETE file_Name.XLSX OK – deletes EXCEL file
- DELETE FORMAT Table_Name OK – deletes the ACL table layout
- DELETE FIELD Field_Name OK – deletes field in Open file from Table layout
- DELETE Variable_Name OK – deletes variable
- DELETE ALL OK – deletes all variables currently active.
Creating Expressions within a Script
The ability to use functions to create computed fields (expressions) is one of ACL’s strengths. ACL allows the user to create expressions – conditional and unconditional – by manually editing the table layout and “Add a New Expression”. This same functionality exists in ACL scripts. Note: If you have already created the expression, you can simply cut-and-paste from the log file.
While expressions can have many parameters, these are examples of basic DEFINE FIELD commands that can be used in a script to create an expression.
Unconditional Expression
DEFINE FIELD Field_Name COMPUTED
Blank line; Description; OR <IF test> <STATIC> <PIC format> <AS display_name> <WIDTH characters> <SUPPRESS>
Value
Or on a single line
DEFINE FIELD Field_Name COMPUTED Value
Example:
DEFINE FIELD Amount_with_Tax COMPUTED Amount + (Amount * Tax_Rate)
I recommend that you delete the expression before creating it, in case it already exists; but this is not necessary in AN if you have SAFETY OFF.
DELETE FIELD Amount_with_TAX OK
DEFINE FIELD Amount_with_Tax COMPUTED Amount + (Amount * Tax_Rate)
Conditional Expression:
DEFINE FIELD Field_Name COMPUTED
Blank line; Description; OR <IF test> <STATIC> <PIC format> <AS display_name> <WIDTH characters> <SUPPRESS>
Value 1 if condition 1
Value 2 if condition 2
…
Value N – default value if no conditions apply
Example:
DELETE FIELD New_Amount OK
DEFINE FIELD New_Amount COMPUTED
AS “New; Amount” PIC “9,999.99” Calculates new Amount
Amount * 1.05 if ProdClass = ’01’
Amount * 1.07 if ProdClass = ‘02’
Amount * 1.08
ASSIGN Command
You can also create and initial variable with the ASSIGN Command.
- {ASSIGN} variable_name = expression <IF>
- ASSIGN v_ctr = 1 – create a Numeric variable called v_ctr and sets it to a value of 1
- ASSIGN v_date = `20180629` – creates a DateTime variable v_date and set it to value the date 2018/06/29
The variables can be used to control the script, for example to create a filter:
- ASSIGN v_date = `20180607` – note: variable is entered as a DATETIME using backwards quotes
- SET FILTER Cost_Date > v_date
- ASSIGN v_date = CTOD(“2018/06/07”, “YYYY/MM/DD”) – variable entered as CHARACTER and converted to a DATETIME
- SET FILTER Cost_Date > v_date
In the next post (Script Post #3) I will discuss methods for prompting users for input; validating the input; and executing commands based on user input.