Lookup Tables are a valuable tool, available in any Summation system, that help ensure consistent data entry, provide shortcuts that can save keystrokes and time when entering data, and pick-lists of searched values. Although the Summation default tables and forms created in a new case include certain Lookup Tables, you can create your own custom Lookup Tables and associate them with your custom (or with other Summation default) fields.

Creating a Lookup Table

A Self-Replacing Lookup Table contains a list of shortcuts with corresponding longhand values. When an item is selected from the pick-list or a shortcut entered in the field the Lookup is attached to, the corresponding longhand value is auto-populated into the field the Lookup is attached to (for the active summary). A Fill-in Another Lookup Table contains a list of shortcuts with corresponding longhand values. When an item is selected from the pick-list or a shortcut entered in the field the Lookup is associated with, the corresponding longhand value is auto-populated into a field other than the field the Lookup is attached to.

Note: You can attach a single Lookup table to multiple fields that will be populated with values from the same pick-list. For example, the _Names Lookup table in the Summation demonstration cases is attached to the Author, Recipient, CC, and Mentions fields, among others. All those fields contain values from the same list of names and are attached to the same Lookup table to minimize the need for duplicate data-entry into multiple Lookup tables.

Tutorial Steps

Accessing the Form Editor

Before starting the creation of a Lookup table, make sure that no users are working in the database you want to modify. Lookup Tables are created through the Form Editor, which is a utility and no other users are in the database.

1. BACKUP YOUR DATABASE BEFORE PROCEEDING
   
   
2. Select the Utilities option from the Options menu.
   
   
3. Click the Create or Modify Forms button. The Form Editor will launch and the active form in the database will display in the view.
   
   Note: The Form Editor opens in the top left-hand corner of the window, so you may need to maximize to facilitate navigation.
   
   
4. If the open form is not the one you want to modify, then close it by selecting the Close Form option from the File menu, and open the desired one by selecting the Open Form option from the File menu. Select the desired form from the list and click OK.
   
   
5. Identify the field you want to attach the Lookup Table to and double-click inside the white (field) box.
   
   
6. When the Edit Properties dialog opens, select the Lookups tab.
   
   
7. Click the Add New Lookups button and Next at the first dialog
   
   
8. Select the type of Lookup Table (Self-Replacing or Fill-in Another Field) you want to attach to the field and follow the corresponding set of instructions below.

Creating a Self-Replacing Lookup Table

1. Follow steps (1 through 8) in the "In the Accessing the Form Editor" section, above.
   
   
2. Click the Self-Replacing Field radio button.
   
   
3. The next dialog prompts you to select a table to use as your Lookup. The table stores the data used to populate the attached field and consists of a Code Column (shortcut) and an Expansion Column (longhand of data).
   
   If you want to use an existing Lookup Table, choose a table from the dropdown list.
   
   If you want to create a new table, then click the New Table button. By default, the name of the new table and the expansion column will be derivatives of your fieldname, and the CodeColumn name will be CodeCol. These settings are sufficient, unless your table value conflicts with an existing column or table name, in which case you will be prompted to rename your Lookup table.
   
   
4. Click the Finish button to save your selections and attach the Lookup table to the selected field.

Attaching Multiple Lookups to a Single Field

In some cases, you may want to combine the self-replacement and fill-in functionality in the same field, to maximize use of data-entry shortcuts. For example, you would like to enter a shortcut value into the Author field, and auto-populate the Author field with the selected author's longhand name AND simultaneously auto-populate the Company field with the author's corresponding company. To accomplish this goal you must attach two Lookups (based on the same Lookup Table) to the same field (Author, in our example), which will populate that field (Author, in our example) with values from one column (Authorx, in our example) in the Lookup Table (_Names, in our example) and will populate another field (Company, in our example) with values from another column (Companyx, in our example) in the same Lookup Table (_Names, in our example). You are essentially attaching a Lookup Table to the same field multiple times.

1. Create a Self-replacing Lookup table named _Names (see the Creating a Self-Replacing Lookup Table section, above) attached it to the Author field, with a column named Authorx that populates the Author field.
   
   
2. When you return to the Lookups tab in the Field Properties dialog, click the Add New Lookup button.
   
   
3. Click Next at the first dialog.
   
   
4. Click the Fill-in Another Field button.
   
   
5. Select from the drop-down list the field you want to populate with the longhand value corresponding to the shortcut selected from the Lookup table (Company, in our example).
   
   
6. The next dialog displays the table values will be stored in (_Names, in our example) and gives you the option to modify it. Click the Modify Table button.
   
   
7. Click the Add Column button and enter a column name (Companyx, in our example) in the blank Expansion Column field that displays.§ A note data-type is the recommended.
   
   
8. Click OK when you are done.
   
   
9. Determine which expansion column will be used to populate the selected field. Select the appropriate column from the list and click Finish.

Note: When entering data into your lookup table, you will be prompted to enter a value for each expansion column. When a shortcut is entered into a field or item is selected from the pick-list, the longhand from each expansion column will populate the corresponding field (e.g. Shortcut = "PF" will populate "Connor Stevens" in the Author field and "Stevens Geotechnical" in the Company field).

Adding data to an existing empty Lookup Table

1. While in column or form view activate Edit Mode by clicking F2.
   
   
2. Place you cursor in the field with an associated Lookup table and click F6.
   
   If your Lookup Table is empty, then you will be prompted that the Lookup is empty and asked if you want to add entries to it. Click Yes to add new values in the Lookup table.
   
   
3. Fill in a shortcut value (e.g. PF) and an expansion column value for each expansion column available (e.g. 1. "Connor Stevens", and 2. "Stevens Geotechnical".).
   
   The shortcut (e.g. PF) is the value a user will enter in the field to auto-populate the longhand value. The expansion columns hold the longhand values that will be auto-populated into the designated fields.
   
   
4. Click OK to save your entries and continue adding more.
   
   
5. Click Cancel once you have completed adding all the desired values.
   
   
6. To add values to a Lookup table that contains items, click the Add button from the Lookup table dialog and proceed through Steps 3 through 5, above.
   
   
7. Once all modifications have been made, click OK to exit the Lookup Table.