Creating general lookup/validation tables

iMIS enables you to create and add to validation and expansion tables for many fields of the iMIS interface. iMIS comes with built in links between many of the its standard fields, and you can create validation tables for any of your user-defined demographic or list-string fields.

Guidelines for populating tables

When working with lookup/validation and expansion tables, keep the following guidelines in mind:

  • Do not use special characters in the Codes or Expansion fields when creating general lookup/validation tables.
  • The Code field on the Set up general lookup/validation window can hold up to 60 characters. However, the general lookup/validation tables that are used for various fields in iMIS that 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.

Tables that provide validation

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.

Tables that provide expansion

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.

Tables that expand and validate

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-down lists. 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 may 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.

Synchronizing lookup and validation table changes to the web

When you make any changes to the validation and expansion tables through the Desktop or Advanced Accounting Console, you must manually synchronize the information to the web. Any changes you make through the Desktop or Advanced Accounting Console will not be automatically pushed to the web.

To synchronize your lookup and validation table changes to the web, do the following:

  1. In the Staff site, go to Settings > Organization.
  2. Under Synchronize Desktop or Advanced Accounting Console settings changes to the web, click Refresh Cached Settings. This will synchronize any lookup and validation table changes from the Desktop or Advanced Accounting Console to the web.

Note: You can also click Save on the Organization settings page in the Staff site to synchronize Desktop or Advanced Accounting Console system settings changes to the web.

More:
Set up general lookup/validation window