More information

Lookup Tables Maintenance

Lookup Tables are used within many of the worlds of Millennium. The maintenance functions for them are accessed within the Tools World.

When the Tools World item is expanded in the Millennium Explorer, you will see several Lookup Table Maintenance items as well as some system maintenance items. The lookup items are:

Custom Lookups
Event Lookups
Membership Lookups
Profiles Lookups

When any one of these is expanded, you will see the items Descriptions which leads to the Help text for each lookup table in that world, and Lookup Tables which accesses an index listing of the lookup table names, which are used to initiate the maintenance of the lookup tables themselves. The techniques for viewing or maintaining the lookup tables is the same for each set of tables, and are described in this Help topic.

The item in the Explorer, Batch Control, expands to list the specialized lookup tables, Gift Batches and Dues Batches. Clicking either leads directly to the Options form for the corresponding table. Also, the Report Groups item in the Explorer, leads directly to the maintenance for that specialized lookup table.

When you click on the name of any of these sets of tables, the display will show an index list of the tables in that set. The techniques for viewing or maintaining the lookup tables are the same for each set of tables, and are described in this Help topic.

Lookup tables are standard database tables, but with the specialized purpose in Millennium of "driving" other fields in rows in data tables, or sometimes in other lookup tables. Usually within the other Worlds of Millennium, you will only see the Value field from the lookup table rows, but each entry consists of several fields of data.

When data rows are created or edited, the maintenance form includes pull-down list boxes for each of the fields that are driven by a lookup table. When that list is displayed, the Values from the lookup table are shown but when you choose one for that data field, it is the Code that is actually copied from the lookup table and stored as data in the data row.

When that data row is displayed, the system will use that code to "read" the lookup table as it is at that time, and display the Value field from the lookup row. If lookup table rows have been edited (or deleted) since the data row was created, the system still attempts to trace the code back to the current Value in the lookup table.

If the system cannot locate a lookup table entry that matches the code that is stored in the data row, the message, "Lookup not found" followed by the code that is stored in that field. This is true when the lookup has been deleted (although SQL data integrity checking makes this a rarity) and also when the user has not been given Table Security Access to that particular lookup row.

Therefore, care and planning must be used when lookup table entries are to be edited, or deleted.

Most of the lookup tables conform to a standard set of columns. For more information on that layout, and those that differ from the standard, see the topic, Lookup Table Layouts.

For descriptive information on the purpose and required entries in individual lookup tables, see the topics, Profiles Lookup Tables, Event Lookup Tables, or Membership Lookup Tables.

Lookup Tables Listings

The standard techniques for viewing and editing the individual rows in a lookup table are the same for those used in the Profiles World, the Membership Process, the Events World, or the Custom lookup tables. Once you have accessed lookup maintenance for the desired world or set, you will be shown an index listing of all of the lookup tables for that world/set. Scroll through the list or use the index letters at the top of the page to jump to a particular place in the list.

Standard Lookup Tables Display

Once a lookup table is identified, all of its rows are shown below a section header. There are a few exceptions to this standard behavior for accessing the display of a lookup table. See the Exceptions to the Standard Behavior section of this topic.

The Short display is the default view and for most tables, it shows the Value, Code, Group, and Active Flag from each row, delineated by a separator bar. An Edit button is shown to the left. If you switch to the Long display, you will be shown all of the displaying fields for each row. For most tables, this includes the Access and Maintenance Group letters, an Amount 1 and Amount 2 field, and the Last Edit Date and User ID from its last edit.

Security may be applied to the display of the lookup table rows by using the Access field. When the lookup rows are displayed, the system will examine the Millennium User Group (MUG) for the user who is logged in and will only display those lookup table rows where the MUG for the user is present in the Access field in the lookup table row.

Sorting Lookup Table Entries

There are three sort orders that may be applied to the accessible set of lookup entries. These choices are available by using the Options item in the context menus for that table. Sort the lookup table entries by Value, Code, or Group (the table_type field which allows you to classify or group rows within the table, not the MUG). The system will display the lookup rows in ASCII order based on the data in the field that is selected from these buttons.

Find

If the lookup table is very large, you may want to use the Find feature to automatically scroll the display to a particular entry. The Options form shows text boxes for the Value or Code and you may enter complete or partial data for either one. This will display the first row that conforms to the data you entered and all rows that follow in the chosen sort order. From this display, you may scroll "downward" only. To access the display of the rows that would display above the one you specified, you must re-access the Options form or the lookup table index.

View the existing entries in a lookup table

