OCW Exercise 1 – Update AP Vendor Addresses

Introduction

During this four-step exercise, you will create a vendor list showing a list of vendors and their respective addresses. You will then use Office Connector Write functionality to allow addresses to be updated in Excel and then written back to Accounts Payable.

You will learn about the following:

• Write Wizard
• TSWrite Worksheet Function
• Send Data to Sage 300 Construction and Real Estate

IMPORTANT NOTE - In order to write data, permission to write data via Office Connector must first have been granted for your Sage operator ID via Office Connector Administrator. In addition, ODBC Write permission must be granted for your Sage operator ID within Sage Desktop - Security Administration.

Step 1 - Insert the Query

This step uses the Office Connector Query Wizard to insert a query that provides a list of your vendors.

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 300 CRE, 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.
8. Select the following columns:
   • Vendor
   • Name
   • Address 1
   • Address 2
   • City
   • State
   • Zip Code
9. Click Finish.

Step 2 - Add Write Instructions

This step will use the Office Connector Write Wizard to insert TSWrite functions that serve as instructions to Office Connector so that it knows which cell values to write to which database columns etc.

1. Select cell H2.
2. Click the Write Wizard toolbar button.
3. Select the AP Vendor table and click Next.
4. Click Modify Only and click Next
5. In the Value column, type $A2 and click Next.
6. Select the following columns and then click Next:
   • Address 1
   • Address 2
   • City
   • State
   • ZIP
7. in the Value column, type the following cell references:
   • $C2
   • $D2
   • $E2
   • $F2
   • $G2
8. Click Finish
9. Select cells H2 through L2
10. Double-click the fill-handle to copy the TSWrite formulas down to all rows of the query.

NOTE - The formulas in columns H through L serve as instructions to Office Connector Write. You may wish to hide these columns since they do not display a value that would be useful to the end-user.

Step 3 - Make Some Address Changes

Change one or more of the addresses shown in columns C through G.

Step 4 - Write Data

This step will read the values from your worksheet, display a list of the data values to be written back to Sage 300 CRE, and then carry out the updates when you choose to proceed.

1. Click Send Data to Sage 300 Construction and Real Estate on the Office Connector toolbar.
2. Scroll up and down the list of records shown in the Approve Changes to see the data values that will be written to each record.
3. Click Write. Once the writing has finished, a recap report will be displayed in your default web browser.
4. When prompted if you would like to refresh your queries and functions, click Yes

NOTE - This example writes all address values back to Sage 300 CRE regardless of whether or not the values have changed. In the next exercise, you will learn how to use simple conditional logic to control whether or not specific values are written.