Correspondence
The Correspondence table is used to track details concerning any written correspondence, either from your institution to the constituent or vice versa. Correspondence rows may be used in different ways:
- Rows in this table may behave as secondary rows attached to a specific row in the Gift or Dues Transaction table. In this situation, the Correspondence row is intended to store information about the acknowledgment letter that was sent in response to that giving transaction (though you might also use it for other correspondence). A Correspondence row linked to a Gift or Dues transaction, then, will only display with the Gift or Dues transaction to which it is linked.
- Rows in this table may also behave as primary (independent) rows that display with the Correspondence table only. In this situation, the row would be used to store information about any kind of communication between the constituent and the institution. This might be letters of solicitation, invitations, newsletters, renewal notices, or any other communication.
- Rows in this table may behave as secondary rows attached to a specific row in the Event or Activity tables. In this circumstance, the Correspondence row is intended to store information about the invitation that was sent for a particular Event or Activity. A constituent's Event Correspondence rows will display in the Profiles World, within the Event Log under the Event Correspondence heading only, but not in the Profiles, Correspondence display. A constituent's Event Correspondence rows can also be accessed in the Events World, via a Correspondence Search within a particular Event or Activity.
In any of the above situations, you might want to link the Correspondence row to a copy of the piece of correspondence that was sent. If the correspondence originated with the constituent, then a scanned copy or a text summary of the contents might be attached to the Correspondence row instead. See Attachments for further details.
The Correspondence row uses a Correspondence Name from the correspondence_name lookup table to identify a mailing (do not confuse this with the name of the person at your institution who is sending the correspondence to the constituent). The correspondence_name lookup table also allows you to associate a mailing piece with a particular Solicitation.
The Correspondence row may use a Correspondence Type from the correspondence_type lookup table to categorize the kinds of correspondence that are being tracked.
Example
Your institution is holding an Spring Open House and would like to contact all constituents who reside within 50 miles to invite them to attend. The correspondence_type lookup table might have an entry called 'Invitation' and the correspondence_name table might have an entry called 'Spring Open House 2008 Invitation Letter'. When entering a correspondence row for a constituent, you would select 'Spring Open House 2008 Invitation Letter' from the Correspondence Name drop-list box, and then select 'Invitation' from the Type drop-list box.
The Correspondence data row also provides the ability to track the ID (if the sender is an Millennium constituent), Name and Department of the person sending the correspondence, a Description (using the Comment column) and the Date on which it was sent.
Individual Correspondence rows may be created in the Profiles World as stand alone table rows (refer to the Data Maintenance topic for the 'how-to'), as rows linked to a Gift or Dues Transaction (refer to the Correspondence Data Maintenance, Linked to Gift or Dues Transactions topic for the 'how-to') or by using the Add Correspondence Utility.
Individual Correspondence rows may be created in the Events World from within an Event or Event Activity (refer to the Correspondence Data Maintenance, Linked to Event or Activity topic for the 'how-to') or by using the Add Correspondence Utility.
Sites with a license to use the Advanced Data Examiner (ADE) can use the ADE Import Tool and the ADE to insert stand-alone Correspondence rows into the system, and update stand-alone and linked Correspondence rows that already exist in the system.
Correspondence rows may be deleted one at a time via standard Millennium Data Maintenance procedures, or en masse by using the Delete Correspondence Utility.
The number of rows displayed per page can be changed at anytime by using the Paging selections at the bottom of the table. Users may also navigate to various pages by using this feature (see Paging Data for more information).
An Administrator can remove the Correspondence table from the Millennium Explorer if not used. See Table Options for more information.
Millennium system administrators or the database administrator can configure audit logging for the Correspondence data table. For more information, see Audit Log.
Correspondence Field Descriptions
Correspondence data is stored in the correspondence data table. The following fields are included. The field labels and non-displaying designations that are listed here are those that the system uses by default. If you want to override these default settings, you must customize your display definition.
Note: Custom fields are included in the data table, and are available for any purpose that you want to define. These fields are not shown on the standard Display or standard Data Maintenance forms. If you want to make use of one or more of the custom fields, use the Custom Display Designer to edit the standard Display and Data Maintenance forms.
|
Field Name Field Label |
Lookup Table |
Type SQL/Oracle Length [Default] {Check} |
Description |
|---|---|---|---|
|
corrkey non-displaying |
none |
numeric/number 13,0
|
The primary key field for all rows in all tables. This field holds a permanent "time stamp" of the precise time and date the row was created. This uniquely identifies a row within the table. (System field) |
|
corrid non-displaying |
none |
char 10
|
This field holds the ID number of the constituent who owns this row, providing a link between this row and all others in all other tables which also belong to the same constituent. (System field) |
|
non-displaying |
none |
char 1 ['N']
|
This field holds a Y/N flag to indicate the existence of a linked document(s). The path name of any number of documents may be entered and linked to this row. When the row is displayed, the names of the linked documents will display as hyperlink jumps. When a jump is executed, the document will be opened using the associated word processor. (System field) |
|
no label |
none |
datetime/date
[getdate()]
|
The date that this Correspondence row was last updated. This field is automatically entered by the system. (System field) |
|
no label |
none |
varchar/varchar2 30 [suser_sname()]
|
The User ID of the operator that last edited this Correspondence row. It is automatically entered by the system. (System field) |
|
corrugrp non-displaying |
none |
char 1 ['Z']
|
This field holds the Group letter that was associated with the User ID of the operator who last edited this row. (System field) |
|
Correspondence Name |
Correspondence Name |
char 6 [' ']
|
The institution-assigned name of the particular piece of correspondence described in this row (President's Fall Appeal, End of Year Reminder, etc.) |
|
Correspondence Type |
Correspondence Type |
char 6 [' ']
|
The kind of correspondence described in this row. (acknowledgment, invitation, solicitation, etc.) |
|
Date Sent |
none |
datetime/date
[getdate()]
|
The date on which this correspondence was sent. |
|
Department |
Department |
char 6 [' ']
|
The Department within the institution that is responsible for the correspondence. |
|
Sent by |
none |
char 10
|
The ID Number of the person who sent the correspondence, if that person is a constituent. |
|
corrtitle no label |
none |
char 30
|
The name title of the person who sent the correspondence. |
|
corrlast no label |
none |
char 55
|
The last name of the person who sent the correspondence. |
|
corrfirst no label |
none |
char 30
|
The first name of the person who sent the correspondence. |
|
corrmiddle no label |
none |
char 30
|
The middle name of the person who sent the correspondence. |
|
corrsuffix no label |
none |
char 30
|
The suffix from the name of the person who sent the correspondence. |
|
|
Name Type |
char 6
|
This field is a copy of the Name Type field from the "first found" Name row belonging to the constituent identified in the corrsentid field. Since Name records are ordered by the code for the Name Type, this will generally be the Preferred Name type. (System field) |
|
corrsortnm non-displaying |
none |
char 30
|
If this correspondence is sent by a constituent, this field is a copy of the sortname form of the name that is identified in the corrnamtyp field. If the correspondence is sent by a non-constituent, then this field will be blank. (System field) |
|
corrlnkfil non-displaying |
none |
char 2
|
If this row is acting as a secondary row attached to a primary row in another table, this field holds the number of the table to which the row is attached. If the Correspondence row is acting as a primary row, this field will be blank. (System field) |
|
corrlnkkey non-displaying |
none |
numeric/number 13,0 [0]
|
If this row is acting as a secondary row attached to a primary row in another table, this field holds a copy of the key field from the row to which it is attached. If the Correspondence row is acting as a primary row, this field will be blank. (System field) |
|
corrpoint non-displaying |
none |
numeric/number 10,0
|
This system-assigned field is no longer used or supported. (System field) |
|
corrfnum non-displaying |
none |
char 2 ['22'] {'22'} |
The file (table) number for the Correspondence table. (System field) |
|
corrcrdate non-displaying |
none |
datetime/date
[getdate()]
|
The date that the row was created. (System field) |
|
no label |
none |
varchar/varchar2 4000
|
A free text Comment that may be entered and displayed with this row only. |
|
corrsource Source |
Source |
char 6 [' ']
|
The source for the information in this row. |
|
Response |
Response Type |
char 6
|
This field holds a lookup table entry describing the response to the correspondence, for example, Will attend, Will not attend, Contact again later, etc. If this Correspondence row is linked to an Event or an Activity and the Registration process for the constituent is initiated from the display of this data row in the Events World, this field in the Correspondence row will automatically be set to 'Registered'. |
|
Response Date |
none |
datetime/date
|
The date of the response. |
|
Response Method |
Response Method |
char 6
|
The method by which the response to the correspondence was sent (delivered), for example, phone call, letter, email, in person, etc. |
|
corrdate1
|
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
corrdate2
|
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
corrmny1
|
none |
money/number (19,4)/(16,2)
|
This custom field may be defined and used by your institution to track any monetary information associated with this data row. |
|
corrmny2
|
none |
money/number (19,4)/(16,2)
|
This custom field may be defined and used by your institution to track any monetary information associated with this data row. |
|
corrnum1
|
none |
numeric/number 10, 0
|
This custom field may be defined and used by your institution to track any numeric information associated with this data row. |
|
corrnum2
|
none |
numeric/number 10, 0
|
This custom field may be defined and used by your institution to track any numeric information associated with this data row. |
|
corryesno
|
none |
char 1 ['N'] {'N' or 'Y'} |
This custom field may be defined and used by your institution to flag this data row for any purpose. |
|
corrlook1
|
Correspondence Lookup1 |
char 6
|
This custom field may be defined and used by your institution to track any lookup table driven information associated with this data row. |
|
corrlook2
|
Correspondence Lookup2 |
char 6
|
This custom field may be defined and used by your institution to track any lookup table driven information associated with this data row. |
|
corrlook3
|
Correspondence Lookup3 |
char 6
|
This custom field may be defined and used by your institution to track any lookup table driven information associated with this data row. |
|
corrtext
|
none |
varchar/varchar2 255
|
This custom field may be defined and used by your institution to track any free text information you want to associate with this data row. |
|
corrsolic non-displaying |
Solicitation |
char 6 [' ']
|
This system-generated field holds the Code from the Solicitation lookup table entry that is to be identified with the Correspondence Name that is identified for this data row. (System field) |
|
corrsolnm Solicitation |
Solicitation |
char 65
|
This system-generated field holds the Value (name) from the Solicitation lookup table entry that is to be identified with the Correspondence Name that is identified for this data row. (System field) |
|
|
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
corrdate4
|
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
corrdate5
|
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
corrdate6
|
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
corrdate7
|
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
corrdate8
|
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
corrdate9
|
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
corrdte10
|
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
corrdte11
|
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
corrdte12
|
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
corrmny3
|
none |
money/number (19,4)/(16,2)
|
This custom field may be defined and used by your institution to track any monetary information associated with this data row. |
|
corrmny4
|
none |
money/number (19,4)/(16,2)
|
This custom field may be defined and used by your institution to track any monetary information associated with this data row. |
|
corrmny5
|
none |
money/number (19,4)/(16,2)
|
This custom field may be defined and used by your institution to track any monetary information associated with this data row. |
|
corrmny6
|
none |
money/number (19,4)/(16,2)
|
This custom field may be defined and used by your institution to track any monetary information associated with this data row. |
|
corrmny7
|
none |
money/number (19,4)/(16,2)
|
This custom field may be defined and used by your institution to track any monetary information associated with this data row. |
|
corrmny8
|
none |
money/number (19,4)/(16,2)
|
This custom field may be defined and used by your institution to track any monetary information associated with this data row. |
|
corrmny9
|
none |
money/number (19,4)/(16,2)
|
This custom field may be defined and used by your institution to track any monetary information associated with this data row. |
|
corrmny10
|
none |
money/number (19,4)/(16,2)
|
This custom field may be defined and used by your institution to track any monetary information associated with this data row. |
|
corrmny11
|
none |
money/number (19,4)/(16,2)
|
This custom field may be defined and used by your institution to track any monetary information associated with this data row. |
|
corrmny12
|
none |
money/number (19,4)/(16,2)
|
This custom field may be defined and used by your institution to track any monetary information associated with this data row. |
|
corrnum3
|
none |
numeric/number 10, 0
|
This custom field may be defined and used by your institution to track any numeric information associated with this data row. |
|
corrnum4
|
none |
numeric/number 10, 0
|
This custom field may be defined and used by your institution to track any numeric information associated with this data row. |
|
corrnum5
|
none |
numeric/number 10, 0
|
This custom field may be defined and used by your institution to track any numeric information associated with this data row. |
|
corrnum6
|
none |
numeric/number 10, 0
|
This custom field may be defined and used by your institution to track any numeric information associated with this data row. |
|
corrnum7
|
none |
numeric/number 10, 0
|
This custom field may be defined and used by your institution to track any numeric information associated with this data row. |
|
corrnum8
|
none |
numeric/number 10, 0
|
This custom field may be defined and used by your institution to track any numeric information associated with this data row. |
|
corrnum9
|
none |
numeric/number 10, 0
|
This custom field may be defined and used by your institution to track any numeric information associated with this data row. |
|
corrnum10
|
none |
numeric/number 10, 0
|
This custom field may be defined and used by your institution to track any numeric information associated with this data row. |
|
corrnum11
|
none |
numeric/number 10, 0
|
This custom field may be defined and used by your institution to track any numeric information associated with this data row. |
|
corrnum12
|
none |
numeric/number 10, 0
|
This custom field may be defined and used by your institution to track any numeric information associated with this data row. |
|
corrchk1
|
none |
char 1 ['N'] {'N' or 'Y'} |
This custom field may be defined and used by your institution to flag this data row for any purpose. |
|
corrchk2
|
none |
char 1 ['N'] {'N' or 'Y'} |
This custom field may be defined and used by your institution to flag this data row for any purpose. |
|
corrchk3
|
none |
char 1 ['N'] {'N' or 'Y'} |
This custom field may be defined and used by your institution to flag this data row for any purpose. |
|
corrchk4
|
none |
char 1 ['N'] {'N' or 'Y'} |
This custom field may be defined and used by your institution to flag this data row for any purpose. |
|
corrchk5
|
none |
char 1 ['N'] {'N' or 'Y'} |
This custom field may be defined and used by your institution to flag this data row for any purpose. |
|
corrchk6
|
none |
char 1 ['N'] {'N' or 'Y'} |
This custom field may be defined and used by your institution to flag this data row for any purpose. |
|
corrchk7
|
none |
char 1 ['N'] {'N' or 'Y'} |
This custom field may be defined and used by your institution to flag this data row for any purpose. |
|
corrchk8
|
none |
char 1 ['N'] {'N' or 'Y'} |
This custom field may be defined and used by your institution to flag this data row for any purpose. |
|
corrchk9
|
none |
char 1 ['N'] {'N' or 'Y'} |
This custom field may be defined and used by your institution to flag this data row for any purpose. |
|
corrchk10
|
none |
char 1 ['N'] {'N' or 'Y'} |
This custom field may be defined and used by your institution to flag this data row for any purpose. |
|
corrtxt1
|
none |
varchar/varchar2 255
|
This custom field may be defined and used by your institution to track any free text information you want to associate with this data row. |
|
corrtxt2
|
none |
varchar/varchar2 255
|
This custom field may be defined and used by your institution to track any free text information you want to associate with this data row. |
|
corrtxt3
|
none |
varchar/varchar2 255
|
This custom field may be defined and used by your institution to track any free text information you want to associate with this data row. |
|
corrtxt4
|
none |
varchar/varchar2 255
|
This custom field may be defined and used by your institution to track any free text information you want to associate with this data row. |
|
corrtxt5
|
none |
varchar/varchar2 255
|
This custom field may be defined and used by your institution to track any free text information you want to associate with this data row. |
|
Corresp. Method |
correspondence_mode |
char 6 [' ']
|
The method by which the correspondence was sent; for examples, text message, email, phone call |
|
corremail |
varchar/varchar2 244
|
The email address to where the correspondence was sent. | |
|
corrphone Phone |
varchar/varchar2 244
|
The phone number to where the correspondence was sent. | |
|
corrclick Clicked? |
char 1 ['N'] {'N' or 'Y'} |
Set to 'Y' if the correspondence message was selected. | |
|
corrbounce Bounced? |
char 1 ['N'] {'N' or 'Y'} |
Set to 'Y' if the correspondence message was returned. | |
|
corrsoftb Soft Bounce? |
char 1 ['N'] {'N' or 'Y'} |
Set to 'Y' if the message was returned because it temporarily failed delivery. (soft bounce) | |
|
corrupkey non-displaying |
numeric/number 13,0
|
This field holds the timekey of the pending data row that may be updated from the correspondence_pending table. (system field)
|
|
|
corrpend non-displaying |
char 1 ['N'] {'N' or 'Y'} |
This field is a flag to indicate that this data row has linked information in the 'pending' Correspondence table as a result of data entry by the constituent via the Online Constituent Directory. That linked info is pending incorporation into this data row. (system field) | |
|
corradesrc non-displaying |
ade_source |
char 30
|
If this row is created or updated from data that originated from the correspondence_pending table, then this field will hold information about the data's import source. Data whose import source is the ADE Import Tool can be assigned a value (other than Online Directory or Soft Edit) from the ade_source lookup table by the user who executes the utility. Or, the source can be blank. |
Indices - Correspondence
| Index | Primary | Foreign | Unique | Fields | Sort |
|---|---|---|---|---|---|
| pk_correspondence | Y | Y | corrkey | ASC | |
| corr02 | corrid | ASC | |||
| corr03 | corrlnkfil, corrlnkkey |
ASC | |||
| corr04 | corrsentid | ASC | |||
| corr05 | Y | corrname | ASC |