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 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 SIC field on the Other tab will always display the Code field values defined in the SIC_CODE general lookup/validation table.