Open topic with navigation

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.

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

Top of Page

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.

Top of Page

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.

Top of Page

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.

Top of Page

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.

1. 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.
2. If the entry that you wanted is listed, select and assign it by completing both of the following steps:
1. Click the entry to select it.
2. Assign the selected value to the value-based lookup data field by either clicking the entry a second time, or by pressing Tab.

Top of Page

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.

1. Click Gear .
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.
7. If the entry that you want is listed, select and assign it by completing both of the following steps:
1. Click the entry to select it.
2. Assign the selected value to the value-based lookup data field by either clicking the entry a second time, or by pressing Tab.
8. 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.

Top of Page

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:

1. The lookup field will expand to show the matching Chart of Accounts entry as well as the Accept 80/20 Quid check box.
2. Click the matching lookup table entry to activate the Accept 80/20 Quid check box.
3. 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.

Top of Page