Primary Key Field Type
 ID uniqueidentifier

Field Field Type Null Notes Description
 OBJECTIVE nvarchar(100)   Default = ''
 EXPECTEDDATE datetime  
 ACTUALDATE datetime
 DATE datetime (Computed) coalesce(ACTUALDATE,EXPECTEDDATE)
 STATUSCODE tinyint   Default = 0 0=Planned, 1=Pending, 2=Completed, 3=Unsuccessful, 4=Cancelled, 5=Declined
 COMMENT nvarchar(max)   Default = ''
 ISINTERACTION int (Computed)   case when INTERACTIONTYPECODEID is null then 0 else 1 end
 DATEADDED datetime   Default = getdate() Indicates the date this record was added.
 DATECHANGED datetime   Default = getdate() Indicates the date this record was last changed.
 TS timestamp   Timestamp.
 TSLONG bigint (Computed) CONVERT(bigint, TS) Numeric representation of the timestamp.
 STATUS nvarchar(12) (Computed) CASE [STATUSCODE] WHEN 0 THEN N'Planned' WHEN 1 THEN N'Pending' WHEN 2 THEN N'Completed' WHEN 3 THEN N'Unsuccessful' WHEN 4 THEN N'Cancelled' WHEN 5 THEN N'Declined' END Provides a translation for the 'STATUSCODE' field.
 COMPLETED int (Computed)   case when STATUSCODE in (2,3,4,5) then 1 else 0 end
 CUSTOMIDENTIFIER nvarchar(100)   Default = '' User-definable custom identifier.
 SEQUENCEID int   Identity column used to increment the default lookupid.
 LOOKUPID nvarchar(100) (Computed) (CASE LEN(CUSTOMIDENTIFIER) WHEN 0 THEN '8-' + CAST(SEQUENCEID AS nvarchar(20)) ELSE CUSTOMIDENTIFIER END) Unique identifer that supports user defined values as well as system generated values.
 ISCONTACTREPORT int (Computed)   case when INTERACTIONTYPECODEID is not null and PROSPECTPLANID is not null and STATUSCODE = 2 and len(COMMENT) > 0 then 1 else 0 end An interaction is a contact report when it has a contact method, is a plan step, is completed, and has a comment
 EXPECTEDSTARTTIME UDT_HOURMINUTE   Default = '' Expected starting time of the interaction.
 EXPECTEDENDTIME UDT_HOURMINUTE   Default = '' Expected ending time of the interaction.
 ISALLDAYEVENT bit   Default = 1 Flags this interaction as all day.
 ACTUALSTARTTIME UDT_HOURMINUTE   Default = '' Actual starting time of the interaction.
 ACTUALENDTIME UDT_HOURMINUTE   Default = '' Actual ending time of the interaction.
 STARTTIME char(4) (Computed) coalesce((case when ACTUALSTARTTIME = '' then null else ACTUALSTARTTIME end),EXPECTEDSTARTTIME)
 ENDTIME char(4) (Computed) coalesce((case when ACTUALENDTIME = '' then null else ACTUALENDTIME end) ,EXPECTEDENDTIME)
 EXPECTEDSTARTDATETIME datetime (Computed) dateadd(mi, convert(tinyint, substring(EXPECTEDSTARTTIME, 3, 2)), dateadd(hh, convert(tinyint, substring(EXPECTEDSTARTTIME, 1, 2)), convert(datetime, convert(date, EXPECTEDDATE)))) The expected start date and time of the interaction.
 EXPECTEDENDDATETIME datetime (Computed) dateadd(mi, convert(tinyint, substring(EXPECTEDENDTIME, 3, 2)), dateadd(hh, convert(tinyint, substring(EXPECTEDENDTIME, 1, 2)), convert(datetime, convert(date, EXPECTEDDATE)))) The expected end date and time of the interaction.
 ACTUALSTARTDATETIME datetime (Computed) dateadd(mi, convert(tinyint, substring(ACTUALSTARTTIME, 3, 2)), dateadd(hh, convert(tinyint, substring(ACTUALSTARTTIME, 1, 2)), convert(datetime, convert(date, ACTUALDATE)))) The Actual start date and time of the interaction.
 ACTUALENDDATETIME datetime (Computed) dateadd(mi, convert(tinyint, substring(ACTUALENDTIME, 3, 2)), dateadd(hh, convert(tinyint, substring(ACTUALENDTIME, 1, 2)), convert(datetime, convert(date, ACTUALDATE)))) The Actual end date and time of the interaction.

