Warehouse table: Relationships

The Relationships warehouse table corresponds to the CustomerRelation view in the CRM database, with additional data drawn from the Contact view (for information about the related person) and the A422_PersonOrg view (for information about the related person's schooling).  The school fields are drawn from the most recently updated A422_PersonOrg record for the related person.

Relationship records are joined to parent records by the PersonGUID.

Note that, unlike most subrecord tables, this table does not have state code fields. 

This table is available in version Stage IIa and up.

 

This table is made available to clients via the Advanced Export.

 

 

ColumnCRM viewCRM columnNotesStandard Export nameType
RelationshipGUIDCustomerRelationcustomerrelationshipidprimary key uniqueidentifier
PersonGUIDCustomerRelationcustomeridstudent's primary key uniqueidentifier
StudentRoleGUIDCustomerRelationcustomerroleid uniqueidentifier
StudentRoleCustomerRelationcustomerroleidname Relationship_0N_Role1nvarchar(100)
RelationRoleGUIDCustomerRelationpartnerroleid uniqueidentifier
RelationRoleCustomerRelationpartnerroleidname Relationship_0N_Role2nvarchar(100)
RelatedPersonGUIDCustomerRelationpartneridrelated person's primary key uniqueidentifier
SalutationContactSalutation Relationship_0N_Salutationnvarchar(100)
FirstNameContactFirstName Relationship_0N_FirstNamenvarchar(100)
MiddleNameContactMiddleName Relationship_0N_MiddleNamenvarchar(100)
LastNameContactLastName Relationship_0N_LastNamenvarchar(100)
NameSuffixContactNameSuffix Relationship_0N_NameSuffixnvarchar(100)
NickNameContactNickName Relationship_0N_NickNamenvarchar(100)
HomePhoneContactTelephone2 Relationship_0N_HomePhonenvarchar(100)
MobilePhoneContactMobilePhone Relationship_0N_MobilePhonenvarchar(100)
EmailContactEMailAddress1 Relationship_0N_Emailnvarchar(100)
BusinessPhoneContactTelephone1 Relationship_0N_BusinessPhonenvarchar(100)
PersonRoleContacta422_rel_roleidName Relationship_0N_PersonRolenvarchar(100)
JobTitleContactJobTitle nvarchar(100)
DeceasedYesNoContacta422_deceased Relationship_0N_DeceasedYesNobit
School_PersonOrgGUIDA422_PersonOrga422_personorgidsubrecord's primary key uniqueidentifier
School_OrganizationGUIDA422_PersonOrga422_organizationid uniqueidentifier
School_OrganizationNameA422_PersonOrga422_organizationidname Person Organizationnvarchar(160)
School_DegreeDiplomaGUIDA422_PersonOrga422_rel_degreediplomaid uniqueidentifier
School_DegreeDiplomaA422_PersonOrga422_rel_degreediplomaidname Person Organizationnvarchar(100)
Address_Street1ContactAddress1_Line1 Relationship_0N_Street1nvarchar(100)
Address_Street2ContactAddress1_Line2 Relationship_0N_Street2nvarchar(100)
Address_Street3ContactAddress1_Line3 nvarchar(100)
Address_CityContactAddress1_City Relationship_0N_Citynvarchar(100)
Address_StateProvinceContactAddress1_StateOrProvince Relationship_0N_StateProvincenvarchar(100)
Address_PostalCodeContactAddress1_PostalCode Relationship_0N_PostalCodenvarchar(100)
Address_CountryRegionContactAddress1_Country Relationship_0N_CountryRegionnvarchar(100)
createdbyCustomerRelationcreatedby uniqueidentifier
createdbynameCustomerRelationcreatedbyname nvarchar(160)
createdonutcCustomerRelationcreatedonutc datetime
modifiedbyCustomerRelationmodifiedby uniqueidentifier
modifiedbynameCustomerRelationmodifiedbyname nvarchar(160)
modifiedonutcCustomerRelationmodifiedonutcrelationship record's modification datedatetime
RelatedPersonModifiedOnUTCContactmodifiedonutcrelated person's modification datedatetime
ordinaln/an/asort order int

 

 


Comments