Creating lookup/validation tables
iMIS enables you to create and add to validation and expansion tables for many fields on the iMIS interface. iMIS comes with built in links between many of its standard fields, and you can create validation tables for any of your user-defined demographic or list-string fields.
When working with lookup/validation and expansion tables, keep the following guidelines in mind:
- Do not use special characters in the Code or Expansion fields when creating general lookup/validation tables.
- The Code field on the Lookup/validation tables window can hold up to 60 characters. However, the general lookup/validation tables used for various fields in iMIS have varying field length requirements. Before adding Code values to a general lookup/validation table, you should determine the maximum length of the field to which the general lookup/validation code is connected. If you add a Code that is longer than the field length requirement for the field to which it connects, the data will be truncated, or in the case of certain tables, the value will not be shown as an available option.
- If the length of the Code field for the following general lookup/validation tables is greater than the defined field length, the value will not display in the drop-down as an available option:
- CATEGORY
- MAIL_CODE
- MEMBER_STATUS
- SIC_CODE
- STATUS
Note: The easiest way to find field length is through SQL Server.
- The value that is displayed in a drop down list or in a select window is determined in the following way:
- If a Description is defined, the Description is displayed.
- If no Description is defined, but an Expansion is defined, the Expansion is displayed.
- If neither a Description nor an Expansion are defined, the Code is displayed.
- The value that is saved after a user selects a value from a drop down list or select window is determined as follows:
- If the value in the general lookup/validation table has an Expansion value, the Expansion value is saved.
- If the value in the general lookup/validation table does not have an Expansion value, the Code value is saved.
- The Description value from the general lookup/validation table is never saved as the result of a selection from a drop down list.
- The SIC field on the Other tab will always display the Code field values defined in the SIC_CODE general lookup/validation table.
The value that is saved is the value that is displayed in the window that calls the drop down list or select window.
The following are exceptions to the values that will display in a drop-down list:
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 toMr.
- Entering
p
in the Title field expands toPresident
- Entering
vp
expands toVice 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.
- In the Advanced Accounting Console, go to Settings > Membership > Set up tables > Lookup/validation tables.
- In the Lookup/validation tables window, select (New Table) from the list. A window opens and prompts you for the new table name.
- Enter the exact table name in the window.
- Click Save to insert the new table into the table list.
- Populate the new table:
- Click New.
- Enter a value in the Code field.
- If you are creating an expansion table, enter a value in the Expansion field.
- Enter a Description. The value entered in this field is displayed in lookup windows and drop down lists.
- Click Save.
- Repeat until you have entered all of the lookup/validation values.
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.
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.
- In the Advanced Accounting Console, go to Settings > Membership > Set up tables > Lookup/validation tables.
- 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.
- Enter the name of the new table in the window that displays.
- Click Save.
- Select Import to display the Importing Tab Delimited Tables window.
- Click Continue to display the Select import file window.
- Navigate to your import file, and click Open.
- 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.
- 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.
- 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.
- Delete all of the codes from the table:
- Select a Code to delete.
- Click Delete. A system message prompts you to verify the deletion of the selected record.
- Click Yes.
- Repeat until you have deleted all codes from the table.