Value-based Lookup Tables in Data Entry and Maintenance
The following information is covered in this topic:
- Introduction
- Value-based Lookup Tables, Fields, and Data Tables
- Value-Based Lookup Field Components
- Wildcards
- Entering Data into a Value-Based Lookup Field
- Using the System Default Six Step Value-based Lookups Matching Process
- Restricting the Value-based Lookups Matching Process
- Value-based Lookups Matching Process
Introduction
On data entry and maintenance forms, the majority of the data fields that are controlled by lookup tables are shown as list boxes. All of the lookup table active entries will be listed in the box, and you will click on the entry that you want. When the data row is saved, the system stores the code associated with your selected entry. When the data is displayed, the system uses the stored code to retrieve its associated value from the lookup table, and then shows that value.
However, there are several data fields controlled
by lookup tables that behave differently. These special lookup-table driven
data fields are controlled by (reference) lookup tables that can have
an exceptionally large number of active entries. On data entry and maintenance
forms, these fields use a Search box for finding a lookup table entry,
and are denoted by a Search button
. When the data row is saved, the system stores
both the code and the value associated with the lookup table entry. When
the data is displayed, the system does not retrieve the value from the
lookup table. It simply retrieves the value that stored in the data row,
and then shows it. These lookup tables are called value-based
lookup tables. The data fields that reference value-based lookup
tables are called value-based lookup fields.
Value-based lookup tables can also called
super lookup tables and value-based lookup fields can be called
super lookup fields.
The system uses different techniques to enter, maintain and display value-based lookup fields because it is not practical to access and display the entire set of lookup table entries every time a data entry or maintenance form is needed. Nor is it practical for the system to ’decode’ entries from these lookup tables for data display purposes.
This topic describes using value-based lookup tables and fields during data entry and maintenance. For information on using the other (standard) lookup tables in data entry and maintenance, see the Data Maintenance topic.
Using value-based lookup tables and fields in Reporting is different. See the Value Based Lookup Tables in Reporting topic.
Value-based Lookup Tables, Fields, and Data Tables
This chart lists the data tables that have value-based lookup fields, the field names, and the value-based (super) lookup tables associated with those fields.
| Data Table | Data Field | Lookup Table |
|---|---|---|
| Attributes | Type Name | Attribute Type |
| Basic Data | Default Account | Chart of Accounts |
| Basic Data | Default Division Name | Campaign Division |
| Circle Member | Circle | Circle Definition |
| Dues | Purpose | Chart of Accounts |
| Dues | Solicitation | Solicitations |
| Dues | Batch | Dues Batch Numbers |
| Education | Institutions | Institutions |
| Giving | Purpose | Chart of Accounts |
| Giving | Solicitation | Solicitations |
| Giving | Batch | Gift Batch Numbers |
| Planned Giving | Restriction Code | Chart of Accounts |
| Planned Giving | Solicitation | Solicitations |
| Proposal | Restriction Code | Chart of Accounts |
| Proposal | Solicitation | Solicitations |
| Ratings | Division Name | Campaign Division |
Display the data dictionary view of the chart above.
| Data Table | Field | Lookup Table |
|---|---|---|
| attribute | attrtype | attribute_types |
| corebio | corerest | chart_of_accounts |
| corebio | coredefdiv | campaign_division |
| circles | circircle | circle_definition |
| dues | duesrest | chart_of_accounts |
| dues | duessolic | solicitations |
| dues | duesbatch | dues_batch_number |
| school | schlinstit | institutions |
| gifts | giftrest | chart_of_accounts |
| gifts | giftsolic | solicitations |
| gifts | giftbatch | gift_batch_number |
| plannedgifts | planrest | chart_of_accounts |
| plannedgifts | plansol | solicitations |
| proposal | proprest | chart_of_accounts |
| proposal | propsol | solicitations |
| ratings | ratecmpdiv | campaign_division |
Value-Based Lookup Field Controls
Value-based lookup fields use a Search box to look for matching value-based lookup table entries. The Search box has the following controls and behaviors.
- The Search button
indicates that it is a value-based lookup field. When clicked, the system looks for a matching lookup table entry.
- Click the Gear button
to bypass the system's default six step Value-based
Lookups Matching Process. whenever the system or
a previously executed search cannot locate the desired, unique matching
lookup table entry, click the Gear button to re-do the search. - The Clear button
displays once the system populates the Search box. Click Clear to reset the box to blank. - If the system's default six step matching process does not find an exact match to a lookup table entry, the Search box expands to display "No results for" + the data that you entered into the field. If the matching process finds more than one lookup table entry, the Search box expands to display all of them.
-
When Gear
is clicked, the box expands (if it is not already), and these additional
controls are available.- The Code option button: when clicked, the system will compare the data that you enter into the value-based lookup box (in other words, your data) to only the lookup table's Code column.
- The Value option button: when clicked, the system will compare your data to only the lookup table's Value column. It is not available on the Giving Batch Numbers or Dues Batch Numbers lookup table-driven fields.
- The Account Number option button: is shown for the Chart of Accounts and Solicitations lookup table-driven fields only. When clicked, the system will compare your data to only the lookup table's Account Number column.
- The Group box is available for the Attribute Types lookup table-driven fields only. If an entry from this box is selected, the system to compare your data to only the lookup table entries within the selected Attribute Group.
-
The Accept 80/20 Quid check box displays for every Chart of Accounts results list. This check box is activated when you choose an entry from the list where the Chart of Accounts 80% Rule flag set to Y (Yes). It is possible that the results set will not include a Chart of Accounts entry that has the 80% Rule flag set to Y.
Review the Help about Chart of Accounts for information about the 80% Rule flag.
- The Retry button: when clicked, initiates the search for matching lookup table entries, the search will use the options that you have selected and the data that you have entered.
- The Close button
cancels the matching process. When clicked, the expanded component of
the box will close.
Wildcards
Wildcards are symbols that the system will recognize as "substitutes" for another set of characters. Wildcards can be used in any value-based (super) lookup field Search box on the Profiles Search, and any value-based (super) lookup field Search box on most Data Maintenance forms. The Wildcards topic discusses this concept in further detail, and provides specific examples.
Entering Data into a Value-Based Lookup Field
A Search button indicates that a data field is a value-based
lookup field. When tabbing to or clicking in a value-based lookup Search
box, leading spaces will be highlighted. This allows you to start typing
in the box without having to remove the spaces first.
Type in data as you normally would for any other box. The % (percent) and _ (underscore) wildcard symbols can be used. You can type in complete or partial lookup table codes or values. For value-based data fields that are driven by the Chart of Accounts or Solicitations lookup tables, you can also type in complete or partial account numbers.
You can leave the Search box blank if the lookup table that drives the data field has a an active blank entry. The field will then be blank in the data row that is created.
Bypass the system's default six step Value-based
Lookups Matching Process by clicking the Gear button before you
click Search or before you advance the cursor (by Tab or mouse).
Important! If you try to insert or update a data row without first identifying a valid value-based lookup table entry (which can be a blank if an active blank entry is present in the lookup table) for a value-based lookup field, you will be given an error message and you will be unable to create the row without correcting the situation.
Using the System Default Six Step Value-based Lookups Matching Process
By default, the system uses all six steps of the Value-based Lookups Matching Process to match the data that you type into the Search box (your data) to active entries in the related value-based lookup table. Review the steps that the system takes to find a match in the Value-based Lookups Matching Process section of this topic.
To utilize the system default process, either click
on Search or advance the cursor (by Tab or mouse) after you type in your
data. The system will attempt to match your data to one of the active
entries in the associated lookup table.
When a step in the process locates a unique entry, the Search box will be populated with the value associated with that lookup table entry. If you are looking for an entry in the Chart of Accounts lookup table, the system could prompt you to accept or decline an 80/20 Quid before populating the box. This behavior is discussed in the Value-based Lookups Matching Process section of this topic.
If a unique entry is not located after all of the steps are complete, then the Search box expands to display either 'No results for' plus your data, or expands to display the list of all of the matching entries. Note that the listed entries can be exact or partial matches to your data.
- If no matching
lookup table entries are located, or the entry that you want is not in
the list of matching entries, click Gear to redo the search. Follow the instructions
in the Restricting the Value-based Lookups Matching
Process section of this topic.
- If the entry that you wanted is listed, select and assign it by completing both of the following steps:
- Click the entry to select it.
- Assign the selected value to the value-based lookup data field by either clicking the entry a second time, or by pressing Tab.
Restricting the Value-based Lookups Matching Process
You may want to place restrictions on the matching process so that it only compares the data that you entered (your data) to the lookup table's Code, or Value, or Account Number column. If you are searching for an Attribute Type Name, you can also limit the search to one Attribute Group.
- Click Gear .
- The Value option is selected by default. The matching process will compare your data with only the lookup table's Value column. Click one of the other option buttons to change the option to Code (to compare your data to the lookup table's Code column), or to Account Number (to compare your data to the lookup table's Account Number column) if it is available.
- If you are searching for an entry in the Attribute Types table and you want to limit the search to one Attribute Group, click the desired Group option from the Group list box.
- Click Retry. The system will use either the code, the value, or the account number Value-based Matching Process steps to locate a unique matching active lookup table entry. Review these steps in the Value-based Lookups Matching Process section below.
- If a unique active lookup table entry is located, the Search box will be populated with the value associated with the matching lookup table entry.
- If a unique entry is not found, then either 'No Results for' plus your data will be displayed, or all of the active entries that match your data will be listed. Note that the listed entries can be exact or partial matches to your data.
- If the entry that you want is listed, select and assign it by completing both of the following steps:
- Click the entry to select it.
- Assign the selected value to the value-based lookup data field by either clicking the entry a second time, or by pressing Tab.
- If no entries
are found or the entry that you want is not in the list of matching entries,
then clear your data and click Gear to redo the search, or click Close
on the expanded part of the field to cancel.
Value-based Lookups Matching Process
This section describes the steps that the system will take to match the data that you enter into the value-based lookup Search box (your data), to active entries in the related value-based lookup table.
The Default Matching Process versus a Restricted Matching Process
The system's default behavior is to proceed through all six steps of this process, stopping only when your data exactly or partially matches just one lookup table entry, or when all steps have been completed. But, if you have placed restrictions on the matching process, then the system will perform only those steps that are relevant to the option (Code, Value, Account Number) that you selected, and bypass the other steps. For example, if you selected the Value option, then the system will use only Step 5 and Step 6. If you are looking for an Attribute Types entry, and you have restricted the search to a specific Attribute Group, then the system will look for a match only within the Attribute Group that you have specified.
Attribute Type Names
Attribute rows can be unlinked, or they can be linked to Basic Data, to Education, to Employment, and to Donor rows. Your site may limit the availability of attribute types so that only certain ones can be used when linking to a particular data table, and other types can be used only when unlinked attributes are created. If your system limits the availability of attribute types in this way, the Value-based Lookups Matching Process will attempt to match your data to only the attribute types that you are allowed to use in your particular data entry/maintenance scenario. For example, if you are linking an attribute to a Donor row, then the search will be restricted to only those attribute types that are available for use when linking attributes to donor rows.
Case-Sensitivity
- When the system compares the data you entered to value-based lookup table Codes or Account Numbers, the comparison is case sensitive.
- When the system compares the data you entered to value-based lookup table Values, then the comparison is not case sensitive.
Chart of Accounts 80% Rule
If the 80% Rule flag on the entry that matches the data you entered is set to Y (Yes), the system will (by default) automatically create an 80/20 Quid record that links to the transaction for you. This also automatically calculates the Deductible Amount of the transaction to be 80% of the amount that you entered. You are given the opportunity to accept or decline this automatic system behavior.
Review the Help about Chart of Accounts, for information about the 80% Rule flag.
Once a matching chart of accounts entry is found:
- The lookup field will expand to show the matching Chart of Accounts entry as well as the Accept 80/20 Quid check box.
- Click the matching lookup table entry to activate the Accept 80/20 Quid check box.
- Uncheck the box if you do not want the system to create the 80/20 Quid for this transaction.
This chart lists the Value-Based Lookups Matching Process steps, what lookup tables a step will apply to, what lookup tables that a step will not apply to, and what happens when a match is found or not found.
| Step # | Step | Lookup Tables this Step Applies to | Lookup Tables this Step Does Not Apply to | What Happens Next: |
|---|---|---|---|---|
| 1 |
Exactly match your data to active codes in the lookup table.
Gift Batch Number and Dues Batch Number (only): Exactly match your data to active, open batch numbers in the lookup table. |
|
N/A |
If your data exactly matches only one code then the matching process is finished at this step, and the value associated with the code populates the Search box and is assigned to the value-based lookup field. If no active code is an exact match, then the matching process continues. Chart of Accounts (only): If the 80% Rule flag for the matching entry is set to Yes, then the Search box will expand to allow you to decline the 80/20 Quid. |
| 2 |
Exactly match your data to active account numbers in the lookup table.
|
|
|
If your data exactly matches only one account number, then the matching process is finished at this step, and the value associated with the account number populates the Search box and is assigned to the value-based lookup field. If no active account number is an exact match, or more than one active account number exactly matches, then the matching process continues. Chart of Accounts (only): If the 80% Rule flag for the matching entry is set to Yes, then the Search box will expand to allow you to decline the 80/20 Quid. |
| 3 |
Partially match your data to active codes in the lookup table.
Gift Batch Number and Dues Batch Number (only): Partially match your data to active, open batch numbers in the lookup table. |
|
N/A |
If your data partially matches only one code, then the matching process is finished at this step, and the value associated with the code populates the Search box and is assigned to the value-based lookup field. If no active code partially matches, or more than one active code partially matches, then the matching process continues. Chart of Accounts (only): If the 80% Rule flag for the matching entry is set to Yes, then the Search box will expand to allow you to decline the 80/20 Quid. |
| 4 | Partially match your data to active account numbers in the lookup table. |
|
|
If your data partially matches only one account number, then the matching process if finished at this step. The value associated with the account number populates the Search box and is assigned to the value-based lookup field. If no active account number is a partial match, or if more than one active account number matches, then the matching process continues. Chart of Accounts (only): If the 80% Rule flag for the matching entry is set to Yes, then the Search box will expand to allow you to decline the 80/20 Quid. |
| 5 |
Exactly match your data to active values in the lookup table.
|
|
|
If your data exactly matches only one value, then the matching process is finished at this step and the value in the Search box is assigned to the value-based lookup field. If no active value is an exact match, or more than one active value exactly matches, then the matching process continues. Chart of Accounts (only): If the 80% Rule flag for the matching entry is set to Yes, then the Search box will expand to allow you to decline the 80/20 Quid. |
| 6 |
Partially match your data to active values in the lookup table.
|
|
|
If your data partially matches only one value, then the matching process is finished, and the complete lookup table value populates the Search box and is assigned to the value-based lookup field. If no active value is a partial match, or if more than one active value partially matches, then the value-based lookup field expands to display controls that will let you further search for the value that you want. Chart of Accounts (only): If the 80% Rule flag for the matching entry is set to Yes, then the Search box will expand to allow you to decline the 80/20 Quid. |