Value-Based Lookup Tables in Reporting

This topic discusses the use of value-based lookup tables and fields in reporting. The use of value-based lookup tables and fields when entering and maintaining data is different. Refer to the Value-based Lookup Tables in Data Entry and Maintenance topic if you are using value-based lookups and fields for data entry (and this includes entering data when performing a constituent search) or data maintenance.

If you are not familiar with writing report criteria statements, review the Understand Report Criteria - Columns, Comparisons, Objects and Write Report Criteria Statements topics before proceeding with this topic

If you are not familiar with concept of value-based lookup tables and value-based lookup fields, then review the Introduction and Value-based Lookup Tables, Fields, and Data Tables sections in the Value-based Lookup Tables in Data Entry and Maintenance topic before proceeding with this topic.

The following information is covered in this topic:

• Introduction
• Search Lookup form Components
• Using the Search Lookup form
• Value-based Lookups Matching Process

Introduction

A series of steps are followed when writing a report criteria statement. You must identify the data table and column within that table, choose a comparison, and then specify an object. The type of object that you specify depends on the data table column that you are using. When a column stores value-based lookup table data, then the Object will be an entry or entries from a value-based lookup table. You will enter data into a text box that functions like a Search box in order to locate the lookup table entry or entries that you want. The system will then use a matching process in order to identify a unique lookup table entry that matches the data that you entered (your data). The steps that the system takes in order to find a matching entry are listed in the Value-based Lookups Matching Process section below.

If the system locates a unique match (either exact or partial) for your data, then the box will be populated with the value that is associated with the matching value-based lookup table entry. If no lookup table entry matches your data, or if more than one entry matches your data, then the system displays the Search Lookup form to help you to locate the entry or entries that you want.

Top of Page

Search Lookup Form Components

The Search Lookup form contains the components described below, as they are shown from top to bottom in the form.

• Text box - When the form is first presented, at the top you will see a box showing the data that you entered. As you use the form, you may enter new data in this text box, edit the existing information, or you may click on an item in the list to move it up to this text box.
• List display box - Below the text box, you will see a list of the entries from the lookup table that match the information that you entered. To select one, you must click on an entry to place it into the Text box above.
• Option buttons - Below the list, you will see option buttons for the Code, Value, and (for Chart of Accounts or Solicitations only) the Account. Click one of those buttons to instruct the system to compare the data you entered to a specific field within the lookup table in a 'Retry' situation. Gift and Dues Batch Numbers have only a Code, and therefore those applets will not display a radio button for Value.
• Accept 80/20 Quid - If the form is showing Chart of Account information in either a Gift or Dues maintenance situation, you will see a check box labeled Accept 80/20 Quid. If you select an Account entry that has the 80% Rule field flagged as Yes, then this check box is active and it allows you to accept or decline the automatic creation and application of a Quid Pro Quo row to the transaction.
• Search in Attribute Group - If the form is showing Attribute information, you will see a list box labeled Search in Attribute Group. This allows you to restrict your search to active Attribute Type entries with a specified Group, or to perform the search on all active entries in the table (All Attribute Groups). This restriction will be in addition to the restriction that ensures that only the entries which have the appropriate table-use flag will be considered. (Attribute Types may be flagged for exclusive use as independent data rows, as linked rows to Basic Data, Education, Employment, or Tracking, or for any combination of those uses.)
• Action buttons - Finally, you will see a set of action buttons, labeled OK, Retry, and Cancel. If the data you entered did not produce the desired entry, you may enter additional data in the text box and click Retry. Similarly, you may click a different radio button to have the system compare your data to a different field in the lookup table and then use the Retry button. When you have the desired entry showing in the text box at the top, you should click on the OK button to close the form and place the entry in the main data maintenance form. You may use the Cancel button to close the form without selecting an entry but you should be aware that if you do so, the system will return you to a blank Insert form, or an Update form that shows the current data (edits that you may have made in either form will not be preserved).

You can use keystrokes to navigate through this form instead of using the mouse:

• Use the tab key to place the focus on the text box, the option radio buttons, or the action buttons.
• Use the arrow keys to scroll through the list of possible matches.
• Use the space bar to activate one of the radio option buttons once it has the focus.

