CustomFieldHistoryTracking

FieldHistoryTracker for Acumatica

Purpose: A lightweight, auditable change log for Acumatica UDF/Attribute values on Contracts and Invoices, optimized for fast lookups and “current state” queries.

---

✨ Highlights

• Normalized history table with typed value columns (Text, Code, Decimal).
• Active-state auto-maintenance via triggers (ensures the latest row per entity/field stays Active = 1).
• Hash-bucketed primary key (FieldCodeHash) for even index distribution and predictable query plans.
• Focused indices for common filters: by date, by user, by key, by value, and by field code.
• Plug-in style: Extend with more sources by following the same trigger pattern.

---

🧱 Objects Created

Table: AcumaticaDB.dbo.FieldHistoryTracker

Stores the full history of selected fields/attributes. One row per change.

Primary Key

• PK(FieldCodeHash, ID) — ID is identity; FieldCodeHash = abs(checksum(FieldCode)) % 10 (persisted) spreads rows across 10 buckets.

Uniqueness

• UQ(FieldCodeHash, FieldCode, CompanyID, ID) safeguards accidental duplication.

Columns (selected)

Column	Type	Notes
ID	int IDENTITY	Surrogate key (with hash in PK)
CompanyID	int	Tenant/company context
FieldCode	nvarchar(50)	Attribute ID (e.g., JOBSTATUS, INPROG, DISP)
FieldCodeHash	computed persisted	abs(checksum(FieldCode)) % 10
KeyType	nvarchar(50)	Logical entity (e.g., Contract, Invoice)
RelatedKey	nvarchar(50)	Entity key (ContractCD, RefNbr, etc.)
UDF	bit	1 if user-defined field
Active	bit	1 for the most recent effective row for that (CompanyID, FieldCode, RelatedKey)
Import	bit	1 if system/import action (skip deactivation logic)
FieldDescription	nvarchar(255)	Friendly title from CSAttribute
FieldValueCode	nvarchar(50)	Value ID from CSAttributeDetail.ValueID
FieldValueText	nvarchar(200)	Value label from CSAttributeDetail.Description
FieldValueDecimal	decimal(18,6)	Numeric payload when relevant
AppliedDate	datetime	Effective time of the change
AppliedByUserID	uniqueidentifier	Source user PK
AppliedByUserName	nvarchar(100)	Source user name (denormalized)
LastModifiedDateTime	datetime	Mirrors source entity modify time
LastModifiedByName	nvarchar(100)	Denormalized
LastModifiedByID	uniqueidentifier	Denormalized

Nonclustered Indexes

• IX_FieldHistoryTracker_Active_UDF(FieldCodeHash, CompanyID, Active, UDF, AppliedDate)
• IX_FieldHistoryTracker_AppliedByUser(FieldCodeHash, AppliedByUserID, AppliedByUserName)
• IX_FieldHistoryTracker_AppliedByUser_Active(FieldCodeHash, AppliedByUserID, Active, AppliedDate)
• IX_FieldHistoryTracker_AppliedDate(FieldCodeHash, AppliedDate)
• IX_FieldHistoryTracker_FieldCodeHash(FieldCodeHash, FieldCode)
• IX_FieldHistoryTracker_FieldCodeValueType(FieldCodeHash, FieldCode, FieldValueCode)
• IX_FieldHistoryTracker_FieldValue(FieldCodeHash, FieldValueText, AppliedDate, AppliedByUserID, AppliedByUserName, Active, UDF)
• IX_FieldHistoryTracker_KeyType_RelatedKey(FieldCodeHash, KeyType, RelatedKey)

Why hash-bucket? Many queries start with FieldCode, so hashing keeps the leading key selective and stabilizes plans under skewed attribute usage.

---

⚙️ Triggers & Flow

1) trg_AfterInsert_JobSTATUS_FieldHistoryTracker

Scope: After Insert on FieldHistoryTracker

Goal: For JOBSTATUS, INPROG, DISP updates, set all previous active rows for the same (CompanyID, RelatedKey, FieldCode) to Active = 0, unless the new row was created with Import = 1.

Effect: Guarantees a single Active = 1 record per field/key, making “current status” queries trivial and fast.

2) _trg_Attention_After_Update_Insert_ContractKvExt

Source: ContractKvExt (after UPDATE, INSERT)

