In this exercise, you will create a simple reconciliation between Accounts Payable and General Ledger. You will learn about 'distinct' queries, the TSSum function and reinforce techniques learned in earlier exercises.
Note -
This tutorial example is designed to work with the Timberline Construction Sample Data.
Step 1 - Insert the List of Accounts
This example uses a query to produce a list of accounts that have been used in the Accounts Payable application. We can tell that an account has been used in AP because there will be transactions posted against it.
Select the Timberline Construction data folder and click OK.
If you have implemented security in Sage Timberline Office, then you will be prompted to log in. Enter your operator ID and password and click OK.
The Select Table screen is displayed. Select the AP Transaction table and click Next.
The Select Fields screen is displayed. Select Accounts Payable Account.
Click Next twice to advance to the Query Options screen.
Check the Exclude duplicate rows box and click Finish.
Note -
Although the AP Transaction table contains many rows, this query only returns a few. This is a result of selecting only the "Accounts Payable Account" column and excluding duplicate rows. We therefore get a list of distinct accounts instead of a row for every transaction.
Step 2 - Add Account Title and Balance Columns
Now that you have the relevant list of GL Account numbers from the AP Transaction table you can use the Office Connector Lookup Wizard to insert a TSLookup function that displays the account title and current balance from the GL Account table.
Click Add Condition and select Accounting Date for Field.
Select Less or equal for Comparison.
Select AsOfDate from the Value drop list and click OK. Your completed filter should look like this:
Click Finish to insert the formula.
The formula in cell D4 should look like this: =TSSum("CURRENT_APT_RECORD_1","TAMT","[TGLAP]='" & $A4 & "' AND [TACCTDT]<='" & TEXT(AsOfDate, "M/d/yyyy") & "'")
Step 5 - Add the Difference Column
The final step in the reconciliation process is to determine the difference between the balances in AP as compared to the balance in GL.
Select cell G3 and type AP Balance.
Select cell G4 and type the formula =$D4-$E4-$F4.
Select cell H3 and type Difference.
Select cell H4 and type the formula =$C4+$G4.
Click the Refresh button to fill down the formulas you entered in the previous steps.
Select columns D - F, right-click the column header and pick Hide from the popup menu.
Content updated 12/4/2012 Copyright � 2012 Event 1 Software, Inc. This documentation may not be copied in full or in part without written permission from Event 1 Software, Inc.