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
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. :-)
LikeLike
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. :)
LikeLike
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.
LikeLike
Terry,
Not sure what happened. Sorry about that. I tweaked the link. Please try again and let me know if you still have problems.
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike
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!
LikeLike
Can I add a column using a script that has a computed field?
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
Teaching myself ACL and studying for the CISA in December and came across this site. Great work here!
LikeLike
Gabe,
Thanks! Please spread the word and let me know what else I can do.
Mack
LikeLike
Thanks for the great info, I’m trying to figure out if DEFINE FIELD, COMPUTED, SUBSTRING and IF can all be used together to extract a middle part out of a filename from a Directory table in ACL.
Example:
DEFINE FIELD c_JulianDate COMPUTED SUBSTRING(FILE_NAME, 53, 6) IF c_FileType=”ZZ*”
and it won’t work?
Not sure if it’s an ACL limitation or a syntax issue?
The problem is, I need to use
SUBSTRING(FILE_NAME, 52, 5) IF c_FileType=”RR” (or anything ZZ)
but use the different start parameters of
SUBSTRING(FILE_NAME,53, 5) IF c_FileType=”ZZ”.
Here’s full context: =========
Open t_directory_check_for_missing_julians
DEFINE FIELD c_FileName COMPUTED SUBSTRING(FILE_NAME, 40,2)
DEFINE FIELD c_FileType COMPUTED SUBSTRING(FILE_NAME, 43,7)
DEFINE VIEW Default_View OK
DEFINE FIELD c_Julian COMPUTED SUBSTRING(FILE_NAME, 52,5) IF c_FileType=”RR”
DEFINE FIELD c_Julian COMPUTED SUBSTRING(FILE_NAME, 53,5) IF c_FileType=”ZZ”
Not sure how to make it use 53,5 IF = ZZ and if not equal to ZZ then use the starting position of 52 for everything else?
LikeLike
Chris,
Yes, you can do this, but you have to use what is called a conditional computed field. In other words, you have to indicate what the value would be IF c_FileType = RR, ZZ, or neither.
DEFINE FIELD c_Julian COMPUTED
SUBSTRING(FILE_NAME, 52,5) IF c_FileType = ‘RR’
SUBSTRING(FILE_NAME, 52,5) IF c_FileType = ‘ZZ’
“Neither”
In a conditional computed field, the DEFINE FIELD COMPUTED is on the first line, and the first condition is on the 3rd line. The second line must be blank. If you put the first condition on the 2nd line, it will be ignored.
Then you put each condition on a separate line, but have to put what the resulting value FIRST and then the condition SECOND.
The last condition is always the default value and must match the format of the conditions. In other words, if the conditions result in a character value (in this case, filename), the default value has to be character also. Same is true with numeric, date, and other formats.
See this post @ https://itauditsecurity.wordpress.com/2014/02/03/acl-how-to-add-a-conditional-computed-field/
Cheers!
LikeLike