Creating lookup/validation tables

The table validation feature enables you to assure that everyone within your organization uses the same codes to designate the same thing by either checking entered values against a list of approved values (validation) or by providing a fixed list of values from which users must choose (lookup).

For example, the STATE_CODES table comes preloaded with state and regional two-character abbreviations, and iMIS rejects entries in the St/Prov field that fail to match those in the table. While you are entering or editing a field linked to a table, you can view a selection window of the available table values through the lookup feature.

Expansion tables provide input replacement for specified fields. With this keystroke-saving technique, the user enters an abbreviation for a field, and the table look-up replaces it with the associated replacement text. This feature is useful for fields that commonly contain the same text. For example, the Prefix field can expand abbreviations in data entry according to the PREFIX table:

• Entering m expands to Mr.
• Entering p in the Title field expands to President
• Entering vp expands to Vice President

List string fields will only validate against a table if and when a general table is linked to the field. With a few fields, iMIS uses the table for expansion only (in which case the table associates abbreviations and expanded text) but does not restrict the text that users can enter. For example, the TITLE table expands vp to Vice President but it will not keep you from entering a new title that is not in the table, such as Sales Representative.

Some fields are set to use a table for both expansion and validation. For example, the PREFIX table provides abbreviated entries and restricts the text to one of the field values (or blank). When using combination tables, be sure to enter both the valid abbreviations and the full field values as codes so that both types of text entries will be accepted (validated).

When setting up tables on the Set up general lookup/validation window to provide lookup choices and validation during data entry for coded fields such as Prefix, you should enter values for Expansion or Description that are different from the specified Code to prevent duplicate values from displaying in your drop-downs. For example, if you enter Dr. as the Code and Dr as the Expansion, you will have a duplicate in your list.

Note: Leave the Expansion values blank for lookup/validation tables that you create unless you are sure that you need expansion. Entering expansion values when they are not necessary might cause problems with some lookup/validation fields in the iMIS interface.

Note: Most lookup and validation fields limit input to values defined in their associated tables. Be sure to define all of the codes that your users will need when you set up lookup/validation tables.

When creating general lookup/validation tables, keep the tables as small as possible to optimize system performance on lookups.

The values or selections defined in a lookup/validation table display in a drop-down list.

Note: In order for Prefix and Suffix data to show up in the Staff site and public-facing websites, the Expansion field must be populated because these are expansion tables. Otherwise, prefix and suffix information only displays in the iMIS Desktop view.

1. In the Advanced Accounting Console, go to Settings > Membership > Set up tables > Lookup/validation tables.
2. In the Lookup/validation tables window, select (New Table) from the list. A window opens and prompts you for the new table name.
3. Enter the exact table name in the window.
4. Click Save to insert the new table into the table list.
5. Populate the new table:
   1. Click New.
   2. Enter a value in the Code field.
   3. If you are creating an expansion table, enter a value in the Expansion field.

   Warning!  

   When creating a validation table, leave the Expansion field blank. Entering a value in the Expansion field of a validation table can prevent iMIS fields tied to the table from working properly, and might prevent iMIS from saving the correct data from fields populated by the table.

   4. Enter a Description. The value entered in this field is displayed in lookup windows and drop down lists.
   5. Click Save.
   6. Repeat until you have entered all of the lookup/validation values.

iMIS allows you to import tab delimited files into a general lookup/validation table. The import process requires that the file be tab delimited and contain a Code, Expansion, and Description.

1. In the Advanced Accounting Console, go to Settings > Membership > Set up tables > Lookup/validation tables.
2. In the Lookup/validation tables window, select (New Table) from the list. A window opens and prompts you for the name of the new table.
3. Enter the name of the new table in the window that displays.
4. Click Save.
5. Select Import to display the Importing Tab Delimited Tables window.
6. Click Continue to display the Select import file window.
7. Navigate to your import file, and click Open.
8. When the import is complete, a dialog box will prompt you to select an additional file.
   • Click No to end the import process.
   • Click Yes to import another tab delimited file.
9. When complete, the Set up lookup/validation window is displayed.

A general lookup/validation table is automatically deleted when no codes exist for the table. Do the following to delete each code you have defined for the table you wish to delete.

1. In the Lookup/validation tables window, select the table to be deleted from the table name list. The table’s code, expansion, and description values are displayed in their respective fields.
2. Delete all of the codes from the table:
   1. Select a Code to delete.
   2. Click Delete. A system message prompts you to verify the deletion of the selected record.
   3. Click Yes.
   4. Repeat until you have deleted all codes from the table.