3305 Main Street Ste 019 Vancouver, WA 98663

OCQ 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
Note - This tutorial example is designed to work with the Timberline Construction Sample Data.
Step 1 - Insert the Bank Accounts List
This example uses a query to provide a dynamic list of bank accounts.
  1. Launch Excel.
  2. Rename "Sheet1" to "Lost Checks".
  3. Select cell A3.
  4. Click the Query Wizard toolbar button.
  5. Select the Timberline Construction data folder and click OK.
  6. If you have implemented security in Sage 300 Construction and Real Estate, then you will be prompted to log in.  Enter your operator ID and password and click OK.
  7. The Select Table screen is displayed.  Select the CM Bank account table and click Next.
  8. The Select Fields screen is displayed.  Select Bank account and Description.
  9. Click Finish to insert the query.
Step 2 - 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.

  1. Select cell B1 and type Checks on or before:.
  2. Select cell C1 and type 4/30/2011, substituting the year as appropriate for your sample data.
  3. Select cell C1, click in the Name box, type CutoffDate and press Enter.
    For more information, see Creating a Named Range.
Step 3 - 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.

  1. Select cell C3 and type Potential Lost Checks.
  2. Select cell C4.
  3. Click the Count Wizard toolbar button.
  4. The Select Table screen is displayed.  Select the CM Transaction table.
  5. Click Next to display the Conditions screen.
  6. Click Add Condition to display the Add Filter Condition window.
  7. Select Bank account for Field.
  8. Select Cell Reference for Value Type.
  9. Type $A4 for Value and click OK.
  10. Click Add Condition and select Type for Field.
  11. Select Literal Value for Value Type.
  12. Select Chk from the Value drop list and click OK.
  13. Click Add Condition and select Reconciliation Status for Field.
  14. Select Literal Value for Value Type.
  15. Select O from the Value drop list and click OK.
  16. Click Add Condition and select Reference Date for Field.
  17. Select Less or equal for Comparison so we get checks with a reference date on or before what we enter.
  18. Select Cell Reference for Value Type.
  19. Select CutoffDate from the Value drop list and click OK. Your completed filter should look like this:tutlostcheckcountfilter
  20. Click Finish to insert the TSCount function.
Step 4 - 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.

  1. Select cell B8 and type Bank account ID:.
  2. Select cell C8 and type First.
  3. Select cell C8, click in the Name box, type BankAccountID and press Enter.
    For more information, see Creating a Named Range.
  4. Select cell A10.
  5. Click the Query Wizard toolbar button.
  6. The Select Table screen is displayed.  Select the CM Transaction table and click Next.
  7. The Select Fields screen is displayed.  Select Bank account, Description, Accounting Date, Reference Date, Subtraction, Check and Payee.
  8. Click Next to display the Conditions screen.
  9. Click Add Condition to display the Add Query Condition window.
  10. Select Bank account for Field.
  11. Select Parameter for Value Type.
  12. Select BankAccountID from the Value drop list and click OK.
  13. Click Add Condition and select Type for Field.
  14. Select Literal Value for Value Type.
  15. Select Chk from the Value drop list and click OK.
  16. Click Add Condition and select Reconciliation Status for Field.
  17. Select Literal Value for Value Type.
  18. Select O from the Value drop list and click OK.
  19. Click Add Condition and select Reference Date for Field.
  20. Select Less or equal for Comparison so we get checks with a reference date on or before what we enter.
  21. Select Parameter for Value Type.
  22. Select CutoffDate from the Value drop list and click OK. Your completed filter should look like this:
    tutcmtransfilter
  23. Click Finish to insert the list of checks.
Step 5 - Add Interactivity

As the report exists now, you can change either of the input values and click the Refresh button to see updated results.  One odd thing about the behavior, though, is the fact that the Potential Lost Checks counts will update before you click Refresh.  This is because we used a TSCount formula for the count, and workbook formulas automatically recalculate whenever one of the inputs changes.

In most cases, you will want to leave query parameters in their default state so you can control how the report refreshes.  In some cases, though, you want instantaneous updates similar to workbook formulas, and this example will showcase how to accomplish that.

  1. Right-click cell A10 and select Parameters from the popup menu.
    • Note - You can actually right click anywhere inside the query results to bring up the context menu.
  2. You will see the two query parameters listed on the left with BankAccountID already selected.
  3. Check the box in the lower-right corner labeled Refresh automatically when cell value changes.
  4. Select CutoffDate and check the Refresh automatically when cell value changes box again.
  5. Click OK
  6. Try modifying the value in C8 using the different bank accounts listed in column A above as a reference.
  7. Try modifying the cutoff date in C1 to see how the report updates as you change input values.