Show unbound field with PXFormula

Question

I'm trying to show a value calculated with PXFormula but the field doesn't show the value. I have my CustomDAC named EDITran

  public class EDITran : IBqlTable
  {

    #region Doctype
    [PXDBString(50, IsKey = true, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Doctype")]

    [PXStringList
      (new string[]
      {"SO", "SHI", "INV" },
      new string[]
      {"Sales Order", "Shipment", "Invoice"}
      )]

    public virtual string Doctype { get; set; }
    public abstract class doctype : PX.Data.BQL.BqlString.Field<doctype> { }
    #endregion

    #region Erprefnbr
    [PXDBString(30, IsKey = true, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "ERP RefNbr")]
    public virtual string Erprefnbr { get; set; }
    public abstract class erprefnbr : PX.Data.BQL.BqlString.Field<erprefnbr> { }
    #endregion

    #region Sync
    [PXDBBool()]
    [PXUIField(DisplayName = "Sync")]
    public virtual bool? Sync { get; set; }
    public abstract class sync : PX.Data.BQL.BqlBool.Field<sync> { }
    #endregion
  }
}

so I what to show the value of sync field on the Sales Order screen. The key is the ERP RefNbr (which would be SOOrder.OrderNbr)

I have added the custom non persisted field on the SOOrderExt DAC with this attributes

using PX.Objects.SO;

[PXBool]
[PXUIField(DisplayName="EDI Sync" , Enabled = false)]
[PXFormula(typeof(Selector<SOOrder.orderNbr,
              Selector<EDITran.erprefnbr,
              EDITran.sync>>))]

But when I added a record in EDITran and try to visualize it in SOOrder Form and I checked that EDITran.Sync = 1, it doesn't show the saved value.

Sales Order Screen

What I'm I doing wrong? Is the PXFormula correctly used?

Answer 1

PXFormula used incorrectly. PXFormula attribute works only with current DAC (which is SOOrder) or foreign DAC, existing in PXSelector join condition (you can use Selector keyword to get it). For example, here is the SOOrder.OrderNbr selector declaration

[SO.RefNbr(typeof(Search2<SOOrder.orderNbr,
    LeftJoinSingleTable<Customer, On<SOOrder.customerID, Equal<Customer.bAccountID>,
        And<Where<Match<Customer, Current<AccessInfo.userName>>>>>>,
    Where<SOOrder.orderType, Equal<Optional<SOOrder.orderType>>,
        And<Where<Customer.bAccountID, IsNotNull,
            Or<Exists<Select<SOOrderType,
                Where<SOOrderType.orderType, Equal<SOOrder.orderType>,
                    And<SOOrderType.aRDocType, Equal<ARDocType.noUpdate>,
                    And<SOOrderType.behavior, Equal<SOBehavior.sO>>>>>>>>>>,
        OrderBy<Desc<SOOrder.orderNbr>>>), Filterable = true)]
public virtual String OrderNbr

you can get some fields from the related Customer record, using Selector keyword

[PXFormula(typeof(Selector<
    SOOrder.orderNbr,
    Customer.consolidateStatements>))]

As a result, there are two possible solutions:

1) Rewrite SOOrder.OrderNbr selector declaration with your EDITran DAC

...
[SO.RefNbr(typeof(Search2<SOOrder.orderNbr,
    LeftJoinSingleTable<Customer, On<SOOrder.customerID, Equal<Customer.bAccountID>,
        And<Where<Match<Customer, Current<AccessInfo.userName>>>>>,
    LeftJoin<EDITran, On<EDITran.doctype, Equal<SOOrder.orderType>, 
        And<EDITran.erprefnbr, Equal<SOOrder.orderNbr>>>>>,
    Where<SOOrder.orderType, Equal<Optional<SOOrder.orderType>>,
        And<Where<Customer.bAccountID, IsNotNull,
            Or<Exists<Select<SOOrderType,
                Where<SOOrderType.orderType, Equal<SOOrder.orderType>,
                    And<SOOrderType.aRDocType, Equal<ARDocType.noUpdate>,
                    And<SOOrderType.behavior, Equal<SOBehavior.sO>>>>>>>>>>,
        OrderBy<Desc<SOOrder.orderNbr>>>), Filterable = true)]
public virtual String OrderNbr

Then it will be possible to get you field from there

[PXFormula(typeof(Selector<
    SOOrder.orderNbr,
    EDITran.sync>))]

2) Use PXDBScalar attribute to just get what you need. Note this will be a separate request to the database!!

...
[PXBool]        
[PXDBScalar(typeof(Search<EDITran.sync, 
    Where<EDITran.doctype, Equal<SOOrder.orderType>, 
        And<EDITran.erprefnbr, Equal<SOOrder.orderNbr>>>>))]
public virtual bool? Sync

Answer 2

I'm not sure that's the right use of PXFormula. Here's the reference I normally use when I need to use PXFormula. See if it helps you simplify your PXFormula. You may need to simplify to a single Selector and define a foreign key to be able to complete your lookup.

Also, are you sure that the database contains the values that you expect? I often find that my problem is not where I think, and your issue may lie in setting the values or writing them to the database in your business logic.

It appears that you need the PXFormula to look to your EDITran table to find the record identified by the key (1st field specified in Selector) to then return back the value of the second field specified, which would reside in EDITran.

From Acumatica Developers Blog (AsiaBlog):

Selector

Selector does following:

Fetches a PXSelectorAttribute defined on the foreign key field (KeyField) of the current DAC. Fetches the foreign data record currently referenced by the selector. Using calculates and returns an expression on that data record as defined by ForeignOperand.

public class APVendorPrice : IBqlTable
{
    // 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; }
}

From that, I'd expect your PXFormula to look more like:

[PXFormula(typeof(Selector<
    SOOrder.orderNbr,
    EDITran.sync>))]

... assuming you have enough defined to tell Acumatica how to relate SOOrder.orderNbr to EDITran.erprefnbr.