Your first Analytics script

This short, simple tutorial shows the basics of ACLScript. The tutorial only deals with opening a table and extracting a subset of records, but ACLScript is capable of much more.

What do you need?

In this tutorial we assume that you have installed and activated Analytics and that you have access to the sample data that ships with the application.

Note

By default, the sample data projects are installed at C:\Users\username\Documents\ACL Data\Sample Data Files on your local file system.

Setting up

Open the sample Analytics project

  1. Open ACL for Windows.
  2. Click Open Analytic Project and from the ACL Data\Sample Data Files folder, select Sample Project.ACL.

Create your first script

  1. In the Navigator, from the Overview tab, right-click the Scripts folder and select New > Script.

    New_Script is added to the Navigator and opens in the script editor.

  2. Right-click New_Script, select Rename, and enter extract_invoices.

The script logic

In this script, we are going to use ACLScript to:

  1. Open the Ap_Trans table.
  2. Copy all records from the table with an invoice amount greater than 1000.00 and store them in a new table called Ap_Trans_High.
  3. Open the new table to inspect the results.

Open the Ap_Trans table

Data is stored in tables, so to work with data we need to first open a table. The OPEN command signals that you are working with the specified table and makes the table's data available to your script commands: 

OPEN Ap_Trans

Copy this line, paste it into the script editor, and then click Run on the editor toolbar.

If the Ap_Trans table opens, your script is working. Close the table and continue.

Extract all records to Ap_Trans_High and close Ap_Trans

Now that the script is working with the Ap_Trans table, we can use the EXTRACT command to copy records from Ap_Trans to a new table called Ap_Trans_High:

EXTRACT RECORD TO 'Ap_Trans_High'
CLOSE Ap_Trans

Copy this line, paste it into the script editor on a line after the OPEN command, and then click Run on the editor toolbar.

You should see the Ap_Trans_High table appear in the Navigator under Tables > Accounts_Payable. This new table contains the copied records from Ap_Trans.

Extract the subset of records to Ap_Trans_High and close Ap_Trans

At this point, we can add a conditional IF parameter to the EXTRACT command so that we only copy invoice records with amounts that exceed 1000.00.

Notice how we use the IF parameter to test whether the value of the Invoice_Amount field is greater than 1000.00. If this test does not evaluate to true, the record is not extracted:

EXTRACT RECORD TO 'Ap_Trans_High' IF Invoice_Amount > 1000.00
CLOSE Ap_Trans

Copy this line, replace the existing EXTRACT command in the script editor with it, and then click Run on the editor toolbar.

When prompted, click Yes to overwrite the Ap_Trans_High table. The Ap_Trans_High table now contains the copied records with amounts exceeding 1000.00 from Ap_Trans.

Open the Ap_Trans_High table

We will end the script by opening the new table Ap_Trans_High so that you can inspect the results of the EXTRACT command. As this is the last action in the script, the table opens and you can review the records:

OPEN Ap_Trans_High

Copy this line, paste it into the script editor on a line after the EXTRACT command, and then click Run on the editor toolbar.

The Ap_Trans_High now opens when the script completes and you can review the extracted records from Ap_Trans.

The full script

OPEN Ap_Trans

EXTRACT RECORD TO 'Ap_Trans_High' IF Invoice_Amount > 1000.00
CLOSE Ap_Trans

OPEN Ap_Trans_High

Where to next?