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.ACL add computed field scriptKEY:

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.

acl table after computed fields added

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.

About these ads

4 Comments

Filed under ACL, How to..., Scripting (ACL), Written by Skyyler

4 responses to “ACL: How to Add Computed Fields via Script

  1. Frank

    Hi Skyyler,
    This explanation is very helpful. My scripts are now working great.

    Frank

    • skyyleracl

      Frank, that’s great. I forgot to mention earlier that I was impressed that you didn’t give up on scripting. Too many people are too intimidated by it and give up.

      One problem with scripting is knowing which command you need when you don’t know its name. Had you known to look up “DEFINE” in the ACL help file, you’d have figured it out.

      When I first started scripting, I didn’t know about DEFINE either, and I used workspaces for computed fields when I could have used DEFINE. That kind of thing has happened several times with ACL–I sheepishly learn much later that an easier and faster way exists.

      It happens, so don’t get discouraged. Keep asking for help when you need it. I still do!

      • Frank

        Thanks again,
        One other question. Can a script change table properties. For example, changing text field to numeric, or 4 decimals to 2 places. My data / tables do not change.
        I appreciate all the help, every day I learn something new with this app.
        Frank

        • Frank,
          I’d suggest adding a computed field that uses the field you want to change as input. Search for “VALUE function” at http://docs.acl.com/acl/105/index.jsp

          That function changes text to numbers and allows you to specify the number of decimal places.

          So if the field you want to change is called “Cost” and you want 2 decimal points, the expression would be VALUE(Cost,2)

          Next time you need a function, check the list by searching “ACLScript function reference” at http://docs.acl.com/acl/105/index.jsp

          NOTE: I changed the URLs a couple times because they were not working. ACL doesn’t honor the same links you get from Google. Evidently they don’t let you link directly to the topics you want (even though they provide the URLs at the site).

          Hey, who needs skyyler? :)

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s