ACL: How to Add a Conditional Computed Field

In ACL, a conditional computed field (CCF), is basically a regular computed field with some fireworks.

It looks and acts much like a regular computed field, but has some extra parts that do some extra work. Fortunately, the extras are NOT complicated, and after reading this post, you will find that will you use CCFs frequently.

So what’s the difference?

With a computed field, you use only 1 expression to extract/transform data in a field. With CFFs, you can use multiple expressions; you can even define a default expression to be used when the other expression don’t apply.

Following is a simple example that will make the concept much clearer.

First, we’ll add a CFF manually; then we’ll look at adding them via script.

Add a CFF Manually

Let’s assume we have an ACL table called sales, which has a field called Country. This field contains a number that represents the name of a country (see below). We want to display the name of the country in the table, so we need to add a field (a CFF) that identifies which country is associated with each number. Assume the numbers represent the following countries:

1 = England

2 = France

3 = Germany

CCFs must consist of a default value and at least 1 condition. The CFF we’re going to create will have a default value and 3 conditions.

Here’s the step-by-step:

1. In Project Navigator, double-click on the table to which you want to add the CCF (in this case, sales). Click the Edit Table Layout button (see below).

Remember: To enlarge images, click them. To return to the post, click Back or <– .

2. Click the fx button (see below) to add a new expression (fx stands for function/expression). 3. Type c_Country in the Name field with a Default Value of  ”***unknown” as shown below.

I added 3 asterisks (***) in front of “unknown” so that it stands out more when the default value is displayed in the field. Keep in mind that since the CFF is being created as a character field, the default value also has to be character format. Therefore, you have to put quotes (“) around the default value.

4. Click the green plus button to insert a condition. The Add a Condition and Value box appears as shown below.

5. To create the first condition, click the Condition button (see blue box above) to open the Expression Builder (see below).

6.  To build the expression, click on the Country field and then the equals sign (shown in red boxes below). As you click these items, they are added in the area shown in the blue box below. To complete the expression, type the number 1 so that the expression reads County = 1

Since the format of the original Country field is numeric, the number 1 is NOT enclosed in quotes; if the field’s format was character, the number would have to be enclosed in quotes, as in Country = “1”

7. To close the Expression Builder, click the OK button (not shown). The Add a Condition and Value box appears as shown below; the expression created in step 6 appears in the Condition field. In the Value field, type England in quotes as shown below. Then click OK to close the first condition.

8. To create the second condition, make sure the first condition is highlighted in black (below, left), and click the Duplicate Condition button, which makes a copy of the first condition (below, right). 9. Double-click the second condition (highlighted in black, above right),  and change the number in the Condition field from 1 to 2 and change the country in the Value field from England to France as shown below. Click OK. 10. The CFF will appear as follows: 11. To create the third condition, make sure the second condition is highlighted as shown above, and click the Duplicate Condition button (as shown in step 8). Double-click the newly created third condition, and change the Condition from 2 to 3 and change the Value from France to Germany (similar to what you did in step 9).

12. Click OK, and the CFF will appear as follows:

13. To save the CFF, click the green checkmark as shown above. Then on the next screen, click the X as shown below to close the table layout. 14. The results of the CFF are shown below.

Remember, before the CFF appears in the Default View of the table as shown above, you have to add the field to the view.

To add a field to a view, right click the first empty column in the table, and select Add Columns. Then select the field, and click OK  (for another example, see steps 10-12 in How to Add a Computed Field).

In the graphic above, notice that lines 8, 13, and 17 have a Country value of 4 and a c_Country value of ***unknown. If we knew that 4 = Italy, we could add another condition  to the CFF to fix that. However, sometimes the unknowns are the exceptions for which you are looking.

Watch ACL’s Computed Fields Video

ACL has a FREE boot-camp video called Computed Fields Cross Training that demonstrates how to add a CFF and it’s worth watching.

Since it covers computed fields, CFFs, and the differences between them, the entire video is worth watching. If you want to see just the CFF part (about 5 minutes), go here and start the video at 15:35 and watch through 19:45.

The differences between my procedures and the video:

• The video shows you an entirely different scenario, using different fields and expressions. It’s not as simple as what I showed you above, but it will help you understand another way to use CFFs.
• In the video, they use an existing field as the default value, which is very typical and useful. In my procedure, I used “unknown” because I often build the conditions one at a time and need to know which values don’t match any of the previously defined conditions (e.g., when cleaning user names or street addresses).
• In the video, each condition is created from scratch. In my procedure, I show you how to duplicate an existing condition and change it, which is often faster.

View all boot-camp videos.

Add a CFF via Script

Adding a CFF via script is easy, but it appears a little backwards, compared to how you do it mannually. See the code below for how to add the same CFF described above via a script. I’ve added a few comments that correspond to the line numbers in the script code.

Line Comments

1) This is the normal syntax for defining a computed field.

2) Blank line – Your first condition must start on line 3, so put a blank line on line 2. If you omit the blank line and start your conditions on line 2, the condition in line 2 will be ignored. When your CCF isn’t working right, this is the first thing to check.

The actual line numbers are not important; it doesn’t matter whether the first condition starts on line 3 or 33. The important point is that a blank line must separate the line that defines the field (in this case, line 3) and the line containing the first condition (in this case, line 1).

3-5) In each condition, the Value is first, then the Condition (which is opposite the manual method described above).

6) The default value is always last (opposite the manual method, where you must define it first).

Also, all lines after line 1 are generally indented for readability, but it is not required.

To add the fourth condition for Italy, just add type a new line for that condition between lines 5 & 6.

Rare Problem with CFFs

For some crazy reason, ACL counts every character included in a condition. If the number of characters in all of your conditions for any conditional computed field exceeds 254, ACL will give you an error if you ever try to export that field to Excel.

For example, the condition in line 3 in the graphic above contains about 26 characters, including the spaces. If you have 10 countries, you’d need 10 conditions. 10 conditions x 26 characters per condition = 260 characters, which is more than the 254 limit. I’m not sure exactly how ACL computes the total number of characters, and whether it includes the condition that you put in the default value field, but I’ve noticed this happens only with CFFs with many conditons.

So if you attempt to export the values produced by that conditonal field, you will receive the error that says: You can export fields with maximum of 254 characters to Excel.

For more on this error and 3 solutions, see ACL Error: Cannot Export to Excel.

See also:

How to Add a Computed Field

Teach Yourself ACL

Master List of ACL Articles and Tips