Primary Key Field Type
 ID uniqueidentifier

Field Field Type Null Notes Description
 ADVOCACYLOGID int   Default = 0 Advocacy Log ID Stored on Central Server
 ACTIONCLASSIFICATION nvarchar(50)   Default = ''
 LEGISID nvarchar(10)   Default = '' legislator ID
 TYPECODE tinyint   Default = 0 Message delivery Type
 TYPE nvarchar(7) (Computed) CASE [TYPECODE] WHEN 0 THEN N'EMAIL' WHEN 1 THEN N'FAX' WHEN 2 THEN N'WEBFORM' WHEN 3 THEN N'LETTER' END Provides a translation for the 'TYPECODE' field.
 FAILUREREASONID int FK to MESSAGEFAILUREREASON
 FAXCOST money   Default = 0 Fax Cost
 FROMADDRESS nvarchar(255)   Default = ''
 TOADDRESS nvarchar(2000)   Default = ''
 STATUSCODE tinyint   Default = 0 Message status
 REQUESTEDSERVER nvarchar(100)   Default = ''
 ORIGINALADVOCACYLOGID int   Default = 0 Original Advocacy Log ID Stored on Central Server
 ISINTERNATIONALFAX bit   Default = 0 Is this international fax number
 ISBILLABLE bit   Default = 0 Is this billable message delivery
 FVTRANSACTIONID uniqueidentifier TransactionID
 TESTFLAG bit   Default = 0 Is this test message
 SALUTATION nvarchar(100)   Default = ''
 MESSAGEHEADER nvarchar(255)   Default = ''
 MESSAGESALUTATION nvarchar(255)   Default = ''
 MESSAGESIGNATURE nvarchar(255)   Default = ''
 SUBJECTLINE nvarchar(255)   Default = ''
 MESSAGETEXT nvarchar(max)   Default = '' Content of the message
 EMAILLOGID uniqueidentifier Email log id from bbnc mail system
 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.
 TARGETNAME nvarchar(500)   Default = ''
 FEDERALDISTRICTCODE nvarchar(4)   Default = ''
 UPPERSTATEDISTRICTCODE nvarchar(5)   Default = ''
 LOWERSTATEDISTRICTCODE nvarchar(5)   Default = ''
 ISMESSAGETEXTCHANGED bit   Default = 0 Is Message Text Changed
 DISTRICTNAME nvarchar(100)   Default = ''
 DISTRICT nvarchar(13)   Default = ''
 ADDRESSLINE1 nvarchar(100)   Default = ''
 ADDRESSLINE2 nvarchar(100)   Default = ''
 STATE nvarchar(100)   Default = ''
 COUNTRY nvarchar(5)   Default = ''
 ZIP nvarchar(12)   Default = ''
 CITY nvarchar(50)   Default = ''
 ISSUBJECTCHANGED bit   Default = 0 Is Message Subject Changed
 SERVERDATEUPDATED datetime The last time that log status is updated
 STATUS nvarchar(10) (Computed) CASE [STATUSCODE] WHEN 0 THEN N'NEW' WHEN 1 THEN N'DELIVERED' WHEN 2 THEN N'FAILED' WHEN 3 THEN N'FAILOVER' WHEN 4 THEN N'PROCESSING' END Provides a translation for the 'STATUSCODE' field.

Foreign Key Field Type Null Notes Description
 ACTIONITEMID uniqueidentifier   ACTION_ITEM.ID ActionItemID
 CONSTITUENTID uniqueidentifier   CONSTITUENT.LOCALID ConstituentID
 SOURCECONSTITUENTID uniqueidentifier CONSTITUENT.LOCALID SourceConstituentID
 ADDEDBYID uniqueidentifier   CHANGEAGENT.ID FK to CHANGEAGENT.
 CHANGEDBYID uniqueidentifier   CHANGEAGENT.ID FK to CHANGEAGENT.
 SITECONTENTID int   SiteContent.ID SiteContentId

Index Name Field(s) Unique Primary Clustered
 IX_ADVOCACYLOG_CONSTITUENTID CONSTITUENTID      
 IX_ADVOCACYLOG_DATEADDED DATEADDED    
 IX_ADVOCACYLOG_DATECHANGED DATECHANGED      
 IX_ADVOCACYLOG_SOURCECONSTITUENTID SOURCECONSTITUENTID      
 PK_ADVOCACYLOG ID  
 UIX_ADVOCACYLOG_ADVOCACYLOGID ADVOCACYLOGID    

Trigger Name Description
 TR_ADVOCACYLOG_AUDIT_UPDATE
 TR_ADVOCACYLOG_AUDIT_DELETE

Entity-Relationship diagram of this table