Monthly Archives: April 2012

Tips & Tricks #14

Applying RECOFFSET

We’ve been using RECOFFSET to identify split Purchase Orders/Requisitions and Duplicate Payments, calculate Running Totals, and identify Security Access violations. Please note: RECOFFSET does not work on all versions of ACL. We have only tested this on Desktop 9.1 and 9.2.

Record Offset can be used to move up and down your table to find conditions defined in your computed field. Here’s how we used Record Offset to find Potential Split Purchase Requisitions made within 2 days of each other:

SORT ON Requisitioner Req_Date Vendor_Number TO “Requisition_Sort” OPEN

DEFINE FIELD c_Matching_Date COMPUTED

RECOFFSET(Req_Date,-1) IF RECOFFSET( Requisitioner, -1) = Requisitioner AND RECOFFSET( Vendor_Number, -1) = Vendor_Number

Req_Date

DEFINE FIELD c_days_diff COMPUTED c_Matching_Date – Req_Date

Make sure there’s a blank space after DEFINE FIELD c_Matching_Date COMPUTED

First, we sorted the file so that we have the Requisition Number, Requisition Date, and Vendor Number together.

Second, we created a conditional computed field to test if previous Purchase Requisition had the same Requisitioner and Vendor Number. If it found a match, it would pull down the date from that above record.

Finally, now that we have both of our dates on one record, we can subtract them to determine the days between them. In our case, we’re looking for anything where ABS(c_days_diff) <= 2 AND ABS(c_days_diff) > 0.