PXProjection – SQL Views using BQL

Hi Everyone,

Occasionally I get question from partners related:

• Can we Join several tables with possibility to update them all? You know that Joined tables in Acumatica are read-only and you cannot update fields there
• Can we create/use SQL-like views in Acumatica?
• How can I join grouped (statistical) view to the DAC?

The answer to all these questions is yes you can do it. But answer on how to do that is not so simple. Obviously the most strait forward way is to create an SQL view and generate a DAC based on it. But this way does not solve point number 1 – SQL view is read-only and does not allow to update tables inside.

The more advanced way is to use Acumatica Framework. Basically, you need to use special PXProjection attribute. PXProjection is some sort of the SQL-like view that is done with BQL with some advanced functionality.

Data Selection though Projection.
To use it you need to complete few steps:

• Create new DAC that will represent view. This DAC may have less columns than you have in DACs you will select later. So just define fields you need. In the end it will help a bit with select performance.
• You need to define PXProjectionAttribute on this DAC. For PXProjection constructor you should provide BQL command that will define what tables you want to select. You may use all possible commands of BQL (Join, Where, GroupBy, OrderBy).

  [PXProjection(typeof(Select5<ARHistory,
          InnerJoin<FinPeriod,
                  On<FinPeriod.finPeriodID, GreaterEqual<ARHistory.finPeriodID>>>,
          Aggregate<GroupBy<ARHistory.customerID>>))]
  public class ARHistoryByPeriod : PX.Data.IBqlTable { … }

• You need to specify BQLField property for all database related fields. BQLField is inherited from PXDBFieldAttribute, so all DB field attributes (PXDBString, PXDBBool, PXDBInt and so on) have it inside plus most of Aggregate attributes (CustomerAttribute, Account, FinPeriodID). BQL Field is needed to identify from what table (out of joined in BQL above) this particular field should be taken. This configuration cannot be automatic as different tables may have fields with same names. Also do not forget to define correct keys inside your projection DAC.

  [FinPeriodID(IsKey = true, BqlField = typeof(FinPeriod.finPeriodID))]
  public virtual String FinPeriodID { … }

That is all! Now you can use this DAC inside PXSelects, Reports and Generic Inquires.

PXSelectJoin<ARHistoryByPeriod,
        InnerJoin<Customer,
                On<Customer.bAccountID, Equal<ARHistoryByPeriod.customerID>>,
        LeftJoin<LastRevaluation,
                On<ARHistoryByPeriod.customerID, Equal<LastRevaluation.customerID>,
                And<ARHistoryByPeriod.branchID, Equal<LastRevaluation.branchID>,
                And<ARHistoryByPeriod.accountID, Equal<LastRevaluation.accountID>,
>>>>>>;

You can read more about PXProjection Attribute in Acumatica Help Files.

Data Modification though Projection. Another important thing that is supported by PXProjection is saving changes direct to database. As you have defined DACs in your projection definition, Acumatica knows keys of each record (from original DAC) and can use it to track changes and update values back to correct record of database. But please note that this feature is not available for projections with Group By clause as than keys will be lost with aggregation.

To activate it just put Persistent = true inside PXProjection constructor.

[PXProjection(typeof(Select<TaxTran,
        Where<TaxTran.module,Equal<BatchModule.moduleAP>>>), Persistent = true)]
public partial class APTaxTran : TaxTran { … }

One more example of good editable projection usage:

[PXProjection(typeof(Select2<ARRegister, 
        InnerJoin<ARInvoice, 
                 On<ARInvoice.docType, Equal<ARRegister.docType>, 
                         And<ARInvoice.refNbr, Equal<ARRegister.refNbr>>>, 
        InnerJoin<ARPayment, 
                 On<ARPayment.docType, Equal<ARRegister.docType>,
                         And<ARPayment.refNbr, Equal<ARRegister.refNbr>>>>>,
        Where<ARRegister.docType, Equal<ARDocType.cashSale>, 
                 Or<ARRegister.docType, Equal<ARDocType.cashReturn>>>>),
Persistent = true)]
public class ARCashSale : ARRegister { .. }

Have a nice development!