CustomFieldHistoryTracking/FieldTrackingTable.sql

CREATE TABLE AcumaticaDB.dbo.FieldHistoryTracker (
	ID int IDENTITY(1,1) NOT NULL,
	CompanyID int NOT NULL,
	FieldCode nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	FieldCodeHash AS (abs(checksum([FieldCode]))%(10)) PERSISTED NOT NULL,
	KeyType nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	RelatedKey nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	UDF bit DEFAULT 0 NOT NULL,
	Active bit DEFAULT 0 NOT NULL,
	Import bit DEFAULT 0 NULL,
	FieldDescription nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	FieldValueCode nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	FieldValueText nvarchar(200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	FieldValueDecimal decimal(18,6) NULL,
	AppliedDate datetime NOT NULL,
	AppliedbyUserID uniqueidentifier NULL,
	AppliedbyUserName nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	LastModifiedDateTime datetime NOT NULL,
	LastModifiedByName nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	LastModifiedByID uniqueidentifier NULL,
	CONSTRAINT PK__Watterso__D435033A914A3CF4 PRIMARY KEY (FieldCodeHash,ID),
	CONSTRAINT UQ__Watterso__47317D7CF73A082D UNIQUE (FieldCodeHash,FieldCode,CompanyID,ID)
);
 CREATE NONCLUSTERED INDEX IX_FieldHistoryTracker_Active_UDF ON AcumaticaDB.dbo.FieldHistoryTracker (  FieldCodeHash ASC  , CompanyID ASC  , Active ASC  , UDF ASC  , AppliedDate ASC  )  
	 WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
	 ON [PRIMARY ] ;
 CREATE NONCLUSTERED INDEX IX_FieldHistoryTracker_AppliedByUser ON AcumaticaDB.dbo.FieldHistoryTracker (  FieldCodeHash ASC  , AppliedbyUserID ASC  , AppliedbyUserName ASC  )  
	 WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
	 ON [PRIMARY ] ;
 CREATE NONCLUSTERED INDEX IX_FieldHistoryTracker_AppliedByUser_Active ON AcumaticaDB.dbo.FieldHistoryTracker (  FieldCodeHash ASC  , AppliedbyUserID ASC  , Active ASC  , AppliedDate ASC  )  
	 WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
	 ON [PRIMARY ] ;
 CREATE NONCLUSTERED INDEX IX_FieldHistoryTracker_AppliedDate ON AcumaticaDB.dbo.FieldHistoryTracker (  FieldCodeHash ASC  , AppliedDate ASC  )  
	 WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
	 ON [PRIMARY ] ;
 CREATE NONCLUSTERED INDEX IX_FieldHistoryTracker_FieldCodeHash ON AcumaticaDB.dbo.FieldHistoryTracker (  FieldCodeHash ASC  , FieldCode ASC  )  
	 WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
	 ON [PRIMARY ] ;
 CREATE NONCLUSTERED INDEX IX_FieldHistoryTracker_FieldCodeValueType ON AcumaticaDB.dbo.FieldHistoryTracker (  FieldCodeHash ASC  , FieldCode ASC  , FieldValueCode ASC  )  
	 WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
	 ON [PRIMARY ] ;
 CREATE NONCLUSTERED INDEX IX_FieldHistoryTracker_FieldValue ON AcumaticaDB.dbo.FieldHistoryTracker (  FieldCodeHash ASC  , FieldValueText ASC  , AppliedDate ASC  , AppliedbyUserID ASC  , AppliedbyUserName ASC  , Active ASC  , UDF ASC  )  
	 WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
	 ON [PRIMARY ] ;
 CREATE NONCLUSTERED INDEX IX_FieldHistoryTracker_KeyType_RelatedKey ON AcumaticaDB.dbo.FieldHistoryTracker (  FieldCodeHash ASC  , KeyType ASC  , RelatedKey ASC  )  
	 WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
	 ON [PRIMARY ] ;