ACL: How to Add Computed Fields via Script

Once you’ve mastered creating computed fields, you’re ready to add computed fields to a table via script. It is easier than it sounds.

If you need some background on computed fields, see my previous posts, What is a Computed Field? and How to Add a Computed Field (manually). Now let’s explore writing a script that adds computed fields to a table.

Below is a script that will add the computed fields c_Region and c_Customer_Type to the customer table (these fields are described in the 2 posts noted above ). See the line by line explanation below the script.KEY:

1-5 COMMENT – I always like to explain the overall purpose of a script. I like to add asterisks at the beginning of a script so that when I’m looking through the log, I can tell where a script starts.

7 SAFETY – See the explanation in ACL tip: Create a File Import Script.

8 CLOSE – If any primary and secondary tables are open, this closes them so that the script doesn’t run against the wrong table. Usually, this won’t happen as you normally identify the table you want open (see line 10), but this is just a safety step. I always use this command at the beginning of a script.

10  OPEN – Opens the table to which you want to add the computed fields.

12  COM – Another comment t0 explain what I’m doing next.

13-14 DEFINE – Here’s where I specify which computed fields I want to add to the table. The extra spaces in line 13 aren’t necessary, I just like to line fields up for easy reading. This statement has 4 parts: 1) DEFINE FIELD – the command required, 2) name of the field being added, 3) COMPUTED – defines the type of field, and 4) the ACL expression that determines the value of the computed field. For computed fields, parts 1 & 3 never change; parts 2 & 4 change based on the field being defined.

16 COM – Another comment.

17 DEFINE – This statement makes the computed fields visible in the table. If you leave this out, the fields are still added in lines 13-14, but you won’t be able to see them unless you select Edit, Table Layout on the table, as they will be listed there. If you want to add the fields to a different, custom view, see ACL: Add a Custom View to a Table.

19 SAFETY – See the explanation in ACL tip: Create a File Import Script.

21 COM – Another comment. I like to add asterisks at the end of a script so that when I’m looking through the log, I can easily spot where a script ends.

Here’s what the customer table looks like after the script runs.

If you have several fields in your table, you may need to scroll to the right to see the fields, as they are usually added to the right of any existing fields.

If you’re new to scripting, I’d suggest adding the computed fields to your table manually, and make sure you get the results that you’re looking for. Then copy the ACL expression from the computed field as defined in the table layout (see step 8 in How to Add a Computed Field) to your script, remembering to add the DEFINE FIELD and COMPUTED parts.

Once you get more comfortable, you can then just write the script without defining the computed field manually in the table. Either way, if you make a mistake, fix it, and run the script again.