Some of these posts are oldies, and yet they are still pulling in plenty of traffic. Check out the list, and see if you missed any of them, especially new readers.
Category Archives: ACL
And the Command Cancelled message (see the end of this post regarding that message).
Usually, it means I did something stupid, and I can figure out what, and fix it pretty fast.
Sometimes I have to scratch my head for quite some time before I figure it out.
I wrote a post in 2017 about Deleting ACL Table Covers A Multitude of Sins. This post is an expansion of that post, but mainly focuses on the “Table Already Open” error.
These tricks are the kind that they don’t teach you in class or in tutorials (at least I’ve never learned any of them there; maybe I was in the bathroom during that session); I either figured them out on my own or had someone say, “Let me show you something.”
The Command Line
When I train someone in ACL, the command line is one of the first bonus items to which I draw their attention. The command line allows you to run individual ACL commands without using the ACL menu or scripts.
To open the command line: in the menu bar, click Window, Command Line. This will appear:
You can run most ACL commands from the command line, such as OPEN a table, ASSIGN a variable value, and lots more (the commands can be entered in lower/upper/camel case, but I use uppercase in this post to help them stand out).
My 2 most frequently used command are listed below.
DISPLAY – list the fields in a table, along with their start position, length, and more.
To run this command, 1) open the table you want to run this command against, and 2) enter the command in yellow in the command line, and press Enter.
Note that the last line shows you a computed field and the formula behind it.
DISPLAY VARIABLES – list all currently active variables, their type/format, and their values.
To run the following command, just enter it in the command line, and press Enter.
Note that user-defined variables (v_record and v_table) are shown, along with system variables (OUTPUTFOLDER and WRITE1). If you’re not familiar with ACL system variables, look them up in the ACL help file (it will be worth your time).
Note that 2 of the variables are character (C) type and 2 are numeric (N).
This command is extremely helpful when you are troubleshooting variables.
Bonus: Instead of DISPLAY, you can type DIS; instead of DISPLAY VARIABLES, you can type DIS VAR. Much shorter!
Bonus #2: Another useful use of the command line is to enter variable values. For example, if you have a NOTIFY command at the end of a script that will send an email if v_Run_Notify = “Y”, you can enter v_Run_Notify = “N” in the command line and press Enter to change the variable value and prevent the NOTIFY command from running while you test changes to your script.
Open a Table You Can’t Find
Sometimes I can’t find a table because I don’t remember (or know) which ACL folder it is hiding in (the folder in your project, not a Windows folder on your hard drive).
If you know the name of the table, you can just type OPEN <tablename> and press Enter (where <tablename> is the name of the table you want to open). When I don’t remember the table name or I’m too lazy to type it out, I copy the name from the ACL log or a script that uses it, and copy it to the command line.
When the table opens, you can then see what folder the table was hiding in (the folder is not shown in screenshot below).
Clear the Command Line
When you use the command line a lot, you have to clear it before entering another command. Instead of backspacing and deleting the text, or highlighting and deleting the text, just click the X at the far right.
Likewise, instead of pressing Enter after entering a command, you can click the checkmark.
When you’re working on a big project that contains many different tables, sometimes it’s hard to remember how that table was created. Or you haven’t opened the ACL project in a while, or you have to troubleshoot or review a project someone else created.
So what table(s) were used to create that table, and what filters/joins were used to create it? How many records did the original table contain?
I used to hunt through the ACL log or the scripts to find all that info, but for the most part, it’s all in the table history.
To access a table’s history, 1) open the table you’re interested in, and 2) from the menu bar, select Tools, Table History. You’ll see something like this:
The first line shows the original table (PcardTransactions) and the FILTER used. The second line shows the filtered data (all fields) was extracted to a new table (PCardUSA).
The third line shows number of records in the original table (Input) and the fourth line shows the number of resulting records (Output) in the extracted table.
If a JOIN was used, the table history would list the primary and secondary tables as well as the JOIN command parameters used.
The other nice thing is that you can take a screenshot of the table history and use it for documentation or evidence.
Bonus: Instead of selecting Tools, History from the menu, you can type DIS HIS in the command line, and press ENTER. Same results!
If you have some ACL tricks up your sleeve, let me know.
It was a great experience for me.
Well, sort of. No one likes being audited (ahem). But it gave me a fresh perspective of how others feel when I audit them.
This is the first of 3 posts; this post contains some background info on the project that was audited, and the second one discusses the audit and the results, and in the third post, I describe my perspective on the whole thing, and some takeaways.
It’s official: ACL is changing its name AND its spots.
I’ve claimed several times that ACL has left its first love (analytics) and doesn’t put enough work into their flagship product, ACL Analytics.
Correction: their FORMER flagship product.
At least they are publicly admitting it finally–they NO LONGER are an ANALYTICS company!
That is, according to an ACL user who attended the 2018 ACL Connections conference.
Contrary to what ACL has been touting as their new ‘robotics’ feature, it is NOT robotics process automation (RPA).
[The ‘robotics’ feature is due out later in 2018. It appears to be ACL’s latest attempt to get you to use their GRC software.]
ACL, via John Verver, defines the term this way in his RPA article: “The idea is a relatively simple one: get computers to perform tasks normally performed by humans, and cut resource and time requirements for many repetitive activities.” Continue reading
When you need to rename ACL tables, be careful to also rename the associated .fil file also.
Otherwise, you (or your ACL script) might get confused. You might delete the wrong table or .fil file, and create a head-scratching problem.
I know because I confused myself.Continue reading
If you’ve ever wondered what Audit Command Language (ACL) is, here’s a quick way to find out.
ACL has provided a quick, one-page introduction to ACL. And I mean quick.
It doesn’t explain a lot, but it gives you a quick peek at the basic user interface.
You could call it the ACL Overview for Dummies.
In this post, I want to expand on a problem I mentioned in an earlier post , 10 Signs Mgmt Doesn’t Really Support Analytics.
Audit management too often thinks that once a process or an audit is automated, ALL auditor/staff hours previously spent performing that process can be reassigned elsewhere.
That is not the case at all.
In my last post, I described Why Internal Auditors Should Care about Robotic Process Automation.
That might seem like a strange question, but a few managers and a VP have asked me just that recently. Here’s how I’ve answered it.
Since some of you are newer to the blog, I thought I’d bring a couple of my favorite posts to your attention.
This project automatically opens a folder on the LAN, reads the files in the folder, and loads all of them.
All I did was add one more file to the folder. ACL refused to load that one file.
Good analysts review their results and the log as they work in ACL, after they think they are done, and have others review their log before the ACL project is relied upon.
(You can’t imagine the dumb mistakes my team and I found that saved us a lot of embarrassment later.)
Consider the following advantages, listed in order of importance (to me):
Here’s a way to automate the download of data from Active Directory (AD), specifically group members, into ACL using adfind and the ACL Execute command.
I’ll walk you through it step-by-step.
This was posted before ACL released their own Active Directory driver, which I still haven’t figured out.
Even if you don’t use ACL, you might gain a better understanding of AD and LDAP in general….
In case you missed it, ACL released the next version of their Acerno product, renamed it ACL Excel Add-in, and made it FREE! 2021 UPDATE – it doesn’t look like it’s free any more; requires ACL subscription.
UPDATE – I’m guessing that since this product never caught on, they only give it away to subscribers – go figure.
So I thought I’d update my review.
For my original review of Acerno, see A Review of ACL Acerno. It still seems that I’m the only one who ever took the time to review the product (versus marketing blurbs, which are all over the ‘net), which appears to be a statement regarding its popularity.
Despite the poor popularity, since they updated it AND made it free, I decided to dive in for another look.
Note: This add-in is not just for auditors! Any one who regularly reviews data should consider using this simple, EASY-to-use software.
Please take the new & improved poll at the bottom of this post (also free).
Assume you have Table1, which contains 100 loan transactions. 10 of those transactions have a loan rate of 5% and 10 transactions have a rate of 6%. The remaining transactions have rates above 10%.
Recently, I ran an import script to import a delimited file into ACL, but the last 10 fields were not imported. And I didn’t know it right away, because I received no error message.
In addition (or should I say, in subtraction), the log did not indicate anything was wrong. Continue reading
- At work, you have a second computer (or virtual machine) just for running ACL.
If you have ever wanted to go to an ACL conference, this is the year to do it.
If you’re trying to learn ACL on your own, Teach Yourself ACL, the widest read post on this blog, has been updated.
ACL revised their website, which broke many of the links. Those are now fixed.
Until ACL does the dirty deed again (they like to change links for some reason, without redirecting the old ones).
I also updated other items that ACL has changed since the last blog update in 2012.
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?
Next time you get the cannot perform export to Excel error in ACL, try one of the 3 solutions described below. The full text of the error is:
Cannot perform the export.
You can export fields with maximum of 254 characters to Excel.
Once you’ve mastered creating computed fields, you’re ready to add computed fields to a table via script. It is easier than it sounds.
If you need some background on computed fields, see my previous posts, What is a Computed Field? and How to Add a Computed Field (manually). Now let’s explore writing a script that adds computed fields to a table.
As soon as you create an ACL script, you often have to add to it or edit it. There’s an easy way to do it.
Creating scripts (and editing them) is not as hard as many of you believe them to be.
Sure, it takes practice and time to learn the basics, but YOU can do it.
If you don’t learn scripting, you are NOT using ACL to it’s fullest, nor are you making the best use of your time.
Adding a custom view to an ACL table comes in handy when you want to 1) change the order of the fields in an ACL table, or 2) view a select number of fields.
You can add a custom view manually or via script. We’ll tackle the script version first.
This post is in response to Les’ question about reordering fields in a table.
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.
The profile article of the new ALC CEO, Laura Schultz, indicates a new direction at the company, but I’m not sure what that direction is. Here’s why:
1. ACL tweeted that Schultz is “fiercely determined” (see below), and in the profile, she talks about being “hell-bent” and “extreme” and taking vacations that involve “starving” and “afraid”. This is not your grandmother’s CEO, and maybe that’s the point. Either way, it doesn’t give me any comfort.
I’m talking about the File > New > Table command in ACL, also known as the Data Definition Wizard. Yes, you can create such a script, and I’m going to teach you how!
The good news is that it’s so much easier than you think. The bad news is that it doesn’t APPEAR easy, but it really is, because ACL does the heavy lifting for you. I promise that if you hang in there, you’ll so be a pro. Just try it once, and you’ll be hooked!
On occasion, I have received the following ACL error: The working directory does not have write access permission (see below).
Simply said, it means: the working directory is not working; something is not write. :)
Seriously, the working directory is the directory in which the application wants to start, which is why it is also called the starting directory. This is the directory to which ACL expects you to save your ACL projects. That’s why ACL needs write access to that directory.
When you’re trying to load a new file into an ACL table, you’ll sometimes get this error:: “Application error. ACL Desktop cannot complete this function…contact ACL Technical Support…” (see below).
Here’s the situations in which I’ve encountered this error, and how I’ve fixed it (most common and easiest to fix situations are first).
I haven’t been able to find any reviews of ACL Acerno, so I decided I better get to it.
What is Acerno?
According to ACL’s website, ACL Acerno is a Microsoft Excel Add-in that allows you to efficiently and easily investigate the results generated by ACL software or other sources and share your findings.
For a quick overview, watch this video or check out the quick reference sheet (pdf)—-This info must have been removed when the software was updated.
Acerno is $250 per user.
Even if you don’t read the rest of this, if you’re an auditor, please take the poll at the end of this post.
It’s a best practice to prefix all computed fields with “c_” (e.g., c_Region) for the following reasons:
1) Computed fields are not original data, and you should always keep this in mind. You should scrutinize the values in computed fields carefully, especially when you discover data or relationships that you were not expecting.
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.
Free ACL tutorials are available on YouTube, along with a lot of videos with talking heads. The tutorials walk you through how to do a couple tests, but I found the video resolution to be rather poor. Maybe it’s my equipment, maybe it’s the result of a company trying to adapt some tutorials they already have to another delivery method.