Calculation of Field Values

You can use the predefined attributes PXFormula and PXUnboundFormula in data access classes (DACs) to calculate field values from other values of the same record. You can also calculate aggregate values for all detail records and assign an aggregate value to a field of the master record.

The attributes implement the RowInserted, RowUpdated, and RowDeleted event handlers to calculate aggregates. Also, the attributes use the FieldUpdated event handler for dependent fields. The PXFormula attribute defines the RowSelecting event handler to calculate values for unbound DAC fields. You do not have to define any event handlers; you only need to mark a field in the DAC with an attribute.

Calculating the Value of a Field from Other Fields of the Same Record

[PXFormula(
  typeof(DocTransaction.tranQty.Multiply<DocTransaction.unitPrice>))]
public virtual Decimal? ExtPrice { get; set; }
public abstract class extPrice : PX.Data.BQL.BqlDecimal.Field<extPrice> { }

This code sets ExtPrice to the product of TranQty and UnitPrice.

Calculating the Aggregate Value for a Field in the Parent Record from Multiple Fields of Child Records

[PXFormula(
  typeof(DocTransaction.tranQty.Multiply<DocTransaction.unitPrice>),
  typeof(SumCalc<Document.totalCost>))]
public virtual Decimal? ExtPrice { get; set; }
public abstract class extPrice : PX.Data.BQL.BqlDecimal.Field<extPrice> { }

[PXParent(typeof(SelectFrom<Document>.
  Where<Document.docType.IsEqual<DocTransaction.docType.FromCurrent>.
  And<Document.docNbr.IsEqual<DocTransaction.docNbr.FromCurrent>>>))]
public virtual string DocType { get; set; }
public abstract class docType : PX.Data.BQL.BqlString.Field<docType> { }

This code sets ExtPrice to the product of TranQty and UnitPrice, sums the ExtPrice values of all detail records, and assigns the result to the TotalCost field of the parent document.

Calculating the Aggregate Value for a Field in the Parent Record from One Field of Child Records

[PXFormula(
    null,
    typeof(SumCalc<Document.totalCost>))]
public virtual Decimal? ExtPrice { get; set; }
public abstract class extPrice : PX.Data.BQL.BqlDecimal.Field<extPrice> { }

This code sets the TotalCost field of the parent document to the sum of the ExtPrice values.

Calculating the Aggregate Value for a Field in the Parent Record Without Setting Field Values of Child Records

[PXUnboundFormula(
    typeof(DocTransaction.tranQty.Multiply<DocTransaction.unitPrice>),
    typeof(SumCalc<Document.totalCost>))]
public virtual decimal? ExtPrice
...

This code sets the TotalCost field of the parent document to the sum of the products of the TranQty and UnitPrice values. The attribute does not set the ExtPrice field.

Using Functions and Aggregate Functions

In the first parameter of the PXFormula and PXUnboundFormula attributes, you can specify an expression built of data fields, BQL constants, and the following BQL functions:

• Op1.Add<Op2> (fluent BQL) or Add<Op1, Op2> (traditional BQL), which returns the sum of two values
• Op1.Subtract<Op2> (fluent BQL) or Sub<Op1, Op2> (traditional BQL), which subtracts the second value from the first
• Op1.Multiply<Op2> (fluent BQL) or Mult<Op1, Op2> (traditional BQL), which multiplies two values
• Op1.Divide<Op2> (fluent BQL) or Div<Op1, Op2> (traditional BQL), which divides the first value by the second
• Minus<>, which multiplies a value by –1
• Op1.When<Cond1>.Else<Op2>.When<Cond2>.[...] (fluent BQL) or Switch<Cases> (traditional BQL), which returns a value selected by a condition

In the second parameter of the PXFormula attribute, you can use one of the following aggregation functions:

• SumCalc<>, which calculates the total sum
• CountCalc<>, which counts the detail records
• MinCalc<>, which calculates the minimum values
• MaxCalc<>, which calculates the maximum value

The PXUnboundFormula attribute supports only SumCalc<> and MaxCalc<>.

Using a Field Value from the Parent Record

In the query of the PXFormula attribute, you can use the FromParent operator to obtain the value of a field from the parent record, as shown in the following code.

[PXFormula(typeof(SOShipment.shipmentType.FromParent.
  IsEqual<SOShipmentType.transfer>))]

Using a Field Value from the Record Referenced by the PXSelector Attribute

In the query of the PXFormula attribute, you can use the FromSelectorOf operator to obtain the value of a field from the record that is referenced by the PXSelector attribute, as shown in the following code.

[PXFormula(typeof(SOShipment.shipmentType.FromSelectorOf<shipmentNbr>.
  IsEqual<SOShipmentType.transfer>))]

Using a Field Value from the Setup Record

In the query of the PXFormula attribute, you can use the FromSetup operator to obtain the value of the field from the setup record, as shown below.

[PXFormula(typeof(SOSetup.autoReleaseIN.FromSetup.IsEqual<True>))]