From the Millennium Explorer, expand the Tools World item and then click on either Custom Lookups, Event Lookups, Membership Lookups, or Profiles Lookups. As the item expands in the Explorer, click on Lookup Tables.
From the resulting index list, click on letter for the name of the desired table, or scroll down the page until it is in view. Click the name of the lookup table.
All entries for which your User ID has been given Access will display. (The exceptions to this behavior are the Attribute Types, Chart of Accounts, Institutions, Solicitations and the ZIP Code lookup tables. See the Exceptions to the Standard Behavior section of this topic.)
1. To switch between Long and Short display, using the context menu from the section header Edit button or any of the existing entries, click on Display to access a cascading menu. The name of the current display mode will be disabled. You may switch to the alternate mode by clicking it in the menu.
2. To locate a particular entry, using the context menu from the section header Edit button or any of the existing entries, click on Options. Using the resulting form, type the desired entry's Value OR Code, or partial information. Click OK to access that entry and all that follow it in the designated sort order.
To view the rows in a particular sort order:
1. Pass the mouse pointer over the Edit button to the left of the header or the edit button next to an existing entry to access a context menu.
2. Click the Options item. You will be given the Options form with several options.
3. Click the radio button toward the bottom of the form corresponding to the desired Sort order for the display, either Code, Value, or Group.

TOP

Creating and Editing Lookup Table Entries

The techniques for creating, editing, or deleting lookup table rows are essentially identical to the techniques for doing so in data tables. The context menus provide access to display and editing functions, the maintenance forms display text boxes for free text columns, list boxes for lookup table driven columns, check boxes, etc.

There are no Default Insert forms (defined defaults)for use when creating lookup table rows.

Create a lookup table entry

Access the display of the desired table by using Millennium Explorer > Tools and either Custom Lookups, Event Lookups, Membership Lookups or Profiles Lookups. Pass the mouse pointer over the table name or an edit button for an existing row to access a context menu. Click the Insert item in the menu to access an Insert form.

Alternatively, to access the display of the Attribute Types, Chart of Accounts, Dues Batch Numbers, Gift Batch Numbers, Institutions, and Solicitations lookup tables, you may use the Insert button located to the left of the name of the table.
Enter the desired text (up to 65 characters) in the Value text box. This is the text that will appear in most display situations. Though technically possible, we strongly recommend that no two rows in a single lookup table use the same data in the Value columns.
Enter the desired text (up to 6 characters) for the Code. This code can not be a duplicate of any other Code in the same lookup table or you will be given an error message. Only characters, numbers, and underscores are allowed in the Code field.

See the Exceptions to the Standard Behavior section of this topic for lookup table Code field and Value field exceptions for the following lookup tables: Account CAE, CAE Categories, Campaign Division, Chart of Accounts, Dues Batch Number, Font Names, Gift Batch Number, Titles, ZIP Code.
Leave the check in the Active check box in place.

A lookup table entry flagged inactive (he Active check box is unchecked) will not be available for use in data maintenance. And, inactive lookup table entries will not be available for use on customized data maintenance forms. However, inactive lookup table entries will be available in the Millennium Reporter and Search Screen list boxes.
Locate the Group list box and click on the down arrow to display all of the entries in the Table Groups lookup table. If desired, select an entry from that list.
Locate first Access text box. Edit the list as desired. Tab to the second Access text box and edit it as desired.

By default, these show all Millennium User Group (MUG) letters. You may delete any MUGs that should not have access to view to this lookup table row. See the topic, Lookup Table Security.
Locate first Maintenance text box. Edit the list as desired. Tab to the second Maintenance text box and edit it as desired.

By default, these show all Millennium User Group (MUG) letters. You may delete any MUGs that should not have the ability to edit or delete this lookup table row from the table. See the topic, Lookup Table Security.
Bypass the Amount 1 and Amount 2 text boxes; these are reserved for future use.

See the Exceptions to the Standard Behavior section of this topic for the following lookup tables: Benefit Types, Chart of Accounts, Publication Name, and Solicitations. for exceptions.
Enter any free text data that you want, concerning this lookup row in the Comment text box. This may be up to 255 characters.

See the Exceptions to the Standard Behavior section of this topic for the following lookup tables: Chart Of Accounts, Membership Type.
Click OK to create the row OR click on Cancel to halt the process without creating the row.

Edit a lookup table entry

To edit an existing entry in a lookup table, access the display of the desired table. Pass the mouse pointer over the edit button for that row to access a context menu. Click the Update/Delete item to access a maintenance form.
In the resulting maintenance form, Tab to the desired text box or position the mouse pointer there and click. Use standard data entry techniques to edit existing data, add data, or remove data from any of the fields that you want.
When all data appears as you want, click on OK to Update the row, OR Cancel to halt the process without updating the row.

