ACL: How to Add a Computed Field

If you’ve been wondering how to add a computed field to an existing ACL table, you’re at the right place. I’ll take you through it step-by-step.

In ACL tip: What is a Computed Field?, I defined computed fields and provided 2 examples. I suggest you read that post before you dive into this one.

That post also explains expressions and functions, which you need to understand when creating computed fields. Both that post and this one are long ones, complete with graphics. You might want to print them both out first…

In this post, I’ll show you how to add the c_Region field that is described in the computed field post. It’s not as hard as it looks.

If you want to do this exercise yourself and follow along, first download the Customer.txt file per the instructions in Free Downloads (see the Download Links topic).

When you load the file into ACL, make sure you change the Customer_Number field to text, and name the table ‘customer’. If you forget and leave the Customer_Number field in numeric format, the exercise below will not work because the SUBSTR function only works on text, not numbers.

How to create the c_Region field

1. In Project Navigator, double-click on the table to which you want to add the computed field (in this case, customer). Click the Edit Table Layout button (you could also select Edit > Table Layout on the File Menu).

To enlarge images, click them. Click Back or <– to return.

2. Click the fx button to add a new expression (fx stands for function/expression and opens the Expression Builder, which helps you build expressions step-by-step).

3. To create a computed field, you must enter, at a minimum, a Name and Default value for the field (see below). Enter c_Region in the Name field. If you know the expression you want to use [in this case, SUBSTR(Customer_Number, 1, 2) ], you could just type it into the Default Value field as shown in step 8. However, we’ll assume we need to build the expression from scratch, so just click the f(x) button.

4. Since the expression needed is a substring function*, scroll down in the Functions box until you see SUBSTR, and double-click it, which enters the function in the Expression box.

* Sometimes it’s not obvious what function you need, so you have to scroll through all the functions listed in the Expression Builder or look them up in the ACL help file, which provides examples of how to use each function to create an expression.

5. Double-click the word string in the Expression box, and then double click the Customer_Number field in the Available Fields box (see below, left). This automatically replaces the word string with the Customer_Number field (see below, right). In Expression Builder, you almost always replace the word string with the name of the field you are manipulating.

6. Double-click the word start and type 1, then double-click the word length and type 2 and you should see expression shown in the last box. Make sure you don’t lose the commas or parentheses, as they are important. To understand what this expression does and how it does it, see ACL tip: What is a Computed Field?

7. Next, click the Verify button (not required, but a good idea) to check if the expression is correct. If so, you will see the following:

If something is wrong, you’ll see an Error message like the one below, indicating the syntax is wrong, the wrong field is used, or something was left out. In the case below, the 2 was left out. To fix this, click OK, then add the 2 between the comma and the parenthesis. Click the Verify button again to check the expression.

8. If the expression is valid, click OK twice. The expression will appear in the Default Value field. To accept the entry, click the green checkmark.

9. Click the red X in the upper right corner to close the screen.

Some British users call the red X a “red cross”, which makes me chuckle.

10. Although the computed field was added to the customer table, it is not visible until you add it to your table view. To add it, right-click in the first empty column (to the right of the Name column), and select Add Columns.

You do NOT have to add the computed field to the table view. Adding the field to the view allows you to see the results of the expression on your data. The field is already in the table (it’s the first field shown in step 9) even if you can’t see it.

11. Then click on the c_Region field and then the right arrow (- ->) button to add the field to the Selected Fields box. Click OK (see below).

12. The new field is now visible in the table view. You’re done!

13. Now go back to my ACL tip: What is a Computed Field? and see if you can use what you learned to add the c_Customer_Type field described under the ‘Creating a Customer Type Field’ topic. It also uses the SUBSTR function to extract a different value from the same data.

Leave me a comment and let me know how you did and whether you got hung up somewhere.

If you enjoyed this post, you might want to read:

ACL tip: What is a Computed Field? –  details about the c_Region computed field

ACL tip: Prefix Computed Fields with c_

Teach Yourself ACL

All my posts about ACL