Behavior: When a Contract attribute in {JOBSTATUS, INPROG} changes, insert a row into FieldHistoryTracker. Values are materialized from CSAttributeDetail (ValueID → Description). User info and timestamps are copied from Contract.

3) _trg_Attention_After_Update_Insert_InvoiceKvExt

Source: ARRegisterKvExt (after UPDATE, INSERT)

Behavior: When an Invoice attribute in {DISP} changes, insert a row into FieldHistoryTracker. Same materialization and denormalization strategy as the Contract trigger.

flowchart LR
    A[ContractKvExt / ARRegisterKvExt] -->|Insert/Update| B{Attribute in scope?}
    B -- No --> C[Ignore]
    B -- Yes --> D[Insert row into FieldHistoryTracker]
    D --> E[After Insert Trigger]
    E -->|Deactivate old rows for same key/field| F[Latest row Active=1]

---

🚀 Install

1. Open SQL Server Management Studio against the Acumatica DB.

2. Run the provided script in order:

   • Create table
   • Create indexes
   • Create triggers (3x)

3. Verify the referenced Acumatica objects exist:

   • ContractKvExt, ARRegisterKvExt, Contract, CSAttribute, CSAttributeDetail, Users.

4. Adjust attribute scope if needed (see Configuration below).

The script uses ONLINE = OFF for index creation. If you’re on Enterprise Edition and need hot indexing, flip to ONLINE = ON where supported.

---

🔧 Configuration

• Tracked Attributes

  • Contracts: JOBSTATUS, INPROG
  • Invoices: DISP

• To add attributes:

  • Extend the WHERE cd.AttributeID IN (...) filters in the triggers.

• To track new entities (e.g., SOOrder, PMTask):

  • Create a similar AFTER INSERT/UPDATE trigger on the corresponding *KvExt table.
  • Populate KeyType (e.g., SOOrder) and RelatedKey (e.g., OrderNbr).

---

📊 Common Queries

Current status for a Contract

SELECT TOP (1) *
FROM AcumaticaDB.dbo.FieldHistoryTracker
WHERE CompanyID   = @CompanyID
  AND KeyType     = 'Contract'
  AND RelatedKey  = @ContractCD
  AND FieldCode   = 'JOBSTATUS'
  AND Active      = 1
ORDER BY AppliedDate DESC;  -- defensive

Full history for a Contract

SELECT *
FROM AcumaticaDB.dbo.FieldHistoryTracker
WHERE CompanyID  = @CompanyID
  AND KeyType    = 'Contract'
  AND RelatedKey = @ContractCD
  AND FieldCode  IN ('JOBSTATUS','INPROG')
ORDER BY AppliedDate DESC;

Invoices currently in DISP status

SELECT CompanyID, RelatedKey AS RefNbr, FieldValueCode, FieldValueText, AppliedDate
FROM AcumaticaDB.dbo.FieldHistoryTracker
WHERE KeyType = 'Invoice'
  AND FieldCode = 'DISP'
  AND Active = 1;

Changes by user in a time window

SELECT *
FROM AcumaticaDB.dbo.FieldHistoryTracker
WHERE AppliedByUserID = @UserPK
  AND AppliedDate >= @FromDate AND AppliedDate < @ToDate
ORDER BY AppliedDate DESC;

Find by friendly value text (label)

SELECT *
FROM AcumaticaDB.dbo.FieldHistoryTracker
WHERE FieldCode = 'JOBSTATUS'
  AND FieldValueText = 'Closed'
ORDER BY AppliedDate DESC;

Tip: When filtering by FieldCode, include a sargable predicate on FieldCodeHash if you hardcode it, e.g., AND FieldCodeHash = abs(checksum('JOBSTATUS')) % 10. Otherwise, the nonclustered index ...FieldCodeHash(FieldCodeHash, FieldCode) still helps.

---

🧪 Data Integrity Notes

• The after-insert deactivation respects Import = 1. System/import loads won’t flip Active on prior rows.
• AppliedDate comes from the source entity modify time to reflect business-effective time, not the trigger’s GETDATE().
• Value denormalization (FieldValueCode, FieldValueText) is taken from CSAttributeDetail at the time of change.

---

📈 Performance Guidance

• Add covering includes to indices if your reporting queries need more columns without key lookups.
• Keep the hash bucket count (% 10) consistent. If data grows very large or skewed, consider changing the modulus (requires rebuild) or partitioning by date.
• For large historical queries, prefer bounded date ranges and filter by CompanyID, KeyType, and FieldCode.

---