Delete a lookup table entry

Important! Due to the nature of SQL data integrity checking, when you attempt to delete a lookup table row, the system will have to scan the entire data table(s) that use the lookup table. The amount of time required depends on the size of the data table (s). This is done to verify that the lookup row is not referenced in a data row. If it is in use, you will be given an error message and will not be permitted to delete the row.

Access the display of the desired table. Pass the mouse pointer over the edit button for that row to access a context menu. Click the Update/Delete item to access a maintenance form.
Visually verify that you want to delete the row and click on Delete.

Define a lookup table display

The system administrator can make changes to the appearance of the display of the Lookup data tables by using the Custom Display Designer. Available fields can be added or removed and/or repositioned, and field labels can be changed. When customizing any of the Lookup Table data display and maintenance forms, the Value (table_val) and Code (table_code) columns cannot be removed.

TOP

Exceptions to the Standard Behavior

The following lookup tables behave in ways that are exceptions to the standard behaviors for lookup table maintenance.

Account CAE

The Account CAE maintenance form will accept only one character for the Code field.

Attribute Types

Viewing exception: Because the Attribute Types table has the potential for being quite large, access to the display of this lookup table varies from the standard. The entire lookup table does not automatically display when you identify it from the list of tables. Instead, you will be given the Options form - used to find a particular lookup entry and to specify a sort order for the display. The Options form includes both a Value and a Code text box that you may use to identify an entry to be found. When you enter data (or partial data) in one of those text boxes, the system will return all rows that conform to the data you entered.

The Attribute Types lookup table includes several additional fields in addition to the standard ones that are common to nearly all lookup tables. For complete details on these additional columns, see Attribute Types Layout.

Benefit Type

The Benefits Type maintenance form includes the active field, Amount 1; this field is labeled on the Maintenance form as # of Benefits.

CAE Categories

The CAE Categories maintenance form will accept only two characters for the Code field.

Campaign Division

The Campaign Division lookup table Code field accepts up to 12 characters.

The Campaign Division lookup table Value field accepts up to 120 characters.

Viewing exception: Because the Campaign Division has the potential for being quite large, access to the display of this lookup table varies from the standard. The entire lookup table does not automatically display when you identify it from the list of tables. Instead, you will be given the Select Default Division form - used to find a particular lookup entry and to specify a sort order for the display. The Select Default Division form includes both a Value and a Code selection, along with a search box that you may use to identify an entry to be found. When you enter data (or partial data), the system will return all rows that conform to the data you entered.

Chart of Accounts

The Chart of Accounts lookup table Value field accepts up to 80 characters.

The Chart of Accounts lookup table Comment field accepts up to 4000 characters.

Viewing exception: Because the Chart of Accounts has the potential for being quite large, access to the display of this lookup table varies from the standard. The entire lookup table does not automatically display when you identify it from the list of tables. Instead, you will be given the Options form - used to Find a particular lookup entry (and to specify a sort order for the display). The Options form behaves in the standard way, with the exception that it includes an Account Name, the full Account Number, or a Code text box that you may use to identify an entry to be found. When you enter data (or partial data) in one of those text boxes, the system will return all rows that conform to the data you entered (only).

The Chart of Accounts includes many additional fields in addition to the standard ones that are common to nearly all lookup tables. For complete details on the additional columns, see the Chart of Accounts Layout.

Currency

The Currency lookup table includes one extra field, the Exchange Rate.

Dues Batch Numbers and Gift Batch Numbers

The Dues and Gift Batch Numbers lookup tables have no Code field. In addition, the Value field for these tables accepts up to 10 characters only.

The Dues and Gift Batch Numbers lookup tables are not maintained via standard lookup table maintenance. In order to streamline the ability for operators to create new Batch Numbers and to close existing ones, they are maintained via the Batch Control item in the Millennium Explorer. You will not find these lookup table names in the list of tables in Profiles Lookup Table maintenance.

Font Names

The Font Names lookup table has no Code field.

Institutions

Viewing exception: Because the Institutions table has the potential for being quite large, access to the display of this lookup table varies from the standard. The entire lookup table does not automatically display when you identify it from the list of tables. Instead, you will be given the Options form - used to find a particular lookup entry and to specify a sort order for the display. The Options form includes both a Value and a Code text box that you may use to identify an entry to be found. When you enter data (or partial data) in one of those text boxes, the system will return all rows that conform to the data you entered.

Membership Type

The Membership Type lookup table Comment field accepts up to 4000 characters.

Publication Name

