ACL Scripts Part #4

In my previous discussion about variables, I neglected to mention ACL system-generated variables.  These are more evident now, and can easily be seen by using the Variables tab in the Overview/Navigation window.  The most common use of ACL system-generated variables are those created by the STATISTICS commands: MAX1, MIN1, HIGH1, LOW1, AVERAGE1, RANGE1, TOTAL1, COUNT1 and ABS1.

These, and other variables, are created by the execution of ACL commands and the results can be used to set filters, extract records, etc.  For example, the command EXTRACT RECORD if AMOUNT > 10000 to Over_10K creates the new file Over_10K and also creates a variable (WRITE1) which has the number of records that were extracted.  If you want the script to EXPORT the results to an Excel spreadsheet, but only if there were records with Amount > 10K, then you could use the variable WRITE1.  IF WRITE1 > 0 then records were extracted to Over_10K; but if WRITE1 = 0, then no records met the test (Amount > 10000) and the resulting file is empty (0 records).  The IF Command can determine whether you should execute the EXPORT command or not.

IF Command

The IF Command differs from the IF Condition.

• The IF Condition (e.g. EXTRACT RECORD IF Amount > 100) is tested for each record and the action taken if the test returns a True. So each record is tested to see if Amount is greater than 100, and the record is extracted if the condition is true.
• The IF Command is tested before the Command is executed and the Command is executed if the value is True at that time; it is not executed if the current value is False. It does not test condition for every record.
  • IF WRITE1 > 0 EXPORT FIELDS ALL XLSX TO My_Results – would only execute the EXPORT command if WRITE1 was greater than 0; but all records would be exported.

The two commands can be used in conjunction:

IF HIGH1 > 1000 EXTRACT RECORD IF Amount >= HIGH1

Run STATISTICS first to get HIGH1.  Then the script would perform the EXTRACT only IF HIGH1 > 10000; and would extract all records where the Amount was greater than HIGH1

 

DIALOG Box

Dialog boxes give you more options to obtain user input and then take desired action.  As far as I know, ACCEPT only allows for a single user entry of input or selection from a dropdown list.  Dialog boxes can not only capture user input with radio buttons and check boxes; but can prompt the user for more than one input in a single Dialog box.

Building a DIALOG Box

Create a new, or edit and existing, script and click on the button “Build Dialog” (second button from top left).  Then use the buttons on the left of the dialog box screen – Text, Edit Box, Check box, Radio Button, Drop-Down list, and Project Item – to build your dialog box.

• TEXT – display text – unusually direction to user (e.g. “Select file to open”)
• EDIT Box – provide an area where user to enter input (e.g. “Enter cut-off date”)
• RADIO Button– create a radio button allowing the user to select one of several options (e.g. Analysis by: “Period”, “Quarter” or “Full Year”)
• CHECK Box – create a series of one of more check boxes from which the user can choose (e.g. Select analysis to be performed: “Duplicate Invoices”, “Invoices GT $50K”; “Invoices – no PO”)
• DROP-DOWN List – a custom built drop-down list of items from which the user can select one item (e.g. Select Division: “East”; “Central”; “West”; “International”)
• PROJECT Item – a drop-down list of Project items including: Tables, Fields, Scripts, etc. (e.g. Select table to open: with a project item list of all tables within current project presented to the user

As with the ACCEPT command, the user supplied response – selection or entry – is captured in variables.  With DIALOG Boxes, however, the variable type depends on the method used to obtain user input:

• Edit Box – variable will contain free-form text entry stored as a Character value.
• RADIO button – user selection will be stored as a Numeric value (e.g. if user selects first radio button, the value of the variable will be 1; 2 if second button selected; 3 if third; etc.)
• CHECK Box – user selection will be stored as a True of False (e.g. if user check box 1, the variable will have a True value; is user does not select check box 2 – it will have a False value.
• DROP-DOWN list – variable will contain the drop-down text selected by the user as a character value (e.g. v_Division “West”)
• PROJECT Item – variable will contain the Project Item selected by the user as a character value (e.g. v_input_file “Inventory”; v_field “Prodcls”)

Knowing the variables that the user is being prompted to provide by entering, selecting or checking allows you to take actions.  Using the different methods of obtaining input – Edit Box, Radio Button, Check Box, Drop-Down List and Project Item – will mean that you know what to expect from the input.

The IF Command, discussed above,can then be used to determine the specific commands (or additional scripts) that will be executed.  For example, ACL could use the results of the radio button selection to determine additional scripts to be executed.  Give a Radio button selection with variable v_test and choices: Radio button 1 – “Financial Tests”; Radio button 2 – “Inventory Tests”; Radio button 3 – “HR Tests”; and Radio button 4 – “Payroll Tests” – the scripts code after the Dialog Box would be:

IF v_test = 1 DO Fin_Tests

IF v_test = 2 DO Inv_Tests

IF v_test = 3 DO HR_Tests

IF v_test = 4 DO Payroll_Tests

 

For Check Boxes, the user can select multiple items and the code, therefore, requires multiple variables – one for each possible choice (e.g. v_Fin for Check Box – “Financial Tests”; v_inv for Check Box – “Inventory Tests”; etc.).  If the above example had been coded as four Check Boxes, the script code after the Dialog box would be:

IF v_Fin = T DO Fin_Tests

IF v_Inv = T DO Inv_Tests

IF v_HR = T DO HR_tests

IF v_Pay = T DO Payroll_tests

 

All ACL scripts execute line by line – there is no GOTO that allows you to jump around within a script.  However, a script can call other scripts.

The IF Commands above allow ACL to determine whether or not to execute the scripts: Fin_Tests; Inv_Tests; HR_Tests; and Payroll_Tests.  These scripts are called sub-scripts.  For example, If v_Fin = T then ACL will execute the script called Fin_tests; and upon its completion will return to the next line in the main script: IF v_Inv = T DO Inv_Tests.

The commands above require the sub-scripts to exist.  For example, given the code

IF v_Fin = T DO Fin_Tests

there must already be a script called Fin_Tests for ACL to execute.

 

Next Post: Validating User Input (Error Checking); and using Sub-Scripts (DO Sub-script WHILE condition is “True”).