OCQ Exercise 2 – Address Book

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

Introduction

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:

  • The TSLookup Function
  • The Excel AutoFilter
  • Sorting
  • The TSDataFolder Function

This tutorial example is designed to work with the Timberline Construction Sample Data.

Step 1 - Insert the Query

This example uses a query to list companies so the spreadsheet will automatically adapt as your address book changes.

  1. Launch Excel.
  2. Rename "Sheet1" to "Address Book".
  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 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 AB Company table and click Next.
  8. The Select Fields screen is displayed.  Select Contact ID, Company Name, Primary Contact ID, Primary Phone and Primary Fax.
    • Note - 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.
  9. 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 Wizard to 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.

  1. Right-click column header D and pick Insert from the popup menu.  (Repeat this step so that you insert two columns).
  2. Select cell D3 and type Contact Name.
  3. Select cell E3 and type Email Address.
  4. Select cell D4.
  5. Click the Lookup Wizard toolbar button.
  6. The Select Table screen is displayed.  Select the AB Person table and click Next.
  7. The Key Values screen is displayed.  Type $C4 for Contact ID and click Next.
  8. The Select Fields screen is displayed.  Select Person Name and E-mail address.
  9. Click Finish to insert the formulas.
  10. 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.

  1. Select cell h5 and type Address 1.
  2. Select cell I3 and type Address 2.
  3. Select cell J3 and type City.
  4. Select cell K3 and type State.
  5. Select cell L3 and type Zip.
  6. Select cell H4.
  7. Click the Lookup Wizard toolbar button.
  8. The Select Table screen is displayed.  Select the AB Company Person Address table.
  9. Click Next to display the Key Values screen.
  10. Type $A4 for Contact ID
  11. For Address Type, uncheck the Cell Reference box and type Street address.
  12. Click Next to display the Select Fields screen.
  13. Select Address 1, Address 2, City, State and Postal Code.
  14. Click Finish to insert the TSLookup functions.
  15. Right-click column header A and pick Hide from the popup menu.
  16. Click the Refresh button to fill down the formulas you entered in the previous steps.
Step 4 - Sort the Data

Use the following steps to sort the data alphabetically by company.

  1. Select cell B3.
  2. Click Data / Sort... on the Excel ribbon or menu.
  3. Select Company Name in the Sort By list.
  4. Select the Ascending or A to Z option.
  5. 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.

  1. Select cell B3.
  2. Click Data / Filter on the ribbon or Data / Filter / AutoFilter on the Excel menu.
  3. Select (Custom...) or Text Filters / Custom Filter from the drop down list in cell F3 to show the Custom AutoFilter window.
  4. In the first drop-down list, select begins with.
  5. In the next column, type (503).
  6. 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.

  1. Select cell B1 and type Data Folder.
  2. Select cell D1 and type =TSDataFolder(True) or use the Insert Function feature of Excel.
    • Note - If you replace True with False, then the path to the data folder will be omitted.