acumatica Acumatica Platform Attributes Reference PXFormula Attribute

Example

General Description

A formula in Acumatica is a DAC field that is calculated based on the values of other object fields.

To calculate a formula, Aсumatiсa framework provides a set of various operations and functions (such as arithmetical, logical, and comparison operations and string processing functions; see List of Built-In Common Formulas). In addition to the field values, a formula can use various constants provided by both the core of Acumatica and the application solutions. Moreover, a formula can obtain values for the calculation not only from the current record but also from other sources (see Formula Context and Its Modifiers).

The beauty of the formulas is that they will automatically recalculate the value at the right time:

• On field defaulting (inserting a new row; FieldDefaulting event handler of formula field)
• On updating of dependent fields (FieldUpdated event handler of each dependent field)
• On database selection (only for unbound fields; RowSelecting event handler)
• On database persisting if needed (developer should specify it explicitly; RowPersisted event handler)

Recalculation of a formula field value on the update of a dependent field raises a FieldUpdated event for formula field. This allows you to make a chain of dependent formulas (see Direct and Mediated Circular References in Formulas).

Application developers can write their own application-side formulas.

Modes of Usage

A formula can be used in three main modes:

• Simply calculating the value and assigning it to formula field (see Basic Usage)
• Calculating the aggregate value from existing values of formula fields and assigning it to specified field in the parent object (see Aggregate Usage)
• Mixed mode: Calculating the formula value, assigning it to the formula field, calculating the aggregate value, and assigning it to the field in the parent object (see Combined Usage)

There is another auxiliary mode, unbound formulas, that is very similar to mixed mode, but the calculated values of the formula are not assigned to the formula field. The aggregated value is calculated immediately and assigned to the field of the parent object. See Usage of Unbound Formulas for more information.

PXFormulaAttribute Properties and Constructor Parameters

The formula functionality is implemented by PXFormulaAttribute. The constructor of PXFormulaAttribute has the following signatures:

public PXFormulaAttribute(Type formulaType)
{
    // ...
}

The single parameter formulaType is a type of formula expression to calculate the field value from other fields of the same data record. This parameter must meet one of the following conditions:

• Must implement the IBqlField interface
• Must be a BQL constant
• Must implement the IBqlCreator interface (see List of Built-In Common Formulas)

public PXFormulaAttribute(Type formulaType, Type aggregateType)
{
    // ...
}

The first parameter, formulaType, is the same as in the first constructor. The second parameter, aggregateType, is a type of aggregation formula to calculate the parent data record field from the child data record fields. An aggregation function can be used, such as SumCalc, CountCalc, MinCalc, and MaxCalc. Application developers can create their own aggregation formulas.

An aggregate formula type must be a generic type and must implement IBqlAggregateCalculator interface. The first generic parameter of the aggregate formula type must implement the IBqlField interface and must have the field type of the parent object.

public virtual bool Persistent { get; set; }

The PXFormulaAttribute.Persistent property indicates whether the attribute recalculates the formula after changes are saved to the database. You may need recalculation if the fields the formula depends on are updated on the RowPersisting event. By default, the property equals false.

Usage

In most cases, formulas are used for direct calculation of the value of the formula field from other fields of the same data record.

The simplest example of formula usage:

[PXDBDate]
[PXFormula(typeof(FADetails.receiptDate))]
[PXDefault]
[PXUIField(DisplayName = Messages.PlacedInServiceDate)]
public virtual DateTime? DepreciateFromDate { get; set; }

In this example, the value of the ReceiptDate field is assigned to the DepreciateFromDate field on the insertion of a new record and on the update of the ReceiptDate field.

A slightly more complex example:

[PXCurrency(typeof(APPayment.curyInfoID), typeof(APPayment.unappliedBal))]
[PXUIField(DisplayName = "Unapplied Balance", Visibility = PXUIVisibility.Visible, Enabled = false)]
[PXFormula(typeof(Sub<APPayment.curyDocBal, APPayment.curyApplAmt>))]
public virtual Decimal? CuryUnappliedBal { get; set; }

Here, the unapplied balance of the document is calculated as the difference between the balance of the document and the applied amount.

Example of multiple choice with a default value:

