Database Mapped vs Virtual Fields

Hi All,

Today want to share with you how Database vs Virtual fields are working in Acumatica.

You may notice that some of the fields are linked to DB, some of them not.The difference is in the Attributes are used on it – PXDB<Type>Attribute is mapped on database field, PX<Type>Attribute is not. So from user standpoint it is just prefix “DB” or “”.

In Acumatica there are many fields are mapped to database and most of them have a clone, that is not mapped. Here you can see list of most common attributes.

How does that work?

Reading Data
You know that all attributes are subscribed for Acumatica Row and Field level events. There is one event that is responsible for generation SQL statement – CommandPreparing.
So basically all DB fields are inherited from PXDBFieldAttribute that implements CommandPreparing event;

public virtual void CommandPreparing(PXCache sender, PXCommandPreparingEventArgs e)
{
    if (DatabaseFieldName != null)
    {
        e.BqlTable = _BqlTable;
        string tableName = e.Table == null ? _BqlTable.Name : e.Table.Name;
        e.FieldName = e.SqlDialect.quoteTableAndColumn(tableName, dbFieldName);
    }
    e.DataValue = e.Value;
    e.IsRestriction = e.IsRestriction || _IsKey;
    e.DataType = PXDbType.Bit;
    e.DataLength = 1;
}

The main part here is providing FieldName and DataType, If field name is not populated, than Acumatica will automatically skip that DAC field from select statement.
For example if you have “[PXDBString] Field1”, “[PXBool] Field2” and “[PXDBInt] Field3”,, than select statement will looks like “Select Field1, Field3 from DAC”.

So as you may understand virtual fields do not implement command preparing event handler at all.

For reading data, Acumatica uses RowSelecting event handler where SQL Data Reader still open and accessible.

public virtual void RowSelecting(PXCache sender, PXRowSelectingEventArgs e)
{
    if (e.Row != null) 
    {
        object dbValue = e.Record.GetValue(e.Position);
        sender.SetValue(e.Row, _FieldOrdinal, dbValue);
    }
    e.Position++;
}

As with CommandPreparing, all DB related fields implementing RowSelecting, but virtual fields skipping it.

Read/Write vs Read-Only Fields
Some of the fields just read data from database and never saves it back. Good example here is PXDBScalarAttribute, which is just sub-select. For sub-selects you cannot save anything, so your logic should be working only of reading.
To implement it you can use Operation property from PXCommandPreparingEventArgs. For example here you can see how PXDBScalarAttribute handling only select operations:

public override void CommandPreparing(PXCache sender, PXCommandPreparingEventArgs e)
{
    if ((e.Operation & PXDBOperation.Command) == PXDBOperation.Select && sender.BqlSelect != null)
    {
        ............
    }
}

Custom SQL Command
You remember I told you about FieldName in PXCommandPreparingEventArgs? That is very important property, as it goes directly to SQL Query, so you can put there custom operations.
As for example see, how PXDBScalarAttribute using it for generation sub-selects.

public override void CommandPreparing(PXCache sender, PXCommandPreparingEventArgs e)
{
    ............
    string fieldtext = BqlCommand.GetSingleField(field, sender.Graph, new List<Type>(), null, 
    BqlCommand.FieldPlace.Select); 
    e.FieldName = BqlCommand.SubSelect + function + "(" + 
    (String.IsNullOrEmpty(fieldtext) ? field.Name : fieldtext) + ") " + text + ")";b
    ...........
}

So you can add your own SQL commands on and that read data on CommandPreparing and that read it back on RowSelecting
Using that approach you can create own Database-related and Virtual fields.

Have a nice development!