Use of PXProjection: General Information

The PXProjection attribute binds a DAC to an arbitrarily chosen dataset defined by the Select command. The MYOB Acumatica Framework does not bind this DAC to a database table—that is, it does not select data from the table with the same name as the DAC. Instead, you specify a fluent BQL Select command (or its traditional BQL equivalent) as the data source in your query. This Select command can select data from one DAC or multiple DACs and can include most BQL clauses. Thus, you can think of PXProjection entities as the MYOB Acumatica Framework's version of SQL views.

The PXProjection attribute is mainly used to perform complex Select operations by using a BQL query. If you need to join a BQL query that is also a complex joined select query, you should use the PXProjection attribute.

This attribute can also be used when 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.

Learning Objectives

In this chapter, you will learn how to do the following:

• Define the DAC for a new tab with the PXProjection attribute on an existing form
• Define the data view for the new tab
• Create a new tab item on an existing form

Applicable Scenarios

You use the PXProjection attribute in the following cases:

• You want to join a BQL query that is a complex joined select query.
• You want to display data from multiple tables on a form or a tab.
• You want to retrieve only a subset of fields corresponding to any number of DACs from the database.
• You want to return filtered data from a DAC.
• You want to persist data to any number of database tables.

Configuration of the PXProjection Attribute

The following code shows an example of the use of the PXProjection attribute.

[PXProjection(typeof(
    SelectFrom<Supplier>.
    InnerJoin<SupplierProduct>.On<
     SupplierProduct.accountID.IsEqual<Supplier.accountID>>))]

    public class SupplierAccounts: PXBqlTable, IBqlTable 
    {}

Note that you can use the PXProjection attribute with both traditional and fluent BQL.

Field Mapping by Using the BqlField and BqlTable Properties

In the projection DAC (the DAC on which you declare the PXProjection attribute) , you should explicitly map the projection fields to the database column retrieved by the Select command. To map a field, set the BqlField property of the attribute that binds the field to the database (such as PXDBString and PXDBDecimal) to the type that represents the column, as shown in the following code.

[PXDBString(15, IsUnicode = true, BqlField = typeof(Supplier.accountID))]
public virtual string AccountID { get; set; }

Alternatively, for the code example above, you can use the BqlTable property to map the field. The field binds by its name implicitly since the field has the same name in the Supplier table. Thus, the above code example can be rewritten as follows.

[PXDBString(15, IsUnicode = true, BqlTable = typeof(Supplier))]
public virtual string AccountID { get; set; }

Note that a projection DAC does not need to map all the available DAC fields. Unbound DAC fields and DAC fields that are marked with the PXDBScalar and PXDBCalced attributes do not need to be mapped because they are calculated fields.

Mapping of Fields Automatically Through Inheritance

If you do not want to list all DAC fields, you can inherit projection from one of the DACs in the Select command. In this case, you should neither override fields from this DAC nor add mapping by using BqlField. The following code shows an example.

[PXProjection(typeof(
    SelectFrom<Supplier>.
    InnerJoin<SupplierProduct>.On<
     SupplierProduct.accountID.IsEqual<Supplier.accountID>>))]

public class SupplierAccounts : Supplier //inherit from Supplier
{
    //You do not have to list fields from the Supplier DAC.
    ...
}

Additional Configuration of the PXProjection Attribute

You can further configure a projection in the following ways:

• Reduce the field count by using a projection: In many cases, such as when generating reports, you need only a small subset of the corresponding DAC fields to be returned from the database. To optimize your query and avoid retrieving all the DAC fields, you can configure a projection that retrieves only the data for a specific set of fields.
• Filter rows with the projection: You can configure a projection to return filtered data from a DAC.
• Declare the projection to be mutable: A projection is read-only by default —that is, it does not save any data to the database. However, you can configure a projection to be mutable by using the Persistent property of the PXProjection attribute.
• Use the projection in another projection query: You can do this by referencing the existing projection in the query of the other projection.
• Use parameterized elements in the projection query: You can write a projection query that contains parameterized elements, such as the current value of one of the DAC fields.
• Use the CurrentMatch BQL operator in the projection query: Your projection query can use this operator to provide row-level security.

For details on how to configure a projection in the ways listed above, see Use of PXProjection: Additional Configuration of the PXProjection Attribute.