Office Connector Web HelpOnline Help

Exercise 1 - Vendor List
Contents Index

 
Note - you can select 720p in the video bar to make the text clearer.

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.

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 sample data folder and click OK.
  6. 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.
  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.  
    Note -Use techniques like pressing Ctrl-F for Find or pressing 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. Select Literal Value for Value Type.
  13. Select Subcontractor and Supplier from the Value drop list.  If one of those values does not appear in your data, you can type it in and click the Add button or press Enter.  Once the list is complete, click OK.
  14. Click Add Condition to display the Add Query Condition window again.
  15. Type the Y key to select YTD Amount for Field.
  16. Select Not equal for Comparison and type a 0 for the value.  Your completed conditions should look like this:
  17. Click Next to display the Query Options screen.
  18. 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.  Start typing AP Check to select the AP Check table and click Next.
    Note -If more than one AP Check table is listed, select the one with MASTER.APM in the File column.
  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 Filter 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. Click the Refresh button to copy the formula down.
  13. If you want to get rid of the zeros, use the technique described in Formatting Text Values to Remove Zeros.

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

See Also



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.