Add Quid Pro Quo Utility
The Add Quid Pro Quo Utility allows you create or update data rows en masse for each Gift or Dues row identified in a Generic worktable. Quid Pro Quo rows may only be created as secondary rows, linked to a Gift or Dues row.
To use the utility, you must first create the criteria to select the transactions for which the Quid Pro Quo rows will be created or updated. For the run of the criteria, the Add Quid Pro Quo utility should be chosen (rather than the name of a report format.) When the criteria is run, you will be prompted to create the prototype definition of the Quid Pro Quo rows to be created. All Quid Pro Quo rows that are created by that run of the utility will conform to the information that is entered as the prototype.
For example, you might want to identify the set of constituents whose total giving to the Annual Fund qualifies them for a particular Quid Pro Quo item. Those transactions could be identified and totaled in the criteria and then a Quid Pro Quo row can be attached to one of the selected transactions, bypassing standard data entry. In this situation, you would want to use the report option removing multiples from the export work table so that only one Quid Pro Quo item is attached for each constituent.
Utility Classification
This utility may be used to create new data rows or to perform a mass update of existing data and therefore it is classified as an advanced User Utility.
Caution! By its nature, the Update mode of this utility will overwrite existing data. If improperly used, it could damage the integrity of your data. In addition, there are many variables in the use of this utility (link, mode, reporting options) that must be grasped, in order to ensure that you will produce the desired results. This utility is simple to use, but its ramifications must be fully understood.
Add Quid Pro Quo Utility and Serial Receipts
If your site is utilizing Serialized Tax Receipting then special considerations apply.
The utility will not link new quid pro quo (QPQ) records to pledge payments or to bequests that pay down expectancies.
The utility will not update existing QPQ records that are either linked to pledge payments or linked to bequests that pay down expectancies.
These special considerations apply only if your site utilizes Serialized Tax Receipting.
See the Serial Receipts topic for complete information about Serialized Tax Receipting.
Running the Utility
Accessing the Utility
The use of this utility is very closely tied to the function of the Millennium Reporter. The utility cannot be accessed via in the Tools World item in the Millennium Explorer as some other utilities may be. It is only accessible via the Millennium Reporter. When a report form is accessed, the Output List box may show either the format names in a designated library, or a list of utilities that may be used with the export worktable that is generated by the run of the criteria. This output list is the means of accessing the Add Quid Pro Quo Utility.
There are two general steps in running this utility. First is the construction of the report criteria and options that will select the desired constituent rows, and second is the defining of the utility options and Quid Pro Quo prototype for use by the utility.
Report Criteria
Perhaps the most important aspect of using the Add Quid Pro Quo utility is the definition of the criteria used to create the export worktable. For complete information, you may find it helpful to review the important concepts of criteria organization and the key table in reporting, and the layout of a generic export worktable. A description of the impact of these functions in the utility is given below.
Key Table
Criteria must be defined in accordance with the utility options you intend to use. If you plan to create or update Quid Pro Quo rows that are linked to a Gift data row, then the report criteria must use the Giving table as the key table, or if they are to be linked to a Dues row, then Dues must be the key table. If you plan to use the option to update only the selected Quid Pro Quo rows, then you must use it as the key table.
Report Options
Consideration must be given to the use of the Allow Multiples reporting option in the criteria definition. The decision must be made with the understanding that the utility will attempt to create or update an Quid Pro Quo for each row in the export worktable, even if they belong to the same constituent. Likewise, you must understand that linked Quid Pro Quo rows will only be considered for possible updating if the row to which they are linked remains in the work table.
- If you select the Allow Multiples option, the utility will create or update one and only one Quid Pro Quo for each selected constituent. If a new Quid Pro Quo is to be created, it will be linked to the one data row that remains after multiples have been removed. Updates will only be performed on Quid Pro Quo rows that are linked to the one remaining row in the work table. Note that it is not possible to control or predict which of the selected rows will be removed and which will be retained in the worktable by this function.
- If you clear the Allow Multiples option, then a Quid Pro Quo will be created once for every row that appears in the worktable. If one constituent owns multiple rows in the worktable, that number of Quid Pro Quo rows will be created by the utility, linked to each of the transactions. If the Quid Pro Quo rows are to be updated, then all that are linked to any of the selected primary rows will be examined as possible matching rows to be updated.
The Spouse Name option should probably be set to Do not combine (which will by default set the Remove Spouses option to NO). This will ensure that each spouse's data will be considered independently for the action of the utility.
On the form, you should set the Address radio button to None, to optimize the run time of the criteria.
Active must be set to N on the Quid 80/20 entry.
Output List
To the right of the Report Name text box in the report form, you will see an Output List. This list box may display the names of report formats or it may display the names of Millennium utilities. To switch from one to the other, you must use the Options, Report buttons to access that form. Then you can use the Output List radio buttons on that form to specify that you want to see the names of the utilities. When you return to the report form, you should choose the Add Quid Pro Quo item from the list.
Run Options
To optimize the run time of the criteria, you should set the Format Names option to NO.
It is not necessary to the action of the utility to save the Export worktable, though you may choose to do so for verification purposes, following the run of the utility.
Utility Options and Prototype
When the report criteria and reporting options are set and run, the system will automatically produce a Utility form, prior to sending the report to the Queue. This form is used to define the prototype of the Quid Pro Quo row you want to create for the constituents identified in the worktable and also to set the utility options. The form will show a modified version of the Insert form for the Quid Pro Quo table and will also allow you to set your preferred options for the run
Quid Pro Quo Prototype
The utility form will show list boxes, text boxes or check boxes for the Quid Pro Quo Type, Value, Source, and Accepted indicator. In addition, toward the bottom of the form, you will see a calendar and a text box which you may use to specify an Effective Date for the Quid Pro Quo row. You will use standard data entry techniques to fill in as many of those fields as you want for the new Quid Pro Quo rows. Note that there is no provision for including a Comment in the new or updated rows.
Caution! While it is perfectly acceptable to leave fields blank in the utility prototype, you must do so with the following understanding. If the utility is run in one of the Update modes and an existing Quid Pro Quo of the same Type is found, any fields that are blank in the utility prototype will be updated to 'blank' in the existing Quid Pro Quo that is being updated. Comments in the existing row will not be overwritten.
Utility Options
Mode
Toward the middle of the Utility form, you will see an item labeled Mode. This allows you to specify the manner in which the Quid Pro Quo information is integrated into the existing database rows.
- If you choose Insert Only, the utility will create a new Quid Pro Quo row for each Gift or Dues row identified in the worktable.
- If you choose Insert/Update, then the utility will attempt to locate an existing Quid Pro Quo of the same Type you have activated in the prototype you defined, and which is linked to the Gift or Dues rows identified in the work table. If one is found, the data in the existing row is updated with the data in the prototype. If none is found, the utility will create a new one according to the prototype definition. Note that when the utility is updating existing rows, that any blank fields in the prototype will overwrite existing data in the row that is being updated.
- If you choose Update Only, then the system will look for an existing Quid of the same Type that is linked to the Gift or Dues rows identified in the work table. If one is found, it will be updated with the data in the prototype definition, but if none is found, no new one is created.
- If you choose Update Selected, then the utility will update the Quid rows that are identified in the worktable and therefore, to use this option, the selection criteria must use the Quid Pro Quo table as the key table.
Using the Add Quid Pro Quo Utility
The following instructions assume that you are familiar with the use of the Millennium Reporter. For more information about any procedure described in the following steps, use the hyperlinks to read more on that subject.
- Use the Millennium Explorer to navigate to the Millennium Reporter and access a Report Form via the item New Criteria, and then the item Custom, or via one of the report library templates.
- In the Report Form, click Options, Report, and then the Output List button, Utilities radio button. Also shown on that page, click the Address option, None. Click OK. You will be returned to the Report Form, and the Formats/Utilities list should now show the utility names
- In the Report Form, from the Formats/Utilities list, choose the item, Add Quid Pro Quo Utility.
- Create a name for your report and type it into the Report Name text box. The report name can be up to 50 characters in length. Avoid using punctuation, except for the underscore character, in the report name.
- Set up the Report Options (spouse names, multiples, deceased) in accordance your intentions for the creation of the Quid Pro Quo rows.
- Create criteria statements that will identify the transactions that should be considered for the creation or updating of linked Quid Pro Quo rows. You must use the data table to which the Quid Pro Quo rows will be linked as the key table in the set of criteria.
- When all criteria are defined and the options set as desired, click the File button, then Save, and then Run the report.
- You will be given a Run Options form. Select the Queue you want to use for the run of the report and all other run options you want. You should choose No for Format Names question to improve performance. When all options are set as desired, click OK. The system will present a Utility Options form.
- In the utility option form, use standard data entry techniques to enter the prototype information. These include a Quid Pro Quo Type, Value, Source, and whether or not the Quid was accepted. Also, further down on the form, use the calendar or type in the dates for the Effective Date. The data that you enter for these fields will be used for every Quid Pro Quo row that is created or updated. You may leave data fields blank if you want, but if the utility is to update existing rows, the Type field must be used for matching purposes.
- Choose the mode for the run of the utility by clicking Insert, Insert/Update, Update Only, or Update Selected.
- Click OK to start the run of the report and the launch of the utility. You should be given a message that the report has been successfully added to the Queue.