Top of Page

Using the Search Lookup Form

If the system matching process does not identify a unique lookup table entry, a Search Lookup form displays to help you to locate the lookup table entry or entries that you want.

If the data you entered matches multiple entries by code, or multiple entries by value, or multiple entries by account number (for the Chart of Accounts or Solicitations):

• The Lookup form is displayed, showing the data you entered in the text box. You may use this form to examine all matching entries according to Code or Value (or for Chart of Accounts or Solicitations, the Account Number.)
• The radio buttons below the list area allow you to choose which of those you want to examine. By default, the form will display all Values which are a partial match for the data you entered.
• If you prefer to focus the search using the Code or Acct Number instead of the Value, you may activate the desired radio button and click Retry to display the entries that match the data.
• When the list is displayed you may scroll to locate the desired entry or use the keyboard arrow keys to select one. It will then be shown in the text box at the top of the form. You must click on OK to accept that entry and return to the original data maintenance form, or you may simply double click on an entry in the list.
• If you are searching the Attribute Types table (only), this form includes the ability to restrict the search to entries with specific data in the Group field, or to search entries of all Groups.

If the data you entered does not match any entries:

• The Lookup form is displayed, showing the data you entered in the text box at the top. The list box will display the message, "No records located".
• Once the form has been presented and you plan to Retry the matching process with different data, you must specify which of the fields you want to match, the Code, the Value, or (for the Chart of Accounts or Solicitations) the Account Number. At this stage of the process, the system will only examine the specified field. When the desired radio button is active and the data has been edited, click on the Retry button to perform a new search. If that does not produce the desired entry, you may click on a different radio button and Retry the search using a different field.
• Once the desired entry is shown in the list box in the form, you must click on it to highlight it and move it to the text box, and then click on OK to accept that entry. You will return to the original data maintenance form where your selected entry will be shown.

If you click on Cancel in the lookup form or close the mini-browser using the 'X' button in the upper right corner of the window, the form will be removed.

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 a value-based lookup text box (your data) to entries in the related value-based lookup table.

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.

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 codes in the lookup table.	Attribute Types Campaign Division Chart of Accounts Circle Definition Dues Batch Number Gift Batch Number Institutions Solicitations	N/A	If your data matches only one code then the matching process is finished at this step, and the value associated with the code populates the lookup text box. If no active code is an exact match, then the matching process continues.
2	Exactly match your data to account numbers in the lookup table.	Chart of Accounts Solicitations	Attribute Types Campaign Division Circle Definition Dues Batch Number Gift Batch Number Institutions	If your data matches only one account number, then the matching process is finished at this step, and the value associated with the account number populates the lookup text box. If no active account number is an exact match, or more than one active account number exactly matches, then the matching process continues.
3	Partially match your data to codes in the lookup table	Attribute Types Campaign Division Chart of Accounts Circle Definition Dues Batch Number Gift Batch Number Institutions Solicitations	N/A	If your data matches only one code, then the matching process is finished at this step, and the value associated with the code populates the lookup text box. If no active code partially matches, or more than one active code partially matches, then the matching process continues.
4	Partially match your data to account numbers in the lookup table.	Chart of Accounts Solicitations	Attribute Types Campaign Division Circle Definition Dues Batch Number Gift Batch Number Institutions	If your data matches only one account number, then the matching process if finished at this step. The value associated with the account number populates the lookup text box. If no active account number is a partial match, or if more than one active account number matches, then the matching process continues.
5	Exactly match your data to active values in the lookup table.	Attribute Types Campaign Division Chart of Accounts Circle Definition Institutions Solicitations	Dues Batch Number Gift Batch Number	If your data matches only one value, then the matching process is finished at this step. If no active value is an exact match, or more than one active value exactly matches, then the matching process continues.
6	Partially match your data to values in the lookup table.	Attribute Types Campaign Division Chart of Accounts Circle Definition Institutions Solicitations	Dues Batch Number Gift Batch Number	If your data matches only one value, then the matching process is finished, and the complete lookup table value populates the lookup text box. If no active value is a partial match, or if more than one active value partially matches, then a Lookup Search form displays to help you locate the lookup table entry that you want.

Top of Page