Event 1 Office Connector Web HelpOnline Help

Exercise 1 - Vendor List
Contents Index

 

Introduction

During this exercise you will create a vendor list containing only subcontractors and suppliers.  An Office Connector workbook function will be used to augment the list with information not contained in the Vendor table.  You will learn about the following:
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 Query

This example uses a query as the source of vendors so the spreadsheet will automatically adapt as your vendor list changes.  You will apply conditions to limit the vendor list to subcontractors and suppliers.
  1. Launch Excel.
  2. Rename "Sheet1" to "Vendor List".
  3. Select cell A1.
  4. Click the Query Wizard toolbar button.
  5. Select the Timberline Construction data folder and click OK.
  6. Enter your operator ID and password and click OK.  
    Note -You will not be prompted for your password unless Timberline security is enabled.
  7. The Select Table screen is displayed.  Select the AP Vendor table and click Next.  
    Note -The quickest way to select the table is to simply start typing AP Vendor.
  8. The Select Fields screen is displayed.  Select Vendor, Name, Telephone, Fax Number, Contact 1 Name and Last Check Date.  Remember to use techniques like typing the L key to help you find Last Check Date more quickly.
  9. Click Next to display the Conditions screen.
  10. Click Add Condition to display the Add Query Condition window.
  11. Select Type for Field and One of for Comparison.
  12. Place the cursor in the text box to the left of the Add button and type the following: Subcontractor <Enter> Supplier <Enter> <Enter>.
  13. Click Add Condition to display the Add Query Condition window again.
  14. Type the Y key to select YTD Amount for Field.
  15. Select Not equal for Comparison and type a 0 for the value.  Your completed conditions should look like this:
  16. Click Next to display the Query Options screen.
  17. Change the Name from qryAPVendor to qryVendorList and click Finish.
    Note -We recommend the convention of prefixing your query names with "qry" to make them easy to find in Excel's Name box.

Step 2 - Add the First Check Date

The TSFind function locates the first row in the table that meets the desired conditions.  This step uses the Find Wizard to insert a TSFind function that shows the date of the first check written by each vendor.
  1. Select cell G1 and type First Check Date.
  2. Select cell G2.
  3. Click the Find Wizard toolbar button.
  4. The Select Table screen is displayed.  Select the AP Check table and click Next.
  5. The Select Fields screen is displayed.  Select Check Date.
  6. Click Next to display the Conditions screen.
  7. Click Add Condition to display the Add Query Condition window.
  8. Select Vendor for Field.
  9. Select Cell Reference for Value Type.
  10. Type A2 for Value and click OK.
  11. Click Finish to insert the formula.
  12. Double-click the fill handle to copy the formula down.

The formula in cell G2 should look like this:
=TSFind("MASTER_APM_RECORD_4","CDATE","CVENDOR='" & A2 & "'","0")

See Also



Content updated 8/4/2009
Copyright © 2009 Event 1 Software, Inc.  This documentation may not be copied in full or in part without written permission from Event 1 Software, Inc.