Here’s a couple of my favorite ACL tricks & treats that I use frequently to get me through the day a little faster and a little less frustrated.
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.
Table History
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.
You can easily use Excel’s Flash Fill tool to transform data fast, without formulas.