Abila 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:
To accommodate the custom lookup table driven custom fields, Abila 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, Abila 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.
Though Lookup Tables are used within many of the worlds or Abila 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 Abila Millennium of "driving" other fields in rows in data tables, or sometimes in other lookup tables. Usually within the other Worlds of Abila 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.
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 Abila 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 Abila 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.
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.
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
Edit a Lookup Table Entry
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.
There are several types of security measures that are available within Abila 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 Abila 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).