The Publication Name lookup table uses the Amount 1 column; this field is labeled # of Issues on the maintenance form.

Solicitations

Viewing exception: Because the Solicitations table has the potential for being quite large, access to the display of this lookup table varies from the standard. The entire lookup table does not automatically display when you identify it from the list of tables. Instead, you will be given the Options form - used to find a particular lookup entry and to specify a sort order for the display. The Options form includes Solicitation Name, Solicitation Account, and Code text boxes that you may use to identify an entry to be found. When you enter data (or partial data) in one of those text boxes, the system will return all rows that conform to the data you entered.

The Solicitations lookup table includes many additional fields in addition to the standard ones that are common to nearly all lookup tables. For complete details, see the Solicitations Layout.

Titles

The Titles lookup table has no Code field.

ZIP Codes

Viewing exception: Because the ZIP Code lookup table is extremely large (over 43,000 entries!), access to the display of this lookup table varies from the standard. The entire lookup table does not automatically display when you identify it from the list of tables. Instead, you will be given the Options form, used to Find a particular lookup entry (and to specify a sort order for the display). The Options form behaves in the standard way, with the exception that it includes an ZIP Code, the State, or a City text box that you may use to identify an entry to be found. When you enter data (or partial data) in one of those text boxes, the system will return all rows that conform to the data you entered (only). You must enter at least the first three digits of a ZIP Code OR a state and the first letter of a city in order to narrow this search function.

The ZIP Code lookup table includes many additional fields in addition to the standard ones that are common to nearly all lookup tables. For complete details, see the ZIP Codes Layout.

TOP

Batch Control

The Dues and Giving Batch Numbers lookup tables are not accessed for viewing or editing via the standard Lookup Table Maintenance techniques. In order to facilitate the creation of new batches and to close existing ones, the maintenance of these tables is separated under the Batch Control item in the Millennium Explorer.

When the Batch Control item is expanded in the Explorer, you will see the items Dues Batch Numbers and Gift Batch Numbers. Because they have the potential for being quite large, the entire lookup table does not automatically display when you click on one of these table names. Instead, the display frame will show the Gift Batch Number Options or Dues Batch Number Options form. This form contains several elements.

Insert: The Insert button allows the operator to immediately begin the process of creating a new Gift or Dues Batch Number, without first searching the table to verify that the entry does not already exist. When you click on the Insert button, you will be given a form showing text boxes for the data fields in the lookup table.

Report: The Report button accesses the Gift Batch Number Report or Dues Batch Number Report. Each report provides a listing of all transactions in a specified batch to determine if the batch is in balance.

Value: Use the Value box to search for batch numbers. When you type a lookup table value into that text box, the system will return a display of all of the entries that match the data you entered. Therefore, if you type in '10' and there are entries with values of '10', '101', and '10001', they will all be displayed.

Sort: Click Value to sort the batch number search results by the Value field. Click Group to sort the batch number search results by Group.

For more information, see the topic Batch Control.

Lookup Table Security

There are several security levels that are available within Millennium. Much is dependent on the Database Group to which a user is assigned, which in turn consists of any number of SQL Views of the data tables. That set of security functions apply to the data table rows and a user may be permitted or denied access to an entire data row based on his or her Database group assignment. See the topic, User Security.

Table Security is applied in a different manner. Each User ID may be assigned to an Millennium User Group (MUG) which is a one character designation that acts as an identification flag for a set of users. One of the functions of the MUG is to permit or deny access to particular lookup table entries.

Each lookup row includes fields for Access and Maintenance, followed by a set of upper and lower case letters. These represent the MUGs that will be permitted to Access or Maintain that particular lookup table entry. This does not affect the user's ability to access (for display) the data row that uses the entry but it will affect the ability to edit a particular column's data, if it uses a lookup entry for which the user has not been given access. It will also prevent the user from inserting a data row using a lookup entry to which he or she has not been given access.

For example, a user with a MUG of G might access an Address row that uses a lookup table entry. If that entry does not include a G in the Access field, the Address row will still display and it will show that lookup entry. However, if that user accesses a data maintenance form for that Address, the user will not be allowed to edit the data in the field which already contains the restricted lookup entry. Likewise, if that user attempts to enter a new Address row, the insert form's pull-down list for that field will not include the restricted entry.

The Maintenance field in the lookup table controls the user's ability to access and edit the lookup entry within the Tools World's Lookup Table Maintenance function. If a user's MUG does not appear in the Maintenance text boxes for the lookup entry, that user will not be shown that row in the display of the lookup table in the Tools World, and therefore cannot edit or delete them.

Top of Page