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

Open ACL Edit Layout

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

Add an ACL expression

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.

Add an ACL expression

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.

Choose an ACL 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.

Replace 'string' with the field name

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?

Create SUBSTR expression

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:

ACL Verify Button

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.

ACL function error

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.

ACL green checkmark

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

ACL add expression

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.

Add computed field to table view

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

Add computed field to table view

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

Table view of computed field

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

16 Comments

Filed under ACL, Data Analytics, Free, Free Download, How to..., Written by Skyyler

16 responses to “ACL: How to Add a Computed Field

  1. blair151

    Great post – you should go work for ACL & teach them a thing or two about help documentation. Thanks!

    PS – Re: “Some British users call the red X a “red cross”, which makes me chuckle.” — What makes *me* chuckle is everyone (including me) calls it a “red X” (or “red cross”) when it’s really a *white* X. :-)

    Like

    • skyyleracl

      Blair,
      Thanks for the kind words. And another chuckle. A rather astute observation! It isn’t a red X either! I never noticed. You gave me a good laugh at my own expense. :)

      Like

  2. Terry Terrell

    This is what I get when I try to download the Customer.txt file per the instructions in Free Downloads (see the Download Links topic:

    [cid:image003.png@01CE02BE.C8862780]

    Thanks.

    Like

  3. Les Samuels

    Hi
    I am opening a table in an ACL script, and am then defining a Computed field. The Computed field is automatically appearing as the last field in the table. However, I would like the Computed field to be say the 3rd field in the table, as this position for it is more logical. This would then enable Users to run the script (which does lots of different things), open the table, and copy and paste the fields that they see into Excel. Is there a way that I can dictate in the script where the Computed field will appear without having to code the script to Extract all of the fields to another file just to get them in the order I prefer? Thank you.

    Like

    • Les,
      Thanks for your question. I answered it in my newest post at https://itauditsecurity.wordpress.com/2013/04/20/acl-custom-view-table/.

      The answer is yes, you can change the order in which the fields in the table are displayed. Let me know what you think.

      You could also, without changing the table or adding a custom view, have your script create an Excel report with the fields in any order you desire.

      Like

      • Les Samuels

        Your solution for adding a Custom View via a script works perfectly, thank you very much. When I change the custom view manually, the ACL log does not contain the DEFINE commands. If it did, I might have been aware of these commands, but I guess that ACL does not log manual activity such as this. By the way, although in the script I can understand the use of “OK” for the DEFINE VIEW, it seems that the “OK” for the DEFINE COLUMN does not actually serve a purpose, and instead, results in “Parameter ‘OK’ not recognized” being written to the ACL log when the script is run – hope you agree? Thanks again for your help.

        Like

        • Les,
          You are absolutely correct! Excellent catch. I updated my post based on your most helpful comments. Shame on me for not reviewing the log.

          For those readers interested in our discussion, the custom view article is at https://itauditsecurity.wordpress.com/2013/04/20/acl-custom-view-table/

          Yes, some commands ACL does not log when you do it manually, which is frustrating. So you have to know how to script those commands by hand or you’re out of luck. Someday I’ll compile a list of them.

          In an upcoming post, I’ll discuss this log issue specifically, because some interesting items are available for discussion. Thanks again for your feedback!

          Like

  4. Frank

    Can I add a column using a script that has a computed field?

    Like

    • Frank,
      Yes you can. Did you see the dialog between Les and skyyler above? Does that answer your question, or do you mean adding a column containing a computed field to a table using a script? The dialog above is more about how to position the computed field (or any field) in a custom view of a table.

      Like

      • Frank

        Yes, I would like to add the column via the script. It will not allow me to add via the recorder or from the log. Everthing I code seems to fail. If you have an idea for this, it would make my life a little bit better.
        Thanks
        Frank

        Like

        • skyyleracl

          Frank,
          Please see my post that will be published on 11/25/13 where I explain how to do it, titled, “ACL: How to Add Computed Fields via Script”. Let me know what you think. Thanks.

          Like

  5. Frank

    Hi Skyyleracl,
    In ACL projects users can create folders. Just wondering if a script can move fil’s to a folder that is within an acl project? For example the fil is at the root of the project, but I created a folder call May. Can the fil be moved to the May folder.

    Thanks
    Frank

    Like

  6. skyyleracl

    Frank,
    I’m not aware of any command to do this. You could write a script to extract the file to the new directory (use the SET FOLDER command to direct it to the folder you want the table in) and then delete the previous file. If you don’t delete the previous file, it will continue to take space on your hard drive.

    Like

  7. Teaching myself ACL and studying for the CISA in December and came across this site. Great work here!

    Like

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s