ACL tip: What is a Computed Field?

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.

Mastering ACL

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.

SUBSTR

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

Customer_Number
12345678
21094810
91827364

The syntax of the SUBSTR function is SUBSTR(string, start, length)
where:

To create the c_Region field, use the expression
SUBSTR(Customer_Number, 1, 2)
where:

The result of this expression will be:

Customer_Number	c_Region	Explanation
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 8^th 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)
where:

To create the c_Customer_Type field, use the expression SUBSTR(Customer_Number, 8, 1)
where:

The result of this expression will be:

Customer_Number	c_Customer _Type	Explanation
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_.

See also:

How to Add a Computed Field – the sister post to the one above

Teach Yourself ACL

Master List of ACL Articles and Tips

Advertisement