Creating Acumatica DAC Extension Tables

Most of us have done it at one time or another.  The client needs a new field on the screen, the consultants provide the info, and you create a UDF on a standard Acumatica table.  Job done.  But, for whatever reason, the field goes unused or was perhaps created in error.  Unless you intervene with a SQL script to drop the column, this unused UDF field will probably just exist in SOOrder or SOLine for all time going forward.  I find that very frustrating and is another very good reason to always have a test instance of Acumatica!  But that’s the subject of a different post. In this article, I want to discuss the merits of creating custom fields differently by creating DAC Extension Tables instead of creating individual UDF fields in a standard Acumatica table.

A DAC Extension Table is a separate SQL table that Acumatica automatically maps to a standard table (ex: SOOrder) either via a Left Join or an Inner Join depending on the configuration.  The DAC Extension Table has the same key fields as the standard table in addition to however many UDF fields you care to create.  Acumatica automatically creates an entry in your DAC Extension Table either whenever you need it or for every record in the joined Acumatica table depending on configuration. In my mind, this is a much better way to add information that is directly linked to standard Acumatica Tables.  A few important things to note when creating your own DAC Extension Table:

1. The SQL table should have the same main key columns as the standard tables (these do not need to be defined in the DAC however)
2. The SQL table must contain the CompanyID and DeletedDatabaseRecord fields
3. You must have the PXTable attribute attached to your DAC Extension table DAC. The isOptional parameter dictates whether Acumatica maps your table with an Inner or Left Outer Join.
4. You do not have to use the ‘Usr’ prefix that is required when creating a standard UDF, instead, you should use a prefix indicating its author.
5. You may use the standard audit column types to add metadata to your table (tstamp, CreatedByID, etc). However, if the standard table contains these audit fields, make sure you avoid name collisions.

Here’s a code example for you:

GIST: https://gist.github.com/patrick711/8cde1aaf8da0069d75a59626edd6fdb9

Good luck and happy coding!