Scripts are a powerful way of improving consistency and speed when processing the same commands over and over again. But they can also be used to provide relatively novice users with the ability to run more complex analysis.
By now you may be ready to develop more interactive scripts that you can give to other users. This usually requires prompting the user for input; validating the input; and acting upon the user input by executing certain scripts and not others. This post discusses some of the ACL script commands required to perform these tasks.
Note: while the concepts describe can be applied to AX version of ACL, the discussion is based on the AN version of ACL. The AX version uses the Analytics Header to obtain user input, not the ACCEPT command or DIALOG Box. Also, this is based on my personal experience (and poor memory) and use of ACL – others may have different – equally valid – opinions/practices.
Prompting for User Input
As discussed in Part #2, you can ASSIGN variables (create and initialize them), but sometimes you want user input to create and initialize the variable. This involves prompting the user for entry. The simplest method of prompting the user for input is the ACCEPT command; but before we get to that, a bit of the logic behind prompting the user for input.
In a script, you can ask user for input – selection of a table to open, entry of a cut-off date, specify the test to run, etc – and store the input in a variable. This requires five basic components or steps:
- Prompt User for entry– give user direction e.g. “Select table to open” or “Enter a cut-off date”
- User entry – the user selects a table; enters a date; etc.
- Store user input – the user input is stored in a variable
- Validate user input – check to see is user input is valid e.g. Date is entered in proper format
- Take action – using the user input to perform a task (e.g. open the file or filter based on cut-off)
The first three steps are performed in a single command. In the AN version of ACL the two most common methods of the prompting for, obtaining, and storing user input are the ACCEPT Command and a DIALOG Box.
ACCEPT Command
ACCEPT “prompt string” TO variable_name e.g. ACCEPT “Enter a cut-off date” TO v_Cutoff
Note: Variables are stored as a character fields; use VALUE() function to convert to numeric value if required (e.g. ASSIGN v_amt = VALUE(v_amt, 0)
You can also use the ACCEPT command to prompt the user to select an item from a list by adding a parameter (FIELDS) to the command. Some of the more common selection items are: “xf”’ for tables; “C” for Character fields; “N” for Numeric fields; and “D” for DateTime fields.
ACCEPT “Select table to Open” FIELDS “xf” TO v_infile – prompts user to select from a list of tables in the current ACL Project. The selected table name is stored in variable v_infile
ACCEPT “Select character field” FIELDS “C” TO v_charfield – prompts user to select from a list of character fields in the currently open table. The selected field name is stored in variable v_charfield
ACCEPT “Select numeric field” FIELDS “N” TO v_numfield – prompts user to select from a list of numeric fields in the currently open table. The selected field name is stored in variable v_numfield
ACCEPT “Select date field” FIELDS “D” TO v_datefield – prompts user to select from a list of date fields in the currently open table. The selected field name is stored in variable v_datefield
Note: you can also use the FIELDS parameter to provide a list of: scripts, index, variables, etc.
When asking the user to enter (type) an amount or date, it is simple to capture and then use the input:
ACCEPT “Enter Cut-off amount” TO v_amt – prompt user for amount
ASSIGN v_amt = VALUE(v_amt, 0) – convert entry to a numeric
SET FILTER Uncst > v_amt – apply filter
However when asking the user to select a table or field, more needs to be done. For example:
ACCEPT “Select table to Open” FIELDS “xf” TO v_infile
OPEN v_infile
If the user had select the table named “Inventory”, the commands above will try to open a table called v_infile, not “Inventory”. You would need to tell ACL that you want to use the contents of v_infile (i.e. “Inventory”) and not a table called “v_infile”. In order to do that you must use Macro Substitution. Macro Substitution will substitute ‘contents’ of the variable (v_infile) into OPEN command. To indicate that you want to use the contents of the variable, you must place percent signs around the variable (e.g. %v_infile%).
ACCEPT “Select table to Open” FIELDS “xf” TO v_infile – prompts user to select a table
OPEN %v_infile% – opens table
The above script commands will cause ACL to OPEN the table name which is stored in v_infile, in the example, it will OPEN Inventory.
Next post – adding more power – DIALOG boxes and the IF Command.