[PXUIField(DisplayName = "Class Icon", IsReadOnly = true)]
[PXImage]
[PXFormula(typeof(Switch<
    Case<Where<EPActivity.classID, Equal<CRActivityClass.task>>, EPActivity.classIcon.task,
    Case<Where<EPActivity.classID, Equal<CRActivityClass.events>>, EPActivity.classIcon.events,
    Case<Where<EPActivity.classID, Equal<CRActivityClass.email>,
        And<EPActivity.isIncome, NotEqual<True>>>, EPActivity.classIcon.email,
    Case<Where<EPActivity.classID, Equal<CRActivityClass.email>,
        And<EPActivity.isIncome, Equal<True>>>, EPActivity.classIcon.emailResponse,
    Case<Where<EPActivity.classID, Equal<CRActivityClass.history>>, EPActivity.classIcon.history>>>>>,
    Selector<Current2<EPActivity.type>, EPActivityType.imageUrl>>))]
public virtual string ClassIcon { get; set; }

Order of Fields

The order of fields in the DAC is important to correct formula calculation. All source fields (from which the formula is calculated) including other formulas must be defined in the DAC before the formula field. Otherwise, the field can be calculated incorrectly or can cause a runtime error.

Formula Context and Its Modifiers

By default, the context of the formula calculation is restricted by the current object (record) of the class containing the formula declaration. It is also allowed to use constants (descendants of the Constant<> class).

A formula that uses the fields of its object only:

public partial class Contract : IBqlTable, IAttributeSupport
{
    //...
    [PXDecimal(4)]
    [PXDefault(TypeCode.Decimal, "0.0", PersistingCheck = PXPersistingCheck.Nothing)]
    [PXFormula(typeof(Add<Contract.pendingRecurring, Add<Contract.pendingRenewal, Contract.pendingSetup>>))]
    [PXUIField(DisplayName = "Total Pending", Enabled=false)]
    public virtual decimal? TotalPending { get; set; }
    //...
}

However, it is possible to obtain input values for the formula calculation from other sources:

• A current record of any cache in the BLC (if assigned).
• A foreign record specified by PXSelectorAttribute.
• A parent record specified by PXParentAttribute.

The formula supports the following context modifiers.

Current<TRecord.field> and Current2<TRecord.field>

Fetches the field value from the record stored in the Current property of the TRecord cache.

If the cache's Current property or the field itself contains null:

• Current<> forces field defaulting and returns the default field value.
• Current2<> returns null.

Example:

[PXFormula(typeof(Switch<
    Case<Where<
        ARAdjust.adjgDocType, Equal<Current<ARPayment.docType>>,
        And<ARAdjust.adjgRefNbr, Equal<Current<ARPayment.refNbr>>>>,
        ARAdjust.classIcon.outgoing>,
    ARAdjust.classIcon.incoming>))]
protected virtual void ARAdjust_ClassIcon_CacheAttached(PXCache sender)

Parent<TParent.field>

Fetches the field value from the parent data record as defined by PXParentAttribute residing on the current DAC.

public class INTran : IBqlTable
{
    [PXParent(typeof(Select<
        INRegister, 
        Where<
            INRegister.docType, Equal<Current<INTran.docType>>,
            And<INRegister.refNbr,Equal<Current<INTran.refNbr>>>>>))]
    public virtual String RefNbr { ... }
  
    [PXFormula(typeof(Parent<INRegister.origModule>))]
    public virtual String OrigModule { ... }
}

IsTableEmpty<TRecord>

Returns true if the DB table corresponding to the specified DAC contains no records, false otherwise.

public class APRegister : IBqlTable
{
    [PXFormula(typeof(Switch<
        Case<Where<
            IsTableEmpty<APSetupApproval>, Equal<True>>,
            True,
        Case<Where<
            APRegister.requestApproval, Equal<True>>,
            False>>,
        True>))]
    public virtual bool? DontApprove { get; set; }
}

Selector<KeyField, ForeignOperand>

Fetches a PXSelectorAttribute defined on the foreign key field (KeyField) of the current DAC.

Fetches the foreign data record currently referenced by the selector.

Calculates and returns an expression on that data record as defined by ForeignOperand.

public class APVendorPrice : IBqlTable
{
    // Note: inventory attribute is an
    // aggregate containing a PXSelectorAttribute
    // inside, which is also valid for Selector<>.
    // -
    [Inventory(DisplayName = "Inventory ID")]
    public virtual int? InventoryID
  
    [PXFormula(typeof(Selector<
        APVendorPrice.inventoryID, 
        InventoryItem.purchaseUnit>))]
    public virtual string UOM { get; set; }
}

Using Formulas on Unbound Fields

If the formula field is an unbound field marked with one of the PXFieldAttribute descendants (such as PXIntAttribute or PXStringAttribute), then its calculation is additionally triggered during RowSelecting event.

List of Built-In Common Formulas

TBD

Direct and Mediated Circular References in Formulas

TBD

Control Flow in Conditional Formulas

TBD

Using Multiple Formulas on One Field

TBD