In my 20+ years of customizing and supporting ERP systems, one of the most common requests is for custom reports. The older systems didn’t come with a report writer, and even the more modern systems held challenges in using one. As a fellow developer used to say in one of his many versions of the proverbial data integrity problem, “If you don’t want two answers to the same question, don’t ask two people.”
Modern report writers, or specifically Generic Inquiries in the case of Acumatica, do little to eliminate this age old problem. If anything, they exacerbate the problem by enabling more people greater access to attempt compiling data into a usable format. When it comes to data integrity, linking the various tables of a relational database is best left to those that know how to read and understand primary keys and the plethora of tables containing the data that will be consolidated for reports. That is not to say that you must be born an expert, but certainly someone very familiar with the data structures should be involved in reviewing the tables and relationships built by those who are just starting to learn.
As a developer, you can simplify data structures for your report writer by using an attribute (and associated data class) called PXProjectionAttribute. A [PXProjection] is the Acumatica equivalent of a SQL View, except that the [PXProjection] is compiled at the application server rather than a SQL View at the database. This distinction is very important in that we want our Acumatica code to remain database agnostic, that is, without reliance on what database is being utilized. [PXProjection] allows us to create a “view” that does not care what database is used, makes our view highly portable to other instances of any supported database via our customization project, and still allows us to combine related tables into a simplified data structure for use in screens and reporting. Furthermore, we can expose the [PXProjection] to those with access to write generic inquiries so that our more sophisticated data structures can be use with confidence in data integrity.
In the following example, we will dig into INTran for material transactions and generate a view of fields from INTran, SOOrder, and POOrder. By utilizing left joins, we will retrieve the SOOrder and POOrder data only when applicable.
Example
namespace BLOG
{
[PXProjection(typeof(
SelectFrom<INTran>
.LeftJoin<SOShipLine>.On<SOShipLine.shipmentNbr.IsEqual<INTran.sOShipmentNbr>.And<SOShipLine.lineNbr.IsEqual<INTran.sOShipmentLineNbr>>>
.LeftJoin<SOLine>.On<SOLine.orderNbr.IsEqual<SOShipLine.origOrderNbr>.And<SOLine.orderType.IsEqual<SOShipLine.origOrderType>.And<SOLine.lineNbr.IsEqual<SOShipLine.origLineNbr>>>>
.LeftJoin<SOOrder>.On<SOOrder.orderNbr.IsEqual<SOShipLine.origOrderNbr>.And<SOOrder.orderType.IsEqual<SOShipLine.origOrderType>>>
.LeftJoin<POReceiptLine>.On<POReceiptLine.receiptNbr.IsEqual<INTran.pOReceiptNbr>.And<POReceiptLine.lineNbr.IsEqual<INTran.pOReceiptLineNbr>>>
.LeftJoin<POOrder>.On<POOrder.orderNbr.IsEqual<POReceiptLine.pONbr>.And<POOrder.orderType.IsEqual<POReceiptLine.pOType>>>
))]
[Serializable]
[PXCacheName("Transactions Projection")]
public partial class BLOGTransactions : IBqlTable
{
#region DocType
public abstract class docType : PX.Data.BQL.BqlString.Field<docType> { }
[PXDBString(1, IsFixed = true, IsKey = true, BqlField = typeof(INTran.docType))]
[PXUIField(DisplayName = INRegister.docType.DisplayName)]
public virtual String DocType { get; set; }
#endregion
#region TranType
public abstract class tranType : PX.Data.BQL.BqlString.Field<tranType> { }
[PXDBString(3, IsFixed = true, BqlField = typeof(INTran.tranType))]
[INTranType.List()]
[PXUIField(DisplayName = "Tran. Type")]
public virtual String TranType { get; set; }
#endregion
#region RefNbr
public abstract class refNbr : PX.Data.BQL.BqlString.Field<refNbr> { }
[PXDBString(15, IsUnicode = true, IsKey = true, BqlField = typeof(INTran.refNbr))]
[PXUIField(DisplayName = INRegister.refNbr.DisplayName)]
public virtual String RefNbr { get; set; }
#endregion
#region LineNbr
public abstract class lineNbr : PX.Data.BQL.BqlInt.Field<lineNbr> { }
[PXDBInt(IsKey = true, BqlField = typeof(INTran.lineNbr))]
[PXUIField(DisplayName = "Line Number")]
public virtual Int32? LineNbr { get; set; }
#endregion
#region TranDate
public abstract class tranDate : PX.Data.BQL.BqlDateTime.Field<tranDate> { }
[PXDBDate(BqlField = typeof(INTran.tranDate))]
public virtual DateTime? TranDate { get; set; }
#endregion
#region InventoryID
public abstract class inventoryID : PX.Data.BQL.BqlInt.Field<inventoryID> { }
[PXDBInt(BqlField = typeof(INTran.inventoryID))]
public virtual Int32? InventoryID { get; set; }
#endregion
// Data from SOOrder
#region CustomerID
public abstract class customerID : PX.Data.BQL.BqlInt.Field<customerID> { }
[PXDBInt(BqlField = typeof(SOOrder.customerID))]
public virtual Int32? CustomerID { get; set; }
#endregion
// Data from POOrder
#region VendorID
public abstract class vendorID : PX.Data.BQL.BqlInt.Field<vendorID> { }
[PXDBInt(BqlField = typeof(POOrder.vendorID))]
[PXUIField(DisplayName = "Vendor")]
[PXSelector(typeof(Search<BAccountR.bAccountID>),
SubstituteKey = typeof(BAccountR.acctName))]
public virtual Int32? VendorID { get; set; }
#endregion
}
}First of all, notice the use of [PXProjection]. This example is written using, Fluent Business Query Language, or FBQL. Don’t worry if you prefer the older BQL as that is supported as well. The sytax is:
[PXProjection(typeof(INSERT BQL/FBQL SELECT HERE))]The BQL/FBQL Select statement is written in a way that would retrieve all of the tables needed, maintaining full data integrity of the table relationships, as if you were using it in any other business logic.
Next, notice the class definition. It serves three critical purposes. One, it names the DAC that you or the report writer will use to access the data. Two, it defines what fields will be accessible via the [PXProjection]. Three, it maps the fields of the projection to their underlying “real” tables and fields. In most cases, you need only define the abstract class and the virtual field just as with a normal DAC, but most importantly also the PX data type attribute. For instance, DocType is defined with [PXDBString] so that we can use the parameter BqlField = typeof(INTran.docType) to specify from where our projection will get the data to present via the projection’s DAC BLOGTransactions.
Before leaving the topic of the field definitions, I should note that like any other DAC, it is critical to specify the key fields. In this case, those key fields are DocType, RefNbr, and LineNbr. While you may experience other problems if you forget to reference the keys, in the very least a GI should complain that it cannot render a result because no key is specified.
As with other DAC’s, PXUIFieldAttribute can be used to define a display name which will default when the field is used in screens or reports/GI’s. Also, [PXSelector] may be used to translate the database value to a substitute key, such as displaying the vendor’s name. While the PXDB version of the attributes must be used to be able to map to the BQL/FBQL field in the [PXProjection], there is no need to replicate PXFormula, etc. as defined in the DAC’s being leveraged. Simply create appropriate field in the class, and map it to the underlying field in the database through its predefined DAC.
What about custom fields in my DAC Extensions?
Thanks to some guidance from Acumatica Developer MVP, Fernando Amadoz of Skyknack, I actually learned that answer TODAY. Let’s assume that we have extended SOOrder with a DAC Extension called SOOrderExt and that our field is called usrBLOGCustomerRef1. We simply include SOOrder in our BQL/FBQL and then link the field with references to our DAC Extension as follows:
#region UsrBLOGCustomerRef1
public abstract class usrBLOGCustomerRef1 : PX.Data.BQL.BqlString.Field<usrBLOGCustomerRef1> { }
[PXDBString(BqlField = typeof(SOOrderExt.usrBLOGCustomerRef1))]
[PXUIField(DisplayName = "Customer Ref 1")]
public virtual String UsrBLOGCustomerRef1 { get; set; }
#endregion
Remember that only the fields you define in the [PXProjection]’s class will be accessible, so be sure to define all fields that will be needed. I’d recommend using varibles, such as BLOG.Messages.CustomerRef1 to label the field both in the DAC Extension and in the [PXProjection] so that it remains consistent should you need to change the UI display name in the future.
That’s it for this introductory primer into [PXProjection]. Once you are comfortable with the concepts shown here, there are more advanced things you can do with your [PXProjection] such as incorporating [PXScalar] to add unbound fields. Check out Yuriy Zaletskyy’s blog post for more information.