One of the most helpful tools I have found while learning ACL has been pre-writing my actions. What do I mean by pre-writing? I attempt to describe in English what I want ACL to do and what I expect the output to be. Are five records reasonable or 500? Should the computed field have 12 digits or only three? This has caught me off guard more times that I care to admit.
Let’s walk through an example from one of the tenants of ACL, data integrity verification. Consider a file where the client has provided us check information, invoice information, and PO number:
The client’s system is a bit crazy and randomly duplicates invoices. The problem is we know that it will duplicate the invoices without filling the PO number information. We can see this on row 5 and row 7. Row 10 is a legitimate blank, but we need to get ACL to tell us that.
In my head, here’s what I’m thinking,
“I need ACL to consolidate these records to remove the false duplicates. I want to summarize on the vendor (Paid_To) and invoice number (Invoice_No) and have all fields still available to me. Then I will search for blanks on the PO field. I expect one blank to show up (check 10006/row 10).”
I would then convert that paragraph to ACL:
- Summarize on Paid_To and Invoice_Date, with all other fields in the OTHER box.
- Set filter to ISBLANK(PO_No).
- Extract to final table.
In ACL scripting (if you aren’t familiar, don’t worry):
All this gives us the following table as output:
If you’re not familiar with the summarize command or scripting, don’t fret. My goal was to equip you with the skill of defining your expectation and process. Now when you run through the ACL tutorials and language guide, you can spend some time planning. I’d also suggest peeking at the log every so often. ACL writes your code for you, so it makes scripting very easy (which I’ll dive into in a few weeks).