Note - you can select 720p in the video bar to make the text clearer.
In this exercise, you will create a workbook that displays contact information from Address Book. You'll use Office Connector functions to bring together information from multiple tables and Excel's features to make it an interactive tool. You will learn about the following:
Select the Timberline Construction data folder and click OK.
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.
The Select Table screen is displayed. Select the AB Company table and click Next.
The Select Fields screen is displayed. Select Contact ID, Company Name, Primary Contact ID, Primary Phone and Primary Fax.
You can also press Ctrl-F to bring up the Find box and search for any part of a field name. If more than one field name contains the text you search for, keep pressing F3 to cycle through all of the matching fields.
Click Finish to insert the query.
Step 2 - Add the Contact Information
A single company can have multiple addresses and multiple contacts. This example will use the Office Connector Lookup Wizardto insert a TSLookup function that displays the information for the company's primary contact.
Notice that the Primary Contact ID is just a string of numbers and letters. This is an internal ID that identifies a row in the Person table. We will get the contact information for the primary contact by looking up the ID in the Person table.
Right-click column header D and pick Insert from the popup menu. (Repeat this step so that you insert two columns).
Right-click column header C and pick Hide from the popup menu.
Step 3 - Add the Address Information
Looking up address information is very similar to looking up contact information. The biggest difference will be that since a contact can have multiple addresses, we will need an additional piece of information to tell the TSLookup function which address we want. In this case we want the street address for the primary contact.
Use the following steps to sort the data alphabetically by company.
Select cell B3.
Click Data / Sort... on the Excel ribbon or menu.
Select Company Name in the Sort By list.
Select the Ascending or A to Z option.
Click OK to apply the sort condition.
Step 5 - Add the AutoFilter
Excel's AutoFilter is a simple yet powerful tool that you can use to quickly view only the data you want to see. In this example we'll create a custom AutoFilter to view all of the companies whose primary contact has a telephone number in the 503 area code.
Select cell B3.
Click Data / Filter on the ribbon or Data / Filter / AutoFilter on the Excel menu.
Select (Custom...) or Text Filters / Custom Filter from the drop down list in cell F3 to show the Custom AutoFilter window.
In the first drop-down list, select begins with.
In the next column, type (503).
Click OK to apply the filter.
Step 6 - Add Company Information
The TSDataFolder function returns the name of the current data folder. It can also include the full path to the folder, which will be the case in this example. Using the TSDataFolder function is a good practice because it tells you which company the rest of the information on the screen applies to.
Select cell B1 and type Data Folder.
Select cell D1 and type =TSDataFolder(True) or use the Insert Function feature of Excel.
If you replace True with False, then the path to the data folder will be omitted.