Exercise 3 - Lost Check Report

Introduction

In this exercise, you will create a tool for finding uncleared checks older than a specified date. You'll use input cells with queries and Office Connector functions to make the search interactive. You will learn about the following:

The TSCount Function
Using an input cell to interact with your data
Using parameters in queries
Using dates as conditions in queries and functions

Note -	This tutorial example is designed to work with the Timberline Construction Sample Data.

Note -	This tutorial requires Office Connector version 2.01.0009 or above. Download the latest version here. You can use the About window command to determine which version you have currently installed.

Step 1 - Insert the Bank Accounts List

This example uses a query to provide a dynamic list of bank accounts.

Launch Excel.
Rename "Sheet1" to "Lost Checks".
Select cell A3.
Click the Query Wizard toolbar button.
Select the Timberline Construction data folder and click OK.
Enter your operator ID and password and click OK.

Note - You will not be prompted for your password unless Timberline security is enabled.
The Select Table screen is displayed. Select the CM Bank account table and click Next.
The Select Fields screen is displayed. Select Bank account and Description.
Click Finish to insert the query.

Step 2 - Add the Check Count Column

Use a TSCount function to see how many potentially lost checks there are for a given account. The TSCount function returns the number of rows in a table that meet the specified conditions.

Double-click the border between column headers B and C to autosize the Description column.
Select cell C3 and type Potential Lost Checks.
Select cell C4.
Click the Count Wizard toolbar button.
The Select Table screen is displayed. Select the CM Transaction table.
Click Next to display the Conditions screen.
Select Bank account from the Field list.
Type First in the Value column.
Select Reconciliation Status from the Field list.
Type a capital letter O in the Value column.
Select Type from the Field list.
Type Chk in the Value column.
Select Reference Date from the Field list.
Change = to <= so we get checks with a reference date on or before what we enter.
Type 04/30/2001 in the Value column. Your completed filter should look like this:
Click Finish to insert the TSCount function.

The formula in cell C4 should look like this:
=TSCount("REGISTER_CMT_RECORD_1","((TBNKACC = 'First') AND (TRECOND = 'O') AND (TRANTYP = 'Chk') AND (TTRANDT <= '04/30/2001'))")

Step 3 - Use a Cell Reference for Bank ID

The formula you created in the previous step has a static value of First for the bank. If you replace the bank ID with a cell reference, the formula can then be copied down for every bank in the list.

Select cell C4.
In the Excel Formula Bar, change 'First' to '" & A4 & "'.

The formula in cell C4 should now look like this:
=TSCount("REGISTER_CMT_RECORD_1","((TBNKACC = '" & A4 & "') AND (TRECOND = 'O') AND (TRANTYP = 'Chk') AND (TTRANDT <= '04/30/2001'))")

Step 4 - Add a Date Input Cell

This step identifies an input cell where you can enter the cutoff date that can be used to identify checks that may qualify as lost.

Select cell B1 and type Checks on or before:.
Select cell C1 and type 4/30/2001.

Step 5 - Use a Cell Reference for Cutoff Date

There is a trick to using date values in Excel formulas. Excel is designed to work with numbers, so it stores date values internally as numbers. If you create a formula that references a cell containing a date, Excel uses the underlying number. What you really want is the date text exactly as you typed it. Use the TEXT function to instruct Excel to use the value that is displayed on the screen instead of the internal representation.

Select cell D1.
Type ="My Date Is:" & C1 and press Enter.
Notice how the date is displayed as a number.
Change the formula in cell D1 to ="My Date Is:" & TEXT(C1, "mm/dd/yyyy").
Notice how the date is displayed using the format mm/dd/yyyy.
Delete the formula in cell D1.
Edit the formula in cell C4 to change the date value from '04/30/2001' to '" & TEXT($C$1,"mm/dd/yyyy") & "'.
Copy the formula down by double-clicking the fill handle.

The formula in cell C4 should now look like this:
=TSCount("REGISTER_CMT_RECORD_1","((TBNKACC = '" & A4 & "') AND (TRECOND = 'O') AND (TRANTYP = 'Chk') AND (TTRANDT <= '" & TEXT($C$1, "mm/dd/yyyy") & "'))")

Step 6 - Query the List of Potentially Lost Checks

This step adds a query that uses the bank ID and cutoff date that you specify to display a list of potentially lost checks. Similar to using cell references in formulas, queries use parameters to tie the conditions to cell values. You'll use the Query Wizard's advanced view to insert parameter markers for this query.

Select cell B10 and type Bank account ID:.
Select cell A12.
Click the Query Wizard toolbar button.
The Select Table screen is displayed. Select the CM Transaction table and click Next.
The Select Fields screen is displayed. Select Bank account, Description, Accounting Date, Reference Date, Subtraction, Check and Payee.
Click Next to display the Conditions screen.
Select Bank account from the Field list.
Type First in the Value column.
Select Reconciliation Status from the Field list.
Type a capital letter O in the Value column.
Select Type from the Field list.
Type Chk in the Value column.
Select Reference Date from the Field list.
Change = to <= so we get checks with a reference date on or before what we enter.
Type 04/30/2001 in the Value column. Your completed filter grid should look like it did when you used the Count wizard.
Check the Show advanced view box.
Change 'First' to ?.

Note - Unlike formulas, parameters do not include the single quotes.
Change {d '2001-04-30'} to ?.
Your completed text should look like this:
Click Finish to insert the query.
Excel will prompt you for parameter 1. Click cell C10 (Bank ID), check both boxes and click OK.
Excel will prompt you for parameter 2. Click cell C1 (Cutoff Date), check both boxes and click OK.
Try modifying the values in cells C1 and C10 to see how the check list automatically updates to show you the potentially lost checks for the bank and cutoff date you specify.