A DAC is the Data Access Class that allows Acumatica to access data, typically from a database. In the example below, we will look at some special fields and why you may want to include them in your database table, and subsequently in your DAC. We also will look at some special attributes for these fields.
This DAC is simply called BBExampleDAC and resides in the Blog.Example namespace. Acumatica tends to use a base namespace of PX.Objects and then further segregates each module, such as PX.Objects.IN or PX.Objects.PO. Our customization project is called Blog, so the namespace defaults in as Blog. To keep our C# code organized, we will segregate our namespace Blog similarly. For examples, I’ll use Blog.Example, but like Acumatica, in future posts expect the IN module to be extended into Blog.IN and the PO module to be extended into Blog.PO.
Creating the Custom SQL Table
While we can create custom database tables from within Acumatica, I find it far faster and easier to create my custom tables via SQL using MS SQL Server Management Studio. The SQL code below created the table used for this example. My database for this example is AcumaticaBlog. My customization is called Blog, and this is for Brian’s Blog, so I will use BB to prefix my code. VAR’s have a prefix assigned to them, so make sure your customization prefix does not overlap with standard Acumatica tables or any VAR software you have installed.
USE [AcumaticaBlog]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BBExampleDAC](
[CompanyID] [int] NOT NULL,
[ExampleID] [int] IDENTITY(1,1) NOT NULL,
[ExampleCD] [nvarchar](30) NOT NULL,
[Descr] [nvarchar](256) NULL,
[CreatedByID] [uniqueidentifier] NOT NULL,
[CreatedByScreenID] [char](8) NOT NULL,
[CreatedDateTime] [datetime] NOT NULL,
[LastModifiedByID] [uniqueidentifier] NOT NULL,
[LastModifiedByScreenID] [char](8) NOT NULL,
[LastModifiedDateTime] [datetime] NOT NULL,
[tstamp] [timestamp] NOT NULL,
[NoteID] [uniqueidentifier] NOT NULL,
[DeletedDatabaseRecord] [bit] NOT NULL,
CONSTRAINT [BBExampleDAC_PK] PRIMARY KEY CLUSTERED
(
[CompanyID] ASC,
[ExampleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[BBExampleDAC] ADD DEFAULT ((0)) FOR [CompanyID]
GO
ALTER TABLE [dbo].[BBExampleDAC] ADD DEFAULT ((0)) FOR [DeletedDatabaseRecord]
GO
Creating the DAC
Look over the BBExampleDAC class below. I won’t explain all the parts again as that was an earlier post. However, I will hit some highlights and focus on the special fields and attributes. Also, note that two fields, CompanyID and DeletedDatabaseRecord, are in the database but not in the DAC definition. That is because these fields are very special and managed by Acumatica if they exist. Every DAC you define should have the CompanyID field to separate the data contained from other companies. The DeletedDatabaseRecord is not required unless you want to mark a record as deleted but retain it rather than completely removing the record from the database entirely.
using System;
using PX.Data;
namespace Blog.Example
{
[Serializable]
[PXCacheName("BBExampleDAC")]
public class BBExampleDAC : IBqlTable
{
#region ExampleID
[PXDBIdentity(IsKey = true)]
public virtual int? ExampleID { get; set; }
public abstract class exampleID :
PX.Data.BQL.BqlInt.Field<exampleID> { }
#endregion
#region ExampleCD
[PXDBString(30, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "Example ID")]
public virtual string ExampleCD { get; set; }
public abstract class exampleCD :
PX.Data.BQL.BqlString.Field<exampleCD> { }
#endregion
#region Descr
[PXDBString(256, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "Description")]
public virtual string Descr { get; set; }
public abstract class descr :
PX.Data.BQL.BqlString.Field<descr> { }
#endregion
#region CreatedByID
[PXDBCreatedByID()]
public virtual Guid? CreatedByID { get; set; }
public abstract class createdByID :
PX.Data.BQL.BqlGuid.Field<createdByID> { }
#endregion
#region CreatedByScreenID
[PXDBCreatedByScreenID()]
public virtual string CreatedByScreenID { get; set; }
public abstract class createdByScreenID :
PX.Data.BQL.BqlString.Field<createdByScreenID> { }
#endregion
#region CreatedDateTime
[PXDBCreatedDateTime()]
public virtual DateTime? CreatedDateTime { get; set; }
public abstract class createdDateTime :
PX.Data.BQL.BqlDateTime.Field<createdDateTime> { }
#endregion
#region LastModifiedByID
[PXDBLastModifiedByID()]
public virtual Guid? LastModifiedByID { get; set; }
public abstract class lastModifiedByID :
PX.Data.BQL.BqlGuid.Field<lastModifiedByID> { }
#endregion
#region LastModifiedByScreenID
[PXDBLastModifiedByScreenID()]
public virtual string LastModifiedByScreenID { get; set; }
public abstract class lastModifiedByScreenID :
PX.Data.BQL.BqlString.Field<lastModifiedByScreenID> { }
#endregion
#region LastModifiedDateTime
[PXDBLastModifiedDateTime()]
public virtual DateTime? LastModifiedDateTime { get; set; }
public abstract class lastModifiedDateTime :
PX.Data.BQL.BqlDateTime.Field<lastModifiedDateTime> { }
#endregion
#region Tstamp
[PXDBTimestamp()]
[PXUIField(DisplayName = "Tstamp")]
public virtual byte[] Tstamp { get; set; }
public abstract class tstamp :
PX.Data.BQL.BqlByteArray.Field<tstamp> { }
#endregion
#region NoteID
[PXNote()]
public virtual Guid? NoteID { get; set; }
public abstract class noteID :
PX.Data.BQL.BqlGuid.Field<noteID> { }
#endregion
}
}
In an earlier post, we saw the [PXDBInt] attribute used in its very simple form. You will see string fields decorated with the [PXDBString] attribute here. Notice that there are parameters passed into the attribute. The first parameter tells Acumatica how large to allow the field to be. The field in the database was created as nvarchar(X) where X is the value we want to assign into the attribute for field length. Also note the parameter for IsUnicode. This parameter is set to true because the database field is set to a unicode field type like nvarchar or nchar. Finally, notice the InputMask parameter. This parameter allows controlling the format of the field when presented to the user in the UI and restricts data entry to meet your desired data format.
Specifying the Key in the DAC
Look at the first field, ExampleID. Notice that while it is a nullable integer for the variable type, the attribute is [PXDBIdentity]. This field has been defined in the database as an identity field, meaning it will auto-increment as records are created. This unique value creates a distinct means to find the exact record referenced, so it is a highly efficient key. This is how fields such as the InventoryID in the InventoryItem DAC is defined, and that value will be stored in places like POLine and SOLine. The parameter IsKey explicitly defines the field as the key. IsKey can be used on other fields to create the unique combination of fields required to locate the exact record of interest, but you cannot combine use of a [PXDBIdentity] attribute AND other fields defined with IsKey within the same DAC. This is because [PXDBIdentity] is the “one true key” when it is used as the database prevents having any duplicates in that one single field.
It is worth noting here that Acumatica pairs up ID and CD values. The CD value should be defined as a friendly way of identifying the record. ID stands for IDENTIFIER and is the surrogate key that is used for efficient indexing and storing when linking tables in the database. CD stands for CODE and is the natural key that people would understand in the real world. For instance, the InventoryID is a sequential integer value, but the InventoryCD may be an intelligently designed value that means something to the user when they see it. We usually hide the ID from the user and display only the CD. It makes sense as you accept the idea, but basically we lie to the user by changing the label to read ID on the CD field. Example: The InventoryID field is hidden from the user. The InventoryCD field is exposed to the user. We label the InventoryCD field as “Inventory ID” because that would make the most sense to the user.
Creation
Three fields capture creation data. These fields are optional, but I find myself including them in every custom table.
- CreatedByID – Uses the [PXDBCreatedByID] attribute and captures the GUID of the user that created the record
- CreatedByScreenID – Uses the [PXDBCreatedByScreenID] attribute and indicates which Screen ID of Acumatica was used to create the record
- CreatedDateTime – Uses the [CreatedDateTime] attribute and indicates when the record was created
Modification
Three more fields capture the last modification of the record. As you will see, these fields mirror the Created fields to capture the same information when the record is modified. While you can record changes in an Audit feature of Acumatica, only the last save is stored directly in these fields. Similarly, only use of the Audit feature will save record changes for review if you need to know exactly what was changed, how many times it was changed, or who entered a specific value. In other words, when 3 people are responsible for Create, Modify, Modify activities, these fields will NOT tell you who is responsible for a specific value, and the 2nd person becomes completely invisible without enabling field level auditing.
- LastModifiedByID – Uses the [LastModifiedByID] attribute and captures the GUID of the user that changed the record
- LastModifiedByScreenID – Uses the [LastModifiedByScreenID] attribute and indicates which Screen ID of Acumatica was used to change the record
- LastModifiedDateTime – Uses the [LastModifiedDateTime] attribute and indicates when the record was changed
Optimistic or Pessimistic Locking
The tstamp field enforces your locking strategy. Since a record is loaded into memory when read and written back at a later time, Acumatica does not hold a record lock between the two events. Creating such a transaction block that would span time and user entry literally crashed the database on a legacy ERP system I supported 20 years ago. The problem was wide spread, as managing transaction scopes in that ERP software was one of the most common requests for help and sources of agony for new programmers to understand. With Acumatica, we simply need to understand who wins if 2 people try to save the same record.
Simply put, if the table and DAC contains the tstamp field, defined with the [PXDBTimestamp] Attribute, Acumatica will check the tstamp value and only update an unchanged record. In other words, first one to save wins! Without the field, there is no way to perform this validation, so the last one to save wins. By including this validation, Acumatica fails to save the update by the second user and provides a notice in the UI that the record could not be saved. Without the field, the first user saves the record and goes merrily on their way while the second user overwrites that data. Translation: Always use the tstamp field and declaration in the DAC unless you are absolutely certain that updates will not be a problem, such as a table that will be accessed only via Insert and Select, but never Update.
An Important Note
Last but not least is a very important field called NoteID. Decorated with the [PXNote] attribute, this special purpose field has many uses. As the name may imply, this is how Acumatica allows attaching notes to the record. However, it also may be used to loosely connect other data as this value is a completely unique identifier called a GUID. The odds of generating a matching GUID the second time are so astronomical that it is used globally, as in around the world, as a Globally Unique IDentifier.
The [PXSearchable] attribute of the NoteID field is extra special as well. Acumatica contains a search box at the top of the screen which is referred to as Universal Search. I’ve added a link on the words, but I suspect that I will write a simple explanation at some point in the future. Simply put, [PXSearchable] can be used to define fields in the DAC to be included in the Universal Search. This can be a powerful tool to help your users find data quickly, but overuse can generate so many irrelevant results for what the user really needs that the tool becomes virtually worthless to them. Translation: When the business case shows real value in making data universally searchable, [PXSearchable] provides a way to quickly bring that data to your user in a simple single box search.
The following example is from the InventoryItem DAC to make the part number, Description, and Base U/M searchable:
[PXSearchable(SM.SearchCategory.IN, "{0}: {1}", new Type[]
{ typeof(InventoryItem.itemType), typeof(InventoryItem.inventoryCD) },
new Type[] { typeof(InventoryItem.descr) },
NumberFields = new Type[] { typeof(InventoryItem.inventoryCD) },
Line1Format = "{0}{1}{2}", Line1Fields = new Type[] {
typeof(INItemClass.itemClassCD), typeof(INItemClass.descr), typeof(InventoryItem.baseUnit)
},
Line2Format = "{0}", Line2Fields = new Type[] { typeof(InventoryItem.descr)},
WhereConstraint = typeof(Where<Current<InventoryItem.itemStatus>, NotEqual<InventoryItemStatus.unknown>>)
)]