The PXProjection attribute is mainly used to perform complex Select operations by using a fluent BQL query. If you need to join a fluent BQL query that is also a complex joined select query, you should use the PXProjection attribute.
This attribute can also be used in situations where you need to display data from multiple tables on a form or a tab. To do this, you need to declare a DAC with the PXProjection attribute, which implements the projection of data from one table or multiple tables into a single DAC.
This attribute is also 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. There is no actual table in the database with their names.
Benefits of PXProjection
- Optimized Data Retrieval: When working with screens or reports that require a limited set of fields from a DAC, PXProjection allows you to retrieve only the necessary data. This can lead to reduced database load and improved query performance.
- Improved Readability: By creating a PXProjection that includes only the fields relevant to the task at hand, you improve the readability of your code. Developers working on the project can quickly understand the purpose of the projection and its underlying data requirements.
- Minimized Overhead: In cases where the original DAC has a complex structure with numerous fields, using a PXProjection can help you avoid unnecessary overhead associated with loading and managing those extra fields when they are not needed.
- Enhanced Security: When you want to expose a subset of data to specific user roles or screens, PXProjection provides a way to do so without altering the original DAC's structure. This contributes to maintaining data security and integrity.
PXProjection with a Single DAC
Consider a DAC named InventoryItem, which contains a variety of fields related to inventory items. Now, imagine you are building a report that displays only the item code, description, and base unit. Instead of working directly with the InventoryItem DAC, you can create a PXProjection:
[PXProjection(typeof(Select<InventoryItem,
Where<InventoryItem.itemStatus, Equal<InventoryItemStatus.active>>>))]
public class InventoryItemProjection : IBqlTable
{
#region ItemID
[PXDBInt(IsKey = true, BqlField = typeof(InventoryItem.inventoryID))]
[PXUIField(DisplayName = "Item ID")]
public virtual int? ItemID { get; set; }
#endregion
#region InventoryCD
[PXDBString(30, IsUnicode = true, BqlField = typeof(InventoryItem.inventoryCD))]
[PXUIField(DisplayName = "Inventory ID")]
public virtual string InventoryCD { get; set; }
#endregion
#region Descr
[PXDBString(256, IsUnicode = true, BqlField = typeof(InventoryItem.descr))]
[PXUIField(DisplayName = "Description")]
public virtual string Descr { get; set; }
#endregion
#region BaseUnit
[PXDBString(6, IsUnicode = true, BqlField = typeof(InventoryItem.baseUnit))]
[PXUIField(DisplayName = "Base Unit")]
public virtual string BaseUnit { get; set; }
#endregion
}
In this example, we've created an InventoryItemProjection class that represents a projection of the InventoryItem DAC. We've included only the fields that are relevant to our report, ensuring better performance and a more focused data representation.
Multi-DAC Column Exposing
In situations involving multiple DACs, PXProjection allows you to expose specific fields from each DAC and combine them into a single virtual structure. Let's take an example to illustrate this concept.
[PXProjection(typeof(
SelectFrom<ARInvoice>.
InnerJoin<ARAdjust>.On<
ARAdjust.adjdRefNbr.IsEqual<ARInvoice.refNbr>.
And<ARAdjust.adjdDocType.IsEqual<ARInvoice.docType>>>.
AggregateTo<
Max<ARAdjust.adjgDocDate>,
GroupBy<ARAdjust.adjdRefNbr>,
GroupBy<ARAdjust.adjdDocType>>))]
public class MultiplyProjection : IBqlTable
{
#region InvoiceNbr
[PXDBString(15, IsUnicode = true, IsKey = true, InputMask = "",
BqlField = typeof(ARInvoice.refNbr))]
[PXUIField(DisplayName = "Invoice Nbr.", Enabled = false)]
public virtual String InvoiceNbr { get; set; }
public abstract class invoiceNbr :
PX.Data.BQL.BqlString.Field<invoiceNbr>
{ }
#endregion
#region DueDate
[PXDBDate(BqlField = typeof(ARInvoice.dueDate))]
[PXUIField(DisplayName = "Due Date", Enabled = false)]
public virtual DateTime? DueDate { get; set; }
public abstract class dueDate :
PX.Data.BQL.BqlDateTime.Field<dueDate>
{ }
#endregion
#region AdjgRefNbr
[PXDBString(BqlField = typeof(ARAdjust.adjgRefNbr))]
[PXUIField(DisplayName = "Latest Payment", Enabled = false)]
public virtual String AdjgRefNbr { get; set; }
public abstract class adjgRefNbr :
PX.Data.BQL.BqlString.Field<adjgRefNbr>
{ }
#endregion
#region CuryAdjdAmt
[PXDBDecimal(BqlField = typeof(ARAdjust.curyAdjdAmt))]
[PXUIField(DisplayName = "Latest Amount Paid", Enabled = false)]
public virtual Decimal? CuryAdjdAmt { get; set; }
public abstract class curyAdjdAmt :
PX.Data.BQL.BqlDecimal.Field<curyAdjdAmt>
{ }
#endregion
}
In this code we define a MultiplyProjection class that uses the PXProjection attribute to create a projected view that aggregates data from the ARInvoice and ARAdjust tables. The projection retrieves information such as the invoice number, due date, latest payment reference number, and the latest amount paid. The abstract fields defined within the class correspond to the selected columns from the involved tables. This projection can be used to display summarized payment information associated with invoices.
Also we need to create a view for this such like that:
public SelectFrom<MultiplyProjection>
.Where<MultiplyProjection.invoiceNbr.IsEqual<RSSVWorkOrder.invoiceNbr.FromCurrent>>.View Payments;
Then you can add this DAC to the screen that you need by using the screen editor. In my way it looks like this.
Data Updates in the Database
By default, the projection DAC is readonly and it does not allow persistent changes to the database. However, there is a Persistent property of PXProjection attribute that allows it by setting it as true
Example:
[PXProjection(typeof(Select<SOOrder, Where<SOOrder.approved, Equal<True>>>),
//Persistent says, make saving to database
Persistent = true)
]
This property allows you to persist changes to the database either if they are Inserted, Deleted or in an Updated state.
Also changes can be persisted to all tables 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 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:
#region OrderType
[PXExtraKey]//Take note of usage sample
[PXDBString(2, IsKey = true, IsFixed = true, InputMask = ">aa", BqlField = typeof(SOOrderType.orderType))]
[PXUIField(DisplayName = "Order Type", Visibility = PXUIVisibility.SelectorVisible)]
[PXSelector(typeof(Search<SOOrderType.orderType>))]
public virtual string OrderType { get; set; }
public abstract class orderType : PX.Data.BQL.BqlString.Field<orderType> { }
#endregion
Notice!
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.
Extending PXProjection
The standard PXProjection might not offer all the data columns you require for your business processes. That's where extending PXProjection comes into. By adding additional columns, you can create a more comprehensive and streamlined view of your data. Let's explore a scenario where we need to achieve precisely that.
Imagine you're managing a warehouse with various products. Acumatica PXProjection provides you with a pre-built view that combines data from different tables like INItem, InventoryItem, and INLocation. However, this view lacks a crucial piece of information - the shelf life of each product. To make informed decisions about stock rotation, you need to see the shelf life alongside the existing data.
To achieve this, you can extend the existing PXProjection class by creating your own custom class that inherits from it. Let's call it INItemExtension. In this class, you'll define the additional field for the shelf life.
public class INItemExtension : PXCacheExtension<INItem>
{
#region ShelfLife
[PXDBDate]
[PXUIField(DisplayName = "Shelf Life")]
public virtual DateTime? ShelfLife { get; set; }
public abstract class shelfLife : PX.Data.BQL.BqlDateTime.Field<shelfLife> { }
#endregion
}
Here, we're creating an extension for the INItem DAC and adding a field called ShelfLife.
With the extension in place, it's time to enhance the PXProjection. Locate the PXProjection class that combines the desired tables - INItemXInventoryItemXLocation. We'll add a join to our custom field in this class.
[PXProjection(typeof(
Select2<INItem,
InnerJoin<InventoryItem, On<InventoryItem.inventoryID, Equal<INItem.inventoryID>>,
LeftJoin<INLocationStatus,
On<INLocationStatus.inventoryID, Equal<INItem.inventoryID>>,
LeftJoin<INLocation, On<INLocation.locationID, Equal<INLocationStatus.locationID>>>>>>))]
[PXCacheName("Item Location Projection")]
public class INItemXInventoryItemXLocation : IBqlTable
{
#region ShelfLife
[PXDBDate(typeof(INItemExtension.shelfLife))]
[PXUIField(DisplayName = "Shelf Life")]
public virtual DateTime? ShelfLife { get; set; }
public abstract class shelfLife : PX.Data.BQL.BqlDateTime.Field<shelfLife> { }
#endregion
}
In the PXProjection class, we're extending the existing Select2 to include our custom field. The PXDBDate attribute now references the ShelfLife field from our extension. By doing so, we're effectively joining the shelf life information into our PXProjection.
Utilizing the power of PXProjection becomes especially valuable when you're working with a single DAC entity and need to tailor its structure to meet specific requirements. By creating a customized, flattened view of the DAC, you can enhance your code's readability, improve database query performance, and optimize the overall user experience.
PXProjection in Acumatica empowers businesses to create tailored data structures that consolidate information from multiple DACs. This is particularly useful when you need to expose specific columns from these DACs for reporting and analysis.
PXProjection is more than just a data view - it's a canvas on which you can paint a more detailed picture of your business processes. Extending a PXProjection to include additional columns empowers you to mold your data views to match your specific needs.