AcumatiCares
Introduction
From a certain point of view, an ERP (Enterprise Resource Planning) 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.
PXProjection Attribute
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:
GIST: https://gist.github.com/Dioris/26e2bf16c5634fa550d6bd11482fd7fa
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:
GIST: https://gist.github.com/Dioris/2ef5dde25d6e9c361637834a8b60284c
or we can use it directly in another BQL statement:
GIST: https://gist.github.com/Dioris/2149a44aab505a6c63c541423d6d85ba
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:
GIST: https://gist.github.com/Dioris/b3f322e3db560f8be6fac8caf7a62c36
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:
GIST: https://gist.github.com/Dioris/37ecc07072c32f73aa1f4a0d29a6cb71
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:
GIST: https://gist.github.com/Dioris/dfdb49060df1f07a80e493864d0d91a4
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:
GIST: https://gist.github.com/Dioris/d61f6b13ef068e3dd997d7be79a122e0
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:
GIST: https://gist.github.com/Dioris/a0620755584c73fb8e00ac96aea97bfd
Persistent property
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:
GIST: https://gist.github.com/Dioris/403d482003d6b1f4979965ca0f1ea49f
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:
GIST: https://gist.github.com/Dioris/78c378c17ab91824bd0f38a6b0e986fc
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:
GIST: https://gist.github.com/Dioris/9f41d1f189a129a3f789987a812298d9
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.
Important!!
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.
Happy coding!
