IDEA Tech Tip: Using @ Functions in Tax Audits
Q: I’m a tax auditor and would like to use IDEA to check sales and purchases records, look at mark-ups and verify that taxes are recorded correctly. Which @ functions can help me when appending files, sorting purchase invoices and linking names with similar addresses?
A: For your project, the @lower would be needed when joining two files based on character fields. Let’s say you are in an organization where there isn’t perfect integration between the general ledger (GL) and the accounts payable (AP) system, and in this cumbersome environment purchasing information is input manually into both the GL and AP. Let’s say your link between the two systems is a purchase order number (POID) that is a combination of letters and numbers (e.g., P322sz). If this is input manually in each system, there is a possibility that the inputter may use a variety of upper and lower case in each system.
If you were conducting a test to see if all items in the AP system were also input into the GL system where the AP file is the primary file and the GL is the secondary file, and you were looking for records with no secondary match, you would need to ensure that your purchase order (PO) fields were the same letter-case-wise to rule out any false positives. So you would need to create a new field in your AP file using the equation @lower(POID) and a new field in your GL using the same equation. Then you would do the match in the join using the new all-lowercase fields you created. This would assure you that any PO not in the GL would be a PO that didn’t get entered—not just a PO where a letter was input in a different case in each of the systems.
– Steven Luciani, CPA, CGA, CIDA – IDEA Certified Trainer and Electronic Commerce Audit Specialist at Canada Revenue Agency
A: The @lower function will take everything in a field and change all letters in capital case to lower case. In older versions of IDEA, you would need the @lower or @upper functions when doing comparisons of fields with text. So you would combine this function with the @Isin to look for matches.
Suppose you had a character field where you wanted to look for the word PO BOX. The field could have it as PO Box, or po box or in many different formats, so you would use the @lower to standardize the field to do the search. Your equation would be @Isin(“po box”, @lower(Address)). This would put everything in the address field into lowercase for the comparison with “po box”. Now IDEA has added a function called @Isini that does this for you, so the @upper and @lower are holdovers from when you needed them for comparison.
You can also use them for formatting. In Canada our postal codes are a mixture of letter and numbers, so if I wanted to standardize the postal code for a report I could use the @lower or @upper to make sure that all the characters are either in upper or lower case.
If you are linking names and addresses and using the @Isin function, you would want to use the @lower so that you don’t have to worry about upper and lower cases that don’t match.
– Brian Element, CPA, CISA, CIDA, CISE, CFE – IDEA Certified Instructor and Financial Advisor at Public Works and Government Services Canada
To find more useful IDEA tech tips and tricks, check out the forums at IDEAScripting.com.
About Alain Soublière:
Alain Soublière has many years of experience working with computer audit software. He worked in a senior management role as the IDEA Product Manager for many years before becoming Director of Product strategy for Caseware Analytics and more recently the Chief Product Strategist.