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).
                    On<FinPeriod.finPeriodID, GreaterEqual<ARHistory.finPeriodID>>>,
    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.

                On<Customer.bAccountID, Equal<ARHistoryByPeriod.customerID>>,
                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.

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

One more example of good editable projection usage:

                 On<ARInvoice.docType, Equal<ARRegister.docType>, 
                         And<ARInvoice.refNbr, Equal<ARRegister.refNbr>>>, 
                 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!

8 Replies to “PXProjection – SQL Views using BQL”

  1. I have view with projection which is based on grid line. Now I need to have default value when new line is inserted how can this be done please adviice

  2. Hi Sergey,

    In the same question, Can i make all 3 fields [IsKey=true] in PXAttribute
    Like composite key…
    What is the correct way to make column IsKey=true

    Separately on 3 fields
    To generate a custom column (In previous question)

    Please explain the concept & suggest ??

  3. Hi Sergey,

    How can we generate Custom Unique column in Projection DAC

    e.g. In SQL,

    Row_Number() Over(Order By Field1, Field2, Field3) as ‘UniqueColumn’
    Field1+’_’+Field2+’_’+Field3 as UniqueColumn

    Please suggest.

    1. This is not possible. But you can create a new field in the dac, that will concatenate other field with PXFormula

      1. HI Sergey,

        I am using Projection DAC for Report only
        How can i use PXFormula to concatenate fields in Projection DAC only

        Gaurav Katiyar

        1. Please create a dev support ticket at Acumatica partner portal. Support team will give you a code example.

Leave a Reply

Your email address will not be published. Required fields are marked *