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.
6 responses to “ACL: How to Add Computed Fields via Script”
This explanation is very helpful. My scripts are now working great.
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!
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.
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? :)
Thanks, I found this helpful and would teach myself from now on
Super! Thanks for taking the time to leave me a note.