Custom Lookup Tables and Fields
Millennium includes many opportunities for a site to easily customize its use of the software to fulfill a need that may be unique for that institution. One such opportunity is that each data table, as well as the Chart of Accounts and the Circle Definition lookup tables, includes several custom data fields. The use of these fields is totally at the discretion of the institution.
This set of custom fields includes the following fields and columns:
- up to twelve datetime columns
- up to twelve money columns
- up to twelve numeric columns
- at least 11 Yes/No (check box) columns. The plannedgifts data table includes 13.
- up to six free text fields
- at least 3 fields that are controlled by (that is to say, reference) lookup tables. The chart_of_accounts, corebio, address, attribute, gifts, school, dues, donor, actions, tasks, rating, plannedgifts, event, membership, proposal and attendance data tables include 6.
To accommodate the custom lookup table driven custom fields, Millennium includes lookup tables for each data table, for each custom field, which may be defined and used for any purpose desired by the institution. Your institution may use any (or none) of these custom lookup table fields in any combination with any of the Millennium-defined data columns.
All custom lookup tables conform to the Standard Lookup Table Layout.
The custom lookup tables are named using the following naming convention:
<tablename>_lookup1
<tablename>_lookup2
<tablename>_lookup3
and so on.
The custom lookup table driven fields are not shown on the standard display or data maintenance forms. If your institution chooses to make use of any of these columns, the standard display or data maintenance forms should be edited to include them by using the Custom Display Designer.
There are areas in the software where, if a data row is created for a constituent, an almost identical, reverse data row is automatically created for a second constituent. Creating Screener and Relation rows are two examples of this. Most of the information in the Standard fields in the first constituent's data row is copied to the second constituent's data row. However, the information in the custom fields will not be copied to the second constituent's data row.
In addition to the custom lookup tables, Millennium also includes two custom data tables that may be defined and used for any site-defined purpose. These tables are named custombio and customprospect and they each include seven custom lookup table fields.
Custom Lookup Table Maintenance
Though Lookup Tables are used within many of the worlds or Millennium, the Maintenance functions for the lookup tables are accessed within the Tools World. When the Custom Lookups item is expanded in the Millennium Explorer, you will see the items, Descriptions which leads to this Help topic, and Lookup Tables which leads to the Maintenance of the lookup tables themselves.
Lookup tables are standard SQL 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 in data display, 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.
View Lookup Tables for Data Maintenance
The techniques for viewing and editing the individual rows in a lookup table are nearly identical to those used to view data rows in the Profiles World. Rather than using a Constituent Search form to identify the constituent whose data you want to see, you will used an index listing of all of the Millennium lookup tables. You may scroll through the list or use the index letters at the top of the page to jump to a particular place in the list.
Once a lookup table is identified, each of the rows is shown below a section header that includes the familiar buttons, Insert, Short, Long, Options, and Help.
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 View, 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 via 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.
There are three sort orders that may be applied to the accessible set of lookups. These choices are shown in a form accessed via the Options button in the section header edit button. The bottom of that form shows radio buttons for Value, Code, and 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.
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 Lookup Table Entries
- Display the list of custom lookup tables by using Millennium Explorer, .
- From the resulting index list, click a 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.
- The display frame will show all rows for which your User ID has been given Access.
- To switch between Long and Short display: pass the mouse pointer over the Edit button to the left of the Section Header to access a context menu. Locate and click Display and then activate the display mode you prefer.
- To locate a particular entry: pass the mouse pointer over the Edit button to the left of the Section Header to access a context menu. Locate and click 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:
based on the data in their Code columns, pass the mouse pointer over the Edit button to the left of the Section Header to access a context menu. Locate and click Options. Using the resulting form, click the Code radio button.
OR
based on the data in their Value columns, pass the mouse pointer over the Edit button to the left of the Section Header to access a context menu. Locate and click Options. Using the resulting form, click the Value radio button.
OR
based on the data in their Group (table_group) columns, pass the mouse pointer over the Edit button to the left of the Section Header to access a context menu. Locate and click Options. Using the resulting form, click the Group radio button. Click OK.
Create and Edit 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 Insert (create) button is located in the section header, the Edit (update) button is shown to the left of each row, 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 for use when creating lookup table rows as there are for data tables.
Create a Lookup Table Entry
- To create a new entry in a lookup table, access the display of the desired table. Pass the mouse pointer over the Section Header Edit button to the left of any existing entry to access a context menu. Click Insert. You will be given an Insert form.
- Type the desired text 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.)
- Tab to the Code text box and enter the desired character (up to 6 characters) for the Code. This code may NOT be a duplicate of any other Code in the same lookup table or you will be given an error message.
- Tab to the Active check box. Leave the check in place. (Inactive lookup table rows are not available for use in data maintenance in the Profiles World. They are available in list boxes in the Millennium Reporter.)
- Tab to the Group list box and click the down arrow to display all of the entries in the Table Groups lookup table. If desired, select an entry from that list.
- Tab to the first Access text box. Edit the list as desired. Tab to the second 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 (viewing) to this lookup table row. See the topic, Lookup Table Security.
- Tab to the first Maintenance text box. Edit the list as desired. Tab to the second 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.
- Tab past the Amount 1 and Amount 2 text boxes (these are reserved for future use).
- Tab to the Comment text box and enter any free text data that you want, concerning this lookup row. This may be up to 255 characters.
- Click OK to create the row OR click 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 to the left of the desired row to access a context menu. Click Update/Delete.
- In the resulting update 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 OK to Update the row, OR Cancel to halt the process without updating the row.
Delete a Lookup Table Entry
Note: 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), and could potentially take an extended period of time. 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.
- To delete an existing entry in a lookup table, access the display of the desired table. Pass the mouse pointer over the Edit button to the left of the desired row to access a context menu. Click Update/Delete.
- In the resulting update form, visually verify that you want to delete the row and click Delete.
Lookup Table Security
There are several types of security measures 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 the data row that uses the entry but it may affect the ability to see a particular column's data, if it uses a lookup entry for which the user has not been given access.
For example, a user with a MUG of G might access an Activity row that uses a lookup table entry. If that entry does not include a G in the Access field, the data row will still display but in the place where you would expect to see that lookup entry, you will see the message, "Lookup not found." If that user accesses a data maintenance form for that data table and displays the pull-down list box, that table entry will not appear.
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).