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.

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.

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.

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

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!

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

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).