From a certain point of view, an ERP is basically a system to store and manipulate data entered by users. It’s definitely a very general point of view that highlights the importance of the data management inside the system.
In Acumatica, the data stored in the database is being handled through a layer that allows developers to manipulate data without querying directly the database. For Acumatica developers, it is a well-known abstraction layer — Business Query Language or simply BQL. This layer provides multiple ways to manipulate data and one of them is the PXProjection attribute.
In this post we’ll dive a little bit into this attribute, we’ll review some of its properties, how it can be used, and show common scenarios where it applies.
This attribute is used to define a new DAC class derived from the IBqlTable interface or any other existing DAC, with specific columns/fields/properties coming from DAC’s included in a select statement that defines the data managed by this new class. From a certain point of view, we can see this new DAC class as the equivalent of a SQL view where both define a set of data from a statement and both are a virtual table, i.e., there is not actual table in the database with their names.
Below is an example of this projection DAC using INTranSplit as main DAC in the select statement:
The above projection DAC selects the latest released entry documents for serialized items in the system and we can use it in a view declaration to show the resulting records in the UI interface of a screen as below:
or we can use it directly in another BQL statement:
No doubt the example above is a little bit complex but showcases how useful a projection DAC could be when dealing with complicated datasets, allowing to reduce the complexity of the final BQL and making tough queries possible.
Let’s start from the beginning:
There are basically two ways to declare a projection DAC when it comes to the base object. We can define it using the IBqlTable interface as the example above, or we can derive the new class from an existing DAC:
In this case, both declarations will fetch all sales orders approved. However, the main difference between these two definitions is that the first one requires you to declare the properties and fields needed, including at least the key fields. For the second definition, the new projection DAC inherits all fields and properties from the base class (SOOrder).
Like any other DAC, we must define the key fields to set the uniqueness conditions for its records by assigning the IsKey = true property to the base attribute. Using the same example above, we must declare the OrderType and OrderNbr fields for the first declaration:
Notice that the properties added are explicitly mapped to an existing property of the DAC from the select statement by assigning the appropriate type to BqlField property. However, this is not necessary when deriving from an existing DAC and this DAC is the same as the one used in the select statement like the second definition.
In case we would need to change any base property derived from the base class, we should override it as follows:
Notice the abstract orderNbr class is defined with the new modifier to hide the definition of the base class. This new abstract class is normally used if this property is used in another Bql statement. The OrderNbr property should have the override modifier in order to set the new attributes.
As initially mentioned, the Select statement can have more than one table involved, which means we would have to map the fields needed from the additional DAC’s in case any field from them is required, otherwise the additional DAC’s will only be used for filtering the data we want.
Below is an example of a projection DAC to fetch sales orders records with a behavior different than RM:
In this case, the additional DAC (SOOrderType) is used for filtering purposes only since the new projection DAC does not map any field from this additional DAC (SOOrderType). However, we could have a special need to map a field from this table. If this is the case, we should do it as follows:
By default, the projection DAC is readonly, i.e., it does not allow to persist changes to the database. However, there is a Persistent property that allows it by setting it as true as you can see here:
This property allows you to persist changes to the database – either if they are Inserted, Deleted or in an Updated state.
Of course, changes can be persisted to all tables/DAC’s involved in the select statement. If the changes in the main table are persisted and if several tables need to be updated in the projection DAC, the documentation says the fields implementing the relationship between the main and the joined tables must have the PXExtraKey attribute to allow the proper update called by the projection. You can see this clearly below:
In this case, the OrderType property is the only one involved in the relationship between the two tables and is where the attribute should be placed.
This attribute also has an option to set a list of the tables that require persist. In this case, tables that do not need it can be excluded:
When using the persistent (with no capital) property to set the list of DAC’s to be persisted, the Persistent property is set to true automatically.
When updating changes to the database, not all fields in all DAC’s involved in the select statement are updated: only the fields that are mapped are the ones that will be updated.
Hope this information helps you understand more about this attribute and it help you to improve your BQL queries.