Wednesday, 14 October 2015

Database Special Columns

Hi All,

Acumatica has some special database columns, like CompanyID, Branch, NoteID  and so on.
Here I will describe what types of special columns you may expect in Acumatica database and when do you need to use them.
All these field are optional and does not required to be in all tables.

In this article you will find Description of:
  • CompanyID 
  • CompanyMask
  • DeletedDatabaseRecord
  • BranchID
  • Tstamp
  • NoteID
  • GroupMask
  • And other Audit Fields:
    • Created/LastModifiedByID
    • Created/LastModifiedDatetime
    • Created/LastModifiedByScreenID
Welcome in the article if you need more details.

CompanyID – this is main tenant id field. Acumatica is multi-tenant application and this is done by splitting all data using Company ID column. Each row has own Company id. When framework selects data, from database, it ads special restriction by the company id. There is no way to change this behavior from customization, as it is low level system logic.
You always must have this column in database table if you want to use multi-tenancy in Acumatica. If you skip it, system will share data from this table between all tenants.
If you have CompanyID, it should always be a part of primary key and all indexes.

CompanyMask – stores special binary mask that defines where this record should be visible and where you can update this record. As I said before, Acumatica stores data in database separated by different tenants. All tenants are isolated, but you have some options how you can share data between them. To share date you database and table should meet some conditions:
  1. Your table should have CompanyID and CompanyMask columns. Without CompanyMask data will be completely separated with no way to read it from other tenant from code.
  2. CompanyMask should be configured to allow other tenants select and update this record. Mask contains two flags for all companies. First flag –  s it possible to select this record from particular company or not. Second flag – is it possible to update this record from particular company or not. So as more companies you have in database, as longer will be a company mask value.
  3. Companyes should be arranged in proper tries. In company table we have ParentID column, that stores hierarchy of companies. Only child companies can see records of parents companies. Parent companies not able to select date of child companies. Also companies from different chains not able to so get data of each other.
If you configured everything properly, several tenants can share the same data between each other.

DeletedDatabaseRecord – special flag, that marks one particular record as deleted. If you have this column in database, system will automatically handle deletion operation and instead of remove this record from database, system will just mark it as deleted. This is good practice for some sort of maintenance entities  you need to store it for references from old documents. Good example is InventororyItems/Customers/Vendors.
You always can read deleted data with PXReadDeletedScope class in your code.

BranchID – main field that stores branch identifier. If table and DAC has this field system will automatically handle branch id defaulting, access rights by branch and other things. Please note that with this field you have to add it in DAC with PXBranchAttribute to enable underlined logic.

Tstamp – is a version of record. This field is required for enabling optimistic transaction locking model. In this case before saving data system will check that version record version in database is the same as was read first time before modifications.

NoteID – is global and unique identifier of the record. This is guid value that represents the record in entire database. Using this value you can find required record in entire database and it is not mater in what table it is. This field is perfect for linking records together and storing references.
Also NoteID is used for linking notes/files/Activities to the record.
Please note, that you should have this field in database table and DAC with PXNoteAttribute as well to enable this logic.

GroupMask  is mask that stores information about different restriction groups where belongs this record. The idea is similar to Company mask, but in this case we want to restrict access to one particular record within one tenant. As many restriction groups you have in the system as longer this mask will be. On select operation system will check what groups is accessible for current user ans system will select only data that is visible to provided groups.

Audit Fields
Created/LastModified/ByID, Created/LastModified/ByScreenID Created/LastModified/DateTime – are audit fields. These field are completely optional and does not required for any system logic, however it can be very useful for audit purposes. Note that you should have these fields in database and DAC with appropriate attribute as well.

Have a nice development.


dangminhhuynh said...

Hi Sergey,
With Customization on each table that we can add these column to use feature like branch or groupmask

Sergey Marenich said...

Hi Dang,

The answer is depend on what do you want to achieve. Some of the columns should be added just to database (Like CompanyID, DeletedDatabaseRecord, CompanyMask), some should be added to DAC as well (Like Branch, GroupMaks, ...), some columns like group mask also require some configuration or additional customization.
IF you adding column to DAC you have to definition of appropriate attribute.

If we speak about group mask you have to add:
public abstract class groupMask : IBqlField { }
public virtual Byte[] GroupMask { get; set; }

To learn more about the way restriction groups are managed, see the documentation for the GL Account Access (GL.10.40.00) screen.

IF we speak about Branch, you have to add:
public abstract class branchID : PX.Data.IBqlField {}
public virtual Int32? BranchID { get; set; }

dangminhhuynh said...

I alreadly know it. But when in customization, Acumatica suggest must have prefix Usr for each DAC/ Database Field but I wonder if I can add BranchID into any table/DAC which is standard of Acumatica.

Sergey Marenich said...

Hi Dang,

Actually, in this particular situation, you don't need to follow the name convention.
The main thing here are Attributes (Branch or PXDBGroupMask).
So I think you can have any name of these columns, event with Usr prefix.