Add Correspondence Utility
The Add Correspondence Utility allows you create or update Correspondence data rows en masse for each constituent identified in a Generic worktable. Those rows may be independent rows in the Correspondence table, or they may be linked to another data row. They may be linked to a Gift or Dues row belonging to the same constituent, OR they may be linked to one of the Event or Activity rows within the Events World.
To use the utility, you must first use the Millennium Reporter to create a set of Profile criteria that select the constituents for whom the Correspondence rows will be created or updated. They will be identified by the ID field in the resulting Generic worktable.
- If the rows are to be linked to a Giving or Dues rows, the same set of criteria must be constructed so as to identify those rows in the timekey field of the Generic export worktable. The criteria must also specify the Add Correspondence Utility in order to launch the utility when the criteria runs.
- If the rows are to be independent Correspondence rows, the criteria may be constructed using any data table as the key table, as long as the Generic export worktable identifies the ID number of the constituents for whom the Correspondence rows will be created. The criteria must also specify the Add Correspondence Utility in order to launch the utility when the criteria runs.
- If the rows will be linked to an Event or Activity data row, the criteria may be constructed using any data table as the key table, as long as the Generic export worktable identifies the ID number of the constituents for whom the Correspondence rows will be created. The criteria must also specify the Add Correspondence Utility in order to launch the utility when the criteria runs.
In all of the cases described above, when the criteria is run and the utility is launched, you will be prompted to create a prototype definition of the Correspondence rows to be created or to be updated. All Correspondence rows that are created or edited by that run of the utility will conform to the information that you enter as the prototype.
For example, you might want to segment your constituency and send different letters of solicitation to each segment. To track that information, you may create a set of criteria to select one segment based on any data in the database. By using the Add Correspondence Utility rather than a format you can create independent Correspondence rows for every constituent identified by the ID number field in the worktable. This can be accomplished with a single run of criteria.
Alternately, you might want to track the details on the Correspondence that a constituent is sent in response to a Gift or Dues transaction. In that case, you would create the criteria to select the desired transactions. They will be identified by the Timekey field in the worktable and as a natural consequence, the ID number field will identify the constituent. The Add Correspondence Utility is launched when the criteria is run and linked Correspondence rows are created. This can also be accomplished with the single run of the criteria.
Or you might want to track the invitations that are sent to constituents for a particular Event or Activity. You might use the same set of criteria statements that were used to produce the labels for the mailing, but instead of the format, specify the utility. When the report is run, the criteria statements will select and identify the constituents in a generic worktable, and the utility will be launched to create the Correspondence rows. The utility prompts you to specify whether the Correspondence will be linked to the selected rows or an Event or Activity. When you choose the Event or Activity, the system will again prompt you to select the name of the Event or Activity from a list box. When used for this purpose, the utility may only be used to insert new Correspondence rows, not to update existing ones.
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.
Running the Utility
The process of running this utility consists of two basic steps:
- Create the Profiles criteria to select the desired transactions and/or the desired constituents.
- Run the criteria, specifying the utility as the output destination. When prompted, enter the desired utility options and Correspondence prototype for use by the utility.
The definition of the criteria used to create the export worktable is an important factor in the successful use of this utility. 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.
Accessing the Utility
The use of this utility is completely integrated with the function of the Millennium Reporter, and is only launched via the run of a set of criteria. 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 Correspondence Utility.
Report Criteria
Key Table
Criteria must be defined in accordance with the utility options you intend to use. If you plan to create independent Correspondences (not linked to another data row), or those linked to an Event or Activity, then the report criteria may use any data table as the key table. If you plan to create Correspondences that are linked to a Giving or Dues data row, then the report criteria must use the desired table 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 a Correspondence for each row in the export worktable, even if they belong to the same constituent. Likewise, you must understand that linked Correspondence rows will only be considered for possible updating if the row to which they are linked remains in the worktable.
If you clear the Allow Multiples option, the utility will create or update one and only one Correspondence for each selected constituent. If a new Correspondence is to be linked, it will be linked to the one data row that remains after multiples have been removed. Updates will only be performed on Correspondences that are linked to the remaining row in the worktable.
Note: 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 select the Allow Multiples option, then a Correspondence will be created once for every row that appears in the worktable. If one constituent owns X number of rows in the worktable, then X number of Correspondence rows will be created by the utility. If the correspondence rows are to be linked, one will be linked to each row selected by the criteria. If the Correspondence rows are to be updated, then every correspondence row that is linked to each of the selected primary rows will be examined as possible matching rows to be updated.
You will want to set the Spouse Name option to Do not combine and 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. If you have other needs, you may alter these settings accordingly.
On the Options>Report form, set the Address radio button to None to optimize the run time of the criteria.
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 Correspondence item from the list.
Run Options
To optimize the run time of the criteria, you should set the Format Names option to NO.
Utility Options and Prototype
When the final 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 Correspondence row you want to create or edit 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 Correspondence table and will also allow you to set your preferred options for the run
Correspondence Prototype
The utility form will show list boxes for the Correspondence Name, Type, Department, Response Type, Response Method, Source columns. It will also show a text box for the Sent ID (the ID number of the person who sent the Correspondence) and a calendar and text boxes for the Sent Date and Response Date. You will use standard data entry techniques to fill in as many of those fields as you want for the new Correspondence 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 Correspondence of the same Type is found, any fields that are blank in the utility prototype will be updated to 'blank' in the existing Correspondence that is being updated. Comments in the existing row will not be overwritten.
Utility Options
Link
The following link options are available.
- Toward the middle of the Utility form, you will see an item labeled Link? followed by Yes and No buttons.
- If you set this option to No and you are using an Insert mode, the utility will create new Correspondence rows as independent rows, belonging to the constituents identified in the worktable. You may use any data table as the key table in the selection criteria.
- If you set this option to No and you are using an Update mode, the utility will examine only independent Correspondence rows belonging to the constituents identified in the worktable, as it attempts to match an existing Correspondence to update. You may use any data table as the key table in the selection criteria.
- If you set this option to Yes and you are using an Insert mode, the utility will next prompt you to specify whether the Correspondence rows are to be linked to the selected rows (must be either Giving or Dues) or to an Event or Activity. If you specify Event, the system will next prompt with a list of the existing Event names and you must choose the desired one. If you specify Activity, the system will next prompt with a list of the existing Activity names and you must chose the desired one. If you specify the selected record, the system will automatically create the new Correspondence rows as linked rows, belonging to the exact data rows identified in the worktable by the key table and timekey field.
- If you set this option to Yes and you are using an Update mode, the utility will examine only linked Correspondence rows belonging to the primary data rows identified in the worktable by the keytableno and timekey field, as it attempts to match an existing Correspondence to update. (This function is not available for Correspondence rows linked to Events or Activities.)
Mode
Below the Link option, you will see an item labeled Mode? This allows you to specify the manner in which the Correspondence information is integrated into the existing database rows.
- If you choose Insert Only, the utility will create a new Correspondence row for each constituent identified in the worktable. (This option must be used when creating Correspondence rows linked to Events or Activities.)
- If you choose Insert/Update, then the utility will examine all Correspondence rows belonging to each constituent identified in the worktable, and attempt to locate an existing Correspondence of the same Correspondence Type and the same Link option you have activated in the prototype you defined. 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.
- If you choose Update Only, then the utility will examine all Correspondence rows belonging to each constituent identified in the worktable, and attempt to locate an existing one of the same Correspondence Type and the same Link option that you have activated in the utility. 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 system will update only those Correspondence data rows that are identified in the worktable as the selected rows.
Using the Add Correspondence 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. There are different steps involved in using this utility depending on the function you want to perform. The first set of instructions apply when the Correspondence row is not linked to any other data row, or is linked to a Giving or Dues row. The second set of instructions apply when the Correspondence row is linked to an Event or Activity row.
Correspondence rows that are independent or linked to a Gift or Dues row.
- 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 Profile, or via one of the Profiles report library templates.
- 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 Correspondences.
-
If you want to create independent Correspondence rows, you may use any data table as the key table, and any set of criteria statements you want to define those constituents.
OR
If you want to create Correspondence rows linked to Giving or Dues, you must use the data table to which the Correspondences will be linked as the key table in the set of criteria. The criteria must be constructed to select the exact data rows to which the Correspondence will be linked.
- From 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 Correspondence Utility.
- 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. If the export is to be saved (your option in this situation), you will be asked to name that file.
- Next 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 Correspondence Name, Type, Department, Sent ID, Response Type, Response Method, and Source. Also, further down on the form, use the calendar or type in the dates for the Sent Date and Response Date. The data that you enter for these fields will be used for every Correspondence 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.
- If it is available, choose Yes or No in response to the Link Correspondences question by clicking the desired radio button.
- Choose the mode for the run of the utility by clicking Insert, Insert/Update, or Update Only.
- 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.
Correspondence that is linked to an Event or Activity row
- 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 Profile, or via one of the Profiles report library templates.
- 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 Correspondences.
- Define a set of criteria that will select the desired constituents. You may use any data table as the key table, and any set of criteria statements you want to define those constituents.
- From 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 Correspondence Utility.
- 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 to launch the criteria and utility.
- Next 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 Correspondence Name, Type, Department, Sent ID, Response Type, Response Method, and Source. Also, further down on the form, use the calendar or type in the dates for the Sent Date and Response Date. The data that you enter for these fields will be used for every Correspondence row that is created or updated. You may leave data fields blank if you want.
- Click the Link? Yes radio button.
- Choose the mode for the run of the utility by clicking Insert (the only mode available when linking to an Event or Activity).
- Click OK to start the run of the report and the launch of the utility.