Foreign Key Field Type Null Notes Description
 CONSTITUENTID uniqueidentifier   CONSTITUENT.LOCALID FK to CONSTITUENT
 PROSPECTPLANID uniqueidentifier PROSPECTPLAN.ID FK to PROSPECTPLAN
 FUNDRAISERID uniqueidentifier CONSTITUENT.LOCALID Indicates which fundraiser should be responsible for this interaction.
 PLANOUTLINESTEPID uniqueidentifier PLANOUTLINESTEP.ID FK to PLANOUTLINESTEP
 INTERACTIONTYPECODEID uniqueidentifier INTERACTIONTYPECODE.ID FK to INTERACTIONTYPECODE
 PROSPECTPLANSTATUSCODEID uniqueidentifier PROSPECTPLANSTATUSCODE.ID Indicates which stage of the plan this step belongs to.
 ADDEDBYID uniqueidentifier   CHANGEAGENT.ID FK to CHANGEAGENT.
 CHANGEDBYID uniqueidentifier   CHANGEAGENT.ID FK to CHANGEAGENT.
 EVENTID uniqueidentifier EVENT.ID Indicates that the interaction took place during a specific event.
 INTERACTIONSUBCATEGORYID uniqueidentifier INTERACTIONSUBCATEGORY.ID Classification of the interaction type.
 FUNDINGREQUESTID uniqueidentifier FUNDINGREQUEST.ID FK to FUNDINGREQUEST
 FUNDINGREQUESTSTAGECODEID uniqueidentifier FUNDINGREQUESTSTAGECODE.ID Indicates which stage of the request this step belongs to.
 FUNDINGREQUESTOUTLINESTEPID uniqueidentifier FUNDINGREQUESTOUTLINESTEP.ID FK to FUNDINGREQUESTOUTLINESTEP
 TIMEZONEENTRYID uniqueidentifier TIMEZONEENTRY.ID Time zone of meeting time.

Index Name Field(s) Unique Primary Clustered
 IX_INTERACTION_ACTUALDATE ACTUALDATE      
 IX_INTERACTION_CONSTITUENTID CONSTITUENTID      
 IX_INTERACTION_DATEADDED DATEADDED    
 IX_INTERACTION_DATECHANGED DATECHANGED      
 IX_INTERACTION_FUNDRAISERID FUNDRAISERID      
 IX_INTERACTION_ID ID      
 IX_INTERACTION_PROSPECTPLANID PROSPECTPLANID      
 PK_INTERACTION ID  
 UC_INTERACTION_LOOKUPID LOOKUPID    

Trigger Name Description
 TR_INTERACTION_AUDIT_ETLDELETEDID
 TR_INTERACTION_AUDIT_UPDATE
 TR_INTERACTION_AUDIT_DELETE
 TR_INTERACTION_TASYNC_I
 TR_INTERACTION_TASYNC_U
 TR_INTERACTION_TASYNC_D

Referenced by Field
 BATCHINTERACTION INTERACTIONID
 EXCHANGECALENDARITEM INTERACTIONID
 EXCHANGECALENDARITEMBATCH INTERACTIONID
 INTERACTIONADDITIONALFUNDRAISER INTERACTIONID
 INTERACTIONATTACHMENT INTERACTIONID
 INTERACTIONMEDIALINK INTERACTIONID
 INTERACTIONNOTE INTERACTIONID
 INTERACTIONPARTICIPANT INTERACTIONID
 INTERACTIONRESPONSE INTERACTIONID
 INTERACTIONSITE INTERACTIONID

Entity-Relationship diagram of this table (BATCHINTERACTION-PROSPECTPLAN)
Entity-Relationship diagram of this table (PROSPECTPLANSTATUSCODE-TIMEZONEENTRY)