A computed field is a field in an ACL table that you create using expressions.
An ACL expression is similar to a Microsoft Excel formula [e.g., =SUM(A1:A2)] in that it contains at least one function [like SUM]. Excel formulas operate on cells (like A1 and A2), but computed fields operate on fields.
Like Excel formulas, computed fields take data you already have to create additional data by combining it, calculating it, extracting parts of it, or manipulating it. However, Excel and ACL use different syntax, and ACL has greater capabilities.
To master ACL, you need to understand and wield the power of computed fields. But first you need to understand the functions that are used to create the expressions. Computed fields can be a bit scary at first, but YOU can learn to use them; it just takes a little time and persistence. (See Teach Yourself ACL.)
If you use Excel functions like vlookup and subtotal, you shouldn’t be afraid of ACL computed fields, expressions, or functions. If you are afraid of computed fields, this post is especially for you, because I used to fear computed fields.
The ACL Help file provides a good definition of computed fields. As you learn about computed fields, keep coming back to it, and it will make more and more sense [brackets are mine]:
Computed fields are fields that are defined using ACL expressions. They are called computed fields because they display the result of the expression, which can be the result of a calculation or operation, or the return value of an ACL function. Computed fields are used to display information in an ACL table that is not present in the data source [your original file or table], or is not in the required format in the data source.
Let’s look at 2 examples of a simple (but very useful) ACL function called SUBSTR (short for substring). This function extracts a subset, or substring, of characters from another field. It’s a good place to start, and soon you will be using it to win friends and influence people.
However, before you use a function, you always need to check the type (or format) of the data the function operates on (look in the ACL help file under the function’s name).
The SUBSTR function is a character function, which means it only works on data that is formatted as characters (text). This function will NOT work on data that has a format of numeric or date. If you try to use SUBSTR on the wrong type of data, you see the following error message: Character expression is required. In other words, this message means that the function you are using only works on text, and the field you’re trying to apply the function to is NOT formatted as text (so change the field format to text or use a function that works on the current format of the field).
The examples below involve a field that contains customer numbers, but when I loaded the file into ACL, I changed the Type of the customer number field format to Text. They still look like numbers, so don’t get confused—they are in character (text) format.
I know this is a lengthy post, but please hang with me a bit longer. If you have questions or a better way to explain something, I’d love to hear it. Just leave me a comment.
Creating a Region Field
Assume you have a Customer_Number field that contains the data shown below, and the first 2 characters of the customer number identify the region the customer resides in. Therefore, you want to create a computed field to extract the region number into a field called c_Region.*
The syntax of the SUBSTR function is SUBSTR(string, start, length)
To create the c_Region field, use the expression
SUBSTR(Customer_Number, 1, 2)
The result of this expression will be:
|12345678||12||The first 2 numbers are 12|
|21094810||21||The first 2 numbers are 21|
|91827364||91||The first 2 numbers are 91|
Creating a Customer Type Field
This computed field is similar to the previous one. We’ll use the same string (Customer_Number), but extract different data from it.
Assume the Customer_Number field contains the same data as before, and the 8th character of the customer number identifies the customer type. Therefore, you want to create a computed field to extract the customer type number into a field called c_Customer_Type.
Again, the syntax of the SUBSTR function is SUBSTR(string, start, length)
To create the c_Customer_Type field, use the expression SUBSTR(Customer_Number, 8, 1)
The result of this expression will be:
|12345678||8||The 8th number is 8|
|21094810||0||The 8th number is 0|
|91827364||4||The 8th number is 4|
In summary, to use the SUBSTR function, identify the field you want to extract data from (string), the position you want to start extracting from (start), and how many characters to extract (length). That wasn’t so hard, was it?
Why not create a spreadsheet with the 3 customer numbers shown above, import it into ACL (ensuring the numbers are formatted as text), and practice creating these computed fields? I’d appreciate any feedback you have, so leave a comment.
* It’s a best practice to begin all computed fields with “c_” (e.g., c_Region). I explain why in ACL tip: Prefix Computed Fields with c_.
How to Add a Computed Field – the sister post to the one above