Relationships
The Relation table allows you to maintain a variety of relationships that usually fall into one of the following general categories.
- Familial relationships will include those between family members (spouse/spouse, father/daughter, mother/son, brother/sister, life partner/life partner, etc.) but could be expanded to include any relationship that your institution deems worthy of tracking (neighbors, friends, college roommates).
- Professional or corporate relationships will include employer/employee, parent corporation/subsidiary, or other professional associations.
- Fundraising relationships are those that track assignments made by your institution for the purpose of cultivating donors or soliciting them for a gift (prospect/prospect manager, prospect/solicitor, prospect/class agent, etc.).
You may use the Group (relisagrp, relwhogrp) fields to identify these relationships into these (or additional) groups. Doing so will allow you to filter or sort certain rows for display based on those groups, or to perform reporting selections using those broader, Group designations.
Relationship rows from any of these general categories are created, displayed and edited using the same techniques. Any Relationship may be created between two constituents in the database, or between a constituent and a non-constituent. If the second party is a non-constituent, there will be some minor variations in the input and display of the information.
You may maintain Start and Stop dates for any relationship but they are especially useful with fund raising relationships, to show the period of time that a particular assignment is or was in effect.
If you have used the standard set of mandatory codes from the Relationship Type lookup table, Millennium performs several input, editing and maintenance functions, described as automatic features.
When you create any Relationship row, the system will automatically create a row that belongs to the second party (if a constituent), showing the reverse relationship, and including the same Standard information contained in the original data row, such as Start and Stop Dates, Comments, or Source. However, information in the custom fields will not be carried over to the reverse relationship.
Example: If A and B are both constituents and you create a row showing that A is an employer whose employee is B, the system automatically creates a row belonging to B showing that B is an employee whose employer is A.
When this information is displayed, the name of the related constituent appears as hyperlink text which will take you to the display of the Relationship page belonging to the second constituent.
If you have used the standard mandatory codes from the Relationship Type lookup table, Millennium can create and display a network of relationships. A diagrammatic Relationship Tree will display other constituents' names and their relationship to the owner of the Relationship Tree, as hyperlinks which may be used to navigate from page to page of information for these related constituents. These trees may show either family, corporate (professional), or fundraising relationships.
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).
Millennium system administrators or the database administrator can configure audit logging for the Relationships data table. For more information, see Audit Log.
Acceptable (Allowable) Relationships
Your site may choose to use the relation_pairs lookup table to define what relationship types are acceptable for use with other relationship types.
Example: You may want to set up the relation_pairs table so that a user can create a mother-child, or a mother-daughter, or a mother-son relationship, but a user cannot create a mother-uncle relationship.
When you create a relationship row, you may select any active relationship type for the first constituent - the Is A (relisa) relationship type. Once selected, the list of relationship types available for the second constituent or non-constituent - the Whose (relwhose) relationship type - will be limited to those active relationship types that are allowed, by the entries in the relation_pairs table, to be paired with the first relationship type.
If an relationship type is not entered in relation_pairs lookup table as a Is A relationship type, then all active relationship types will be allowed for use as the Whose relationship type.
Linked Relationships Rows
When the second party in any relationship row is also a constituent, the display of the row belonging to the first party will show the second party's name as hyperlink text. If you execute the hyperlink jump from that display, you will be taken to the display of the second party's data. From that page, you may use the linked name from the reverse relationship to return to the original page, or you may continue to navigate as you want.
Automatic Relationship Features
When particular Relationships Types are specified in the Is A field, certain automatic features within the system are enabled or triggered. Those situations and the fields involved are described below.
Marital Status
The Marital Status field in an Individual's Basic Data row affects Relationship rows in the following ways:
- The creation of a Spouse - Spouse Relationship row automatically sets the Marital Status field in the Individual’s Basic Data rows to Married.
- Changing the Marital Status field in Basic Data from Married to Divorced automatically changes the Is a/Whose fields in existing Relationship rows of Spouse - Spouse to Divorced Spouse - Divorced Spouse.
- Changing the Is a/Whose fields in a Relationship row from Spouse - Spouse to Divorced Spouse - Divorced Spouse automatically changes the Marital Status field in the Basic Data rows to Divorced. Note: When a Divorced Spouse - Divorced Spouse relationship is created, the system checks for the presence of another Spouse - Spouse relationship before it automatically edits the Marital Status in the Basic Data. This permits you to enter historical data on divorced spouses without incorrectly having the Marital Status updated.
Parent Corporation
The Parent Corporation field in a Corporate or Foundation Basic Data row affects a Relationship row in the following way:
Entering the name or ID number of another constituent in the Parent Corporation field in a Corporate or Foundation Basic Data row automatically creates a Relationship row for each party. The Relationship Is a/Whose fields will be Parent Corporation - Subsidiary as appropriate in the rows belonging to each party.
Removing the name of the Parent Corporation in a Corporate or Foundation Basic Data row automatically deletes an existing Parent Corporation - Subsidiary relationship.
Employment
Employment rows affects Relationship rows in the following ways:
- Creating an Employment row that identifies another constituent as the Employer, automatically generates the creation of Relationship rows for each party. The Is a/Whose Relationship fields will be Employer - Employee as appropriate in the rows belonging to each party.
- Editing the Status field in an existing Employment row from Active to Retired, automatically updates the Is a/Whose fields in the corresponding Relationship rows from Employer to Former Employer and Employee to Retired Employee. This is done as appropriate in the rows belonging to each party.
- Editing the Status field in an existing Employment row from Active to No Longer at this Position, automatically updates the Is a/Whose fields in the corresponding Relationship row from Employer to Former Employer and Employee to Former Employee. This is done as appropriate in the rows belonging to each party.
Death
The creation of a Death row for a constituent affects that constituent's Relationships in the following ways:
- If a row is created in the Death table for one spouse, the Is a/Whose fields in both of the Relationship rows are changed from Spouse - Spouse to Deceased Spouse - Surviving Spouse, as appropriate for each constituent.
- If a row is created in the Death table belonging to either an Employer or an Employee from a set of Relationship rows, both rows are changed from Employer/Employee to Former Employer/Former Employee.
Special Relationship Types
Prospect Management Relationships
Prospect Management relationships are relationship types, or Roles, that establish links between Prospect Managers and Prospects, Prospect Manager Directors, the Managers they oversee, and Prospect Researchers and Assistants, and the Prospect Managers and Directors that they assist.
A user who is also a constituent with a relationship row that has a type that is assigned to a Prospect Management Role of either Prospect Manager, Director, or Assistant/Researcher can use the My Staff and My Prospects Favorites feature to quickly access data for prospective donors. A user with a Director or an Assistant/Researcher Prospect Management Role can also access the Prospect Management Reassignment form.
Any relationship type in the relationship lookup table can be assigned to a Prospect Management Role of Prospect, Prospect Manager, Director or Assistant/Researcher, therefore making that relationship a Prospect Management Relationship.
The following table shows what Prospect Management Roles are necessary to activate Favorites functionality.
|
Prospect Management Role for User's Constituent ID Number (Is A) |
Prospect Management Role for the Second Constituent (Whose) |
Favorites Functionality |
|---|---|---|
|
Director (dir) |
Prospect Manager (pm) |
My Staff to view the User's Prospect Managers My Tasks PM Reassign |
|
Assistant/Researcher (asst) |
Director (dir) |
My Staff to view the Director's Prospect Managers My Tasks PM Reassign |
|
Prospect Manager (pm) |
Prospect (p) |
My Prospects to view the User's Prospects My Tasks |
|
Assistant/Researcher (asst) |
Prospect Manager (pm) |
My Prospects to view the Prospect Manager's Prospects. My Tasks |
To set a relationship Prospect Management Role on an existing relation, complete the following steps.
- Log on to the system as a user with rights to the Relationships Lookup Table.
- From the left-hand navigation, Select .
- Click the Relationships hyperlink.
- Locate the relationship to update and hover over the edit menu. Select Update/Delete from the hover menu.
-
On the Relationships form, select a role from the Prospect Management Role drop down menu.
-
Assistant/Researcher
-
Director
-
Prospect
-
Prospect Manager
-
- Select Update.
To create a new relationship with a Prospect Management Role, complete the following steps.
- Log on to the system as a user with rights to the Relationships Lookup Table.
- From the left-hand navigation, Select Tools> Profiles Lookups>Lookup Tables.
- Click the Relationships hyperlink.
- Hover over the Relationships edit menu and select Insert from the hover menu.
-
On the Relationships form, fill in the desired data elements, making sure to select a role from the Prospect Management Role drop down menu.
- Assistant/Researcher
- Director
- Prospect
- Prospect Manager
- Select Insert.
Spousal Relationships
When Millennium refers to "spousal relationships", it usually encompasses several of the entries from the Relationships lookup table as being "spousal". The necessary codes for these entries and the behaviors that are or aren't triggered by their existence are described below. A constituent may have one and only one Relationship of the following types:
- Spouse
- Spouse Separate Mailing
- Life Partner
- Life Partner Separate Mailing.
- Spouse - Spouse Relationships
Lookup table Code: "sp" - "sp"
A spouse - spouse relationship establishes a link between the rows of two married constituents. Mailing labels and reports may use the spouse relationship to create a combined mailing name. In addition, the spouse relationship activates the display of the Spouse button in the header display that allows you to "toggle" between each spouse's data rows with the click of the mouse. The creation or editing of a relationship of this type will cause the automatic updating of the Marital Status field in the Basic Data row. This relationship type will itself be automatically updated to "Deceased Spouse - Surviving Spouse" if a Death row is created for either of the constituent pair. The existence of this relationship type activates the Spouse check boxes in the Joint or Credit options in the Input Gift and Dues Transaction pages.
Divorced Spouse - Divorced Spouse Relationships
Lookup table Code: "dvsp" - "dvsp"
- A divorced spouse - divorced spouse relationship may be created in one of several ways.
- As a Relationship row is created, the divorced spouse entry from the Relationships lookup table may be used in the Is a/Whose columns.
- An existing spouse - spouse Relationship may be edited to divorced spouse - divorced spouse.
- If the Marital Status field from the Basic Data row for either party in a spouse - spouse relationship is changed to Divorced, the system edits the relationship rows for both parties to divorced spouse - divorced spouse.
Deceased Spouse - Surviving Spouse Relationships
Lookup table Code: "dsp" - "ssp"
When a row is created in the Death table for one of a constituent pair who have a spouse - spouse relationship, the relationship types are automatically changed to Surviving Spouse and Deceased Spouse. You may also enter this relationship type manually through the relationship maintenance page. The display of the Spouse button in the header display is still active but the ability to input spouse joint giving or dues transactions is disabled. The Marital Status field in the Basic Data row is updated and the names will not combine in reporting.
Separate Mailing Address Spouse Relationships
Lookup table Code: "spnm" - "spnm"
Separate mailing address spouse, allows you to create a spouse relationship that links as the "sp - sp" does, except that it will not combine for mailings. This relationship's code in the Relationship lookup table must be "spnm". You may name this relationship as you want in the value field of the Relations lookup table row. The existence of this relationship type activates the Joint or Credit options in the Input Gift and Dues Transaction page and also the display of the Spouse button in the header display.
Life Partner Relationships
Lookup table Code: "lp" - "lp"
The Life Partner relationship may combine for mailings, depending on the choices you make in report writing. Its code in the Relations Lookup table must be, "lp", but you may name it as you want in the lookup table value. The existence of this relationship row activates the display of the Spouse button in the header display. It does not affect the Marital Status field in the Basic Data row. The existence of this relationship type activates the Joint or Credit options in the Input Gift and Dues Transaction page. This allows you the ability to enter a single transaction as being "joint" between two life partners during gift or dues entry, or credit for one life partner for a transaction given by the other.
Separate Mailing Address Life Partner Relationships
Lookup table Code: "lpnm"
The Separate Mailing Address Life Partner relationship will not combine for mailings, regardless of the choices you make in report writing. It's code in the Relations Lookup table must be, "lpnm", but you may name it as you want in the lookup table value. The existence of this relationship row activates the display of the Spouse button in the header display. It does not affect the Marital Status field in the Basic Data row. The existence of this relationship type activates the Spouse Joint and Credit options in the Input Gift and Dues Transaction page. This allows you the ability to enter a single transaction as being "joint" between two life partners during gift or dues entry, or credit for one life partner for a transaction given by the other.
Combine Mail (Not Married) Relationships
Lookup table Code: "cm" - "cm"
The Combine Mail (not married) relationship is used for a non-spouse relationship that may combine for mailings, depending on the choices you make in report writing. It's code in the Relations Lookup table must be, "cm", but, you may name it as you want in the lookup table value. This relationship type does not activate the display of the Spouse button in the header, nor does it affect the marital status field in the Basic Data row, nor does it activate the Joint with Spouse or Credit for Spouse option in the Input Gift and Dues Transaction page.
Non-Constituent Relationships
The system also allows you to record a relationship between a constituent and an individual who is not recorded on the system (a non-constituent). While the reverse relationship cannot be created (since a non-constituent will have no rows or data of its own), it may be beneficial to record such relationships to provide a broader and more detailed overview for a constituent.
Example: It might be useful to know that a constituent is a cousin of the First Lady but you would not necessarily create a constituent profile for the First Lady.
A relationship between a constituent and a non-constituent can be created in one of two ways.
If you are certain that the relationship is between a constituent and a second individual who is a non-constituent:
- Select Insert from the section header Edit button or a record's edit menu
- Click the Non-Constituent button on the Relationships Insert form. You are then presented with a Non-Constituent Insert form to complete.
If you do not know if the second individual in the relationship is a constituent or a non-constituent:
- Select Insert from the section header Edit button context menu or any row's Edit button context menu. The Relationship insert form displays
- Select the desired relationship types from the Is A and Whose list boxes.
- Populate the Is ID text box with the complete or partial name of the second individual, using the format, Last Name/First Name(space)Middle Name;Suffix.
- Populate the other fields as desired.
-
Select Insert. Millennium will then search for all constituents who may match the name criteria you entered.
- If the search returns one or more constituents who completely or partially match the name criteria, click on the constituent that you identify as your second individual in the relationship. A constituent relationship will then be created.
- If one of the listed constituents is not your second individual, click on the respecify the query hyperlink. You now have the option to either re-define your search or to create a non-constituent relationship by clicking the Enter Non-Constituent Relation hyperlink.
- If the search does not return any constituent who matches the name criteria, the Relation ID Search form displays and you will have the option to re-specify your search or to create a non-constituent relationship by following the Enter Non-Constituent Relation hyperlink.
Convert a Non-Constituent Relationship to a Constituent Relationship
If, after a non-constituent relationship has been created, the non-constituent becomes a constituent in Millennium, that non-constituent relationship can be converted to a constituent relationship. This can be done as an update to the non-constituent relationship row.
- Access the relationship row for the constituent.
- Select the Update item from the row's Edit button context menu.
- Check the Change to a Constituent check box.
- Enter the newly created constituent's ID number or name (formatted Last Name/First Name (space) Middle Name;Suffix) in the Is ID text box.
- Update the Begin, End, Source and Comment fields as desired.
- Click Update.
This relationship is now a constituent relationship, with a hyperlink to the reverse relationship between the new constituent and this constituent. And if this relationship was linked to another constituent relationship, that other relationship will be updated to a constituent relationship as well.
- If a constituent has a spousal relationship with a non-constituent, and the relationship type is changed to Surviving Spouse/Deceased Spouse (where the constituent is the Surviving Spouse), then the Marital Status on the Basic Data row for the constituent is changed to Surviving Spouse (ss).
-
In order to activate the Non-Constituent Relationship to Constituent Relationship functionality, a system administrator must add the Is field to the Non-Constituent Maintenance form.
- Access the Profiles version of the Custom Display Designer by using Millennium Explorer. Click Tools. Click Display Design. Click Profiles.
- Click the Profiles menu. Point to Biographical. Point to Relation. Click Non-Constituent Standard.
- Add the data field named relrelatid to the Maintenance form.
- Click the Save menu.
Linked Relationships
A Relation row may be linked to data rows in several other Millennium Data tables: Tracking, Planned Gift or Proposal . This is a Secondary Link, and you may link any number of Relationship rows to any of these types of primary rows. These linked Relations display with the row to which they are linked, and they will also display with Relation table display. These secondary linked relation rows are stored in the Relation table. However, for data maintenance purposes, users should access them through the display of the primary rows to which they link and not through the constituent's Relation display.
When a Relation row exists for a constituent, you may create a one-to-one linked Relation row that belongs to a spouse or another constituent, and only one joint link can be created per Relation row. The linked row will be a copy of the first constituent's row. You may also link an existing Relation row to a Relation row that also already exists for a spouse or another constituent; when you do so, the Relation row for the second constituent will be overwritten with the first constituent's Relation row information. Because the Relation rows are linked, any edits that are made via one constituent's row will automatically be applied to the second constituent's row as well. This is true whether subsequent edits are made via the original constituent or the second constituent's data row. Likewise, if either of the rows is deleted, the linked row will also be deleted. Linked rows may be unlinked. This preserves both data rows and allows you to perform edits or deletions to one without affecting the other.
The following topics discuss the creation and maintenance of linked Relation rows in complete detail.
Data Maintenance, Link New to Existing
Data Maintenance, Link to Existing to Existing
Data Maintenance, How to Link New to Existing
Data Maintenance, How to Link Existing to Existing
Contact Info
Your site may want to maintain additional information, such as address and phone number, or other such contact information, about the second party in a Relation row, especially if that second party is a non-constituent. The Contact Info data table exists for exactly this purpose; a Contact Info data row is created via any constituent or non-constituent Relation row, links to that Relation row, and displays as secondary 'linked' data with that row. See the Contact Info topic for complete details.
Circles
Relationships are characterized as one-to-one. A relation row is a way to associate (relate) one constituent to only one other constituent or non-constituent. The Circles functionality in Millennium offers a way to track associations, or relationships, between one constituent and a group of constituents.
Example: A Relation row tracks the relationship between a mother and a son. A second Relation row tracks the relationship between the mother and a daughter, a third tracks the relationship between the mother and a father, a fourth tracks the relationship between father and son, and so forth. You cannot easily and quickly see that these four individuals belong to the same family. If you create a Circle to represent the family, you can then assign the mother, the son, the daughter, and the father to be members of that family circle and give them the position of mother, son, daughter, and father accordingly.
See the Circles topic for complete details.
Relationships Field Descriptions
Relationship data is stored in the relation 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 |
|---|---|---|---|
|
relkey 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) |
|
relid 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) |
|
reldoc non-displaying |
none |
char 1 ['N']
|
This field holds a Y/N flag to indicate the existence of a linked document for this row. (system field) |
|
reldate no label |
none |
datetime/date
[getdate()]
|
The date that this row was last updated. This field is automatically entered by the system. Note that this behavior is suppressed for changes that are made to a constituent's name, which is also stored in the Relationship data row. (system field) |
|
reluserid no label |
none |
varchar/varchar2 30 [suser_sname()]
|
The User ID of the operator who last edited this row. It is automatically entered by the system. Note that this behavior is suppressed for changes that are made to a constituent's name, which is also stored in the Relationship data row. (system field) |
|
relugroup 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) |
|
relrelatid Is |
none |
char 10
|
The ID number of the individual, corporation, foundation or other organization that is related to the constituent to whom this Relationship row belongs. |
|
relrevkey non-displaying |
none |
numeric/number 13,0
|
When a Relationship row is created for constituent A, a relationship row is automatically created for constituent B, showing the reverse Relationship Type. To link these two rows, the relrevkey in constituent A's row holds a copy of the relkey field from constituent B's row. Likewise, the relrevkey field in constituent B's row will hold a copy of the relkey field from constituent A's row. A link is created using this field only when both parties are constituents, as triggered by clicking the Constituent button in the Maintenance form. (system field) |
|
Is a |
char 6
|
The relationship of the constituent to the second party. |
|
|
Whose |
char 6
|
The relationship of the second party to the constituent. |
|
|
rellinkfil non-displaying |
none |
char 2
|
This field is used to indicate that a link exists
between this Relationship row and |
|
rellinkkey non-displaying |
none |
numeric/number 13,0
|
This field holds a copy of the 'key' field from the linked row to which this Relationship row is linked. If this Relationship row is not linked to another row, this field will be blank. (system field) |
|
relchldflg Child (only displays for Non-constituent Relationships) |
none |
char
|
Relationship rows may identify another constituent or a non-constituent as the second party. If a non-constituent relationship is created, this field is used to signal the system that the second party is a child of the owner of the row. |
|
Status |
char 6
|
The status of the recruitment effort being made with the non-constituent relation. In the standard use of this table, this field is only available for non-constituent Relationships. |
|
|
relbirthdt Birthday (only displays for Non-constituent Relationships) |
none |
datetime/date
[getdate()]
|
The birthday of the Non-constituent identified as the second party in this relationship row. Note that each institution may choose to use this field OR the separate month, day and year fields (relbirthmn, relbirthdy, relbirthyr) and edit their display and data maintenance definitions accordingly. Using one option will not automatically populate the field(s) for the other option. |
|
relsex Gender (only displays for Non-constituent Relationships) |
none |
char 1
|
The gender of the non-constituent identified as the second party in this Relationship row. |
|
relstrtdat Begin |
none |
datetime/date
[getdate()]
|
The beginning date for this relationship. |
|
relstopdat End |
none |
datetime/date
[getdate()]
|
The ending date for this relationship. |
|
Group |
char 6
|
This field is a copy of the Group field from the lookup table entry that is specified in the relisa field. (system field) |
|
|
Group |
char 6
|
This field is a copy of the Group field from the lookup table entry that is specified in the relwhose field. (system field) |
|
|
Source |
char 6
|
The source for this Relationships information. |
|
|
reldnrflag non-displaying |
none |
char 1
|
Not in use at the present time. (system field) |
|
relpoint non-displaying |
none |
numeric/number 10,0
|
This system-assigned field is no longer used or supported. (system field) |
|
no label |
char 30
|
The Title portion of the name of the second party identified in this Relationship row. If that party is a constituent, this will be a copy of the nametitle field from the Name row of the specified Type, belonging to the second party. If that party is a non-constituent, the information will have been entered in the data maintenance form. |
|
|
rellast no label |
none |
char 55
|
The last name portion of the name of the second party identified in this Relationship row. If that party is a constituent, this will be a copy of the namelast field in the Name row of the specified Type, belonging to the second party. If that party is a non-constituent, the information will have been entered in the data maintenance form. |
|
relfirst no label |
none |
char 30
|
The first name portion of the name of the second party identified in this Relationship row. If that party is a constituent, this will be a copy of the namefirst field in the Name row of the specified Type, belonging to the second party. If that party is a non-constituent, the information will have been entered in the data maintenance form. |
|
relmiddle no label |
none |
char 30
|
The middle name portion of the name of the second party identified in this Relationship row. If that party is a constituent, this will be a copy of the namemiddle field in the Name row of the specified Type, belonging to the second party. If that party is a non-constituent, the information will have been entered in the data maintenance form. |
|
relsuffix no label |
none |
char 30
|
The suffix portion of the name of the second party identified in this Relationship row. If that party is a constituent, this will be a copy of the namesuffix field in the Name row of the specified Type. If that party is a non-constituent, the information will have been entered in the data maintenance form. |
|
Type |
char 6
|
The Name Type of the name used for the second party in this Relationship row. This will usually be the preferred name, unless your institution has altered the standard codes for that lookup table. If the second party is a non-constituent, the system will automatically populate this field with 'non'. |
|
|
relsortnam non-displaying |
none |
char 30
|
This field is constructed from the contents of the relfirst, relmiddle and rellast and relsuffix fields according to the set of rules that follow. This field is primarily used for alphabetizing lists in reports because it nullifies inconsistencies that can occur due to the variations of names. (duPont, van Patton, McDonald, Mc Donald, etc.) The system constructs the field in the following way:
1. Convert all letters in relfirst, relmiddle, rellast, and relsuffix to uppercase.
2. Remove any characters from the same fields that are not blank, slash, semicolon, characters "0" through "9", or characters "A" through "Z".
3. Trim any spaces off the rellast field and follow it with a forward slash. Directly after the forward slash, add the relfirst field followed by a space and the relmiddle field. Do not follow the relfirst field with a space if the relmiddle field is blank.
4. If the relsuffix field is blank, the relsortnam has been completely formatted. If the relsuffix does have a value then follow the relfirst, relmiddle combination with a semicolon and then the relsuffix. Do not put a space between the semicolon and relsuffix. (system field) |
|
relfnum non-displaying |
none |
char 2 ['04'] {'04'} |
The file (table) number for the Relationships table. (system field) |
|
relcrdate non-displaying |
none |
datetime/date
[getdate()]
|
The date that the row was created. (system field) |
|
relcomm no label |
none |
varchar/varchar2 4000
|
A free text Comment that may be entered and displayed with this row only. This text is shown in the user's text accent color. |
|
reldate1 |
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
reldate2 |
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
relmny1 |
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. |
|
relmny2 |
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. |
|
relnum1 |
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. |
|
relnum2 |
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. |
|
relyesno |
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. |
|
rellook1 |
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. |
|
|
rellook2 |
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. |
|
|
rellook3 |
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. |
|
|
reltext |
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. |
|
relpend Pending |
none |
char 1 ['N'] {not null} |
This field is a flag to indicate that this data row has linked information in the 'pending' Relationship 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) |
|
relpubfl Publish |
none |
char 1 ['N'] {not null} |
This field is a flag to indicate whether this data row will or will not be available for online publishing. This flag will be applicable when the Options for Publishing Data for the Relation table are set to 'Published by Consent'. Otherwise, the corepub04 column flag in the Basic Data table will be used. |
|
relcontfl non-displaying |
none |
char 1 ['N'] {not null} |
This field is a flag to indicate whether or not Contact Info has been linked to the data row. |
|
relothcrd Credit |
none |
char 1 ['N'] {not null} |
This field is reserved for future use by Millennium. |
|
reljntid non-displaying |
none |
char 1 [' ']
|
If this row is joint with another constituent (each constituent owns a row containing the same data and which are dynamically updated), this field provides linkage between the two rows. If the row is not joint, this field is blank. If it is joint, this field holds a copy of the ID Number of the other constituent who owns the other relation row. (system field) |
|
reljntkey non-displaying |
none |
numeric/number 13,0 [0]
|
If this row is joint with another constituent (each constituent owns a row containing the same data and which are dynamically updated), this field provides linkage between the two rows. If the row is not joint, this field is blank. If it is joint, this field holds a copy of the key field from the other relation row. (system field) |
|
relbirthyr |
none |
char 4
{null} |
This field, along with relbirthmn and relbirthdy, provide an alternative to the use of the relbirthdt field. Your institution has the option to modify the display and maintenance definitions of the Basic Data row and replace the relbirthdt (a true datetime/date field which includes all three pieces of the date) with all or some of the fields relbirthyr, relbirthmn, and relbirthdy which store the three pieces of information in separate columns. Using one option will not automatically populate the field(s) for the other option. This field is validated as a four-digit year (yyyy), between 1600 and 2200. |
|
relbirthmn |
none |
char 4
{null} |
See relbirthyr. |
|
relbirthdy
|
none
|
char 4
{null} |
See relbirthyr.
|
|
relupkey non-displaying |
none |
numeric/number 13,0
|
This field holds the Timekey of the pending data row that may be updated from the relation_pending table. |
|
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
|
reldate4 |
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
reldate5 |
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
reldate6 |
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
reldate7 |
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
reldate8 |
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
reldate9 |
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
reldte10 |
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
reldte11 |
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
reldte12 |
none |
datetime/date
|
This custom field may be defined and used by your institution to track any date information associated with this data row. |
|
relmny3 |
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. |
|
relmny4 |
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. |
|
relmny5 |
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. |
|
relmny6 |
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. |
|
relmny7 |
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. |
|
relmny8 |
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. |
|
relmny9 |
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. |
|
relmny10 |
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. |
|
relmny11 |
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. |
|
relmny12 |
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. |
|
relnum3 |
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. |
|
relnum4 |
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. |
|
relnum5 |
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. |
|
relnum6 |
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. |
|
relnum7 |
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. |
|
relnum8 |
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. |
|
relnum9 |
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. |
|
relnum10 |
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. |
|
relnum11 |
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. |
|
relnum12 |
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. |
|
relchk1 |
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. |
|
relchk2 |
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. |
|
relchk3 |
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. |
|
relchk4 |
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. |
|
relchk5 |
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. |
|
relchk6 |
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. |
|
relchk7 |
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. |
|
relchk8 |
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. |
|
relchk9 |
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. |
|
relchk10 |
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. |
|
reltxt1 |
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. |
|
reltxt2 |
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. |
|
reltxt3 |
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. |
|
reltxt4 |
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. |
|
reltxt5 |
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. |
|
reladesrc non-displaying |
char 30
|
If this row is created or updated from data that originally came from the relation_pending table, then this field will hold information about the data's import source. Data from the Online Constituent Directory will have a source of Online Directory. Data from Soft Edit users will have a source of Soft Edit. 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 - Relationships
| Index | Primary | Foreign | Unique | Fields | Sort |
|
pk_relation |
Y |
|
Y |
relkey |
ASC |
|
relation02 |
|
Y |
|
relid |
ASC |
|
relation03 |
|
Y |
|
relid |
ASC |
|
relation04 |
|
|
|
rellinkkey |
ASC |
|
relation05 |
|
Y |
|
relrelatid |
ASC |
|
relation06 |
|
|
|
relrevkey |
ASC |
|
relation07 |
|
|
|
relcrdate |
ASC |
|
relation08 |
|
|
|
relisa |
ASC |
|
relation09 |
|
|
|
reljntkey |
ASC |