Use of PXProjection: Additional Configuration of the PXProjection Attribute

The following sections describe various ways in which you can further configure and use the PXProjection attribute.

Reducing 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. You can configure a projection to exclude the unnecessary fields and optimize your query. The following code shows an example of a projection that returns only the two fields that are defined by the AdjgDocType and AdjgRefNbr properties.

[PXHidden, PXProjection(typeof(SelectFrom<APAdjust>))]
public class APAdjust3 : PXBqlTable, IBqlTable
{
    [PXDBString(3, IsKey = true, IsFixed = true, InputMask = "",
     BqlField = typeof(APAdjust.adjgDocType))]
    [PXUIField(DisplayName = "AdjgDocType", Visibility = PXUIVisibility.Visible, 
     Visible = false)]
    public virtual String AdjgDocType { get; set; }
    public abstract class adjgDocType : PX.Data.BQL.BqlString.Field<adjgDocType> { }
 
    [PXDBString(15, IsUnicode = true, IsKey = true,
     BqlField = typeof(APAdjust.adjgRefNbr))]
    [PXUIField(DisplayName = "AdjgRefNbr", Visibility = PXUIVisibility.Visible,
     Visible = false)]
    public virtual String AdjgRefNbr { get; set; }
    public abstract class adjgRefNbr : PX.Data.BQL.BqlString.Field<adjgRefNbr> { }
}

Filtering Rows with a Projection

You can configure a projection to return filtered data from a DAC. The following projection returns filtered data from the Vendor DAC. The data is filtered based on the criteria specified in the Where clause of the BQL query that is passed to the PXProjection attribute.

[PXProjection(typeof(SelectFrom<Vendor>.Where<Vendor.payToVendorID
  .IsEqual<Vendor.bAccountID.FromCurrent.Value>>))]
public class SuppliedByVendor : Vendor { }

Persisting Data with a Projection

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 setting the Persistent property of the PXProjection attribute to true. As a result, the table corresponding to the first DAC that is specified in the Select command of the projection's query will be mutable. If you also want to save the changes made to the joined tables in the projection's query, you must mark the fields on which the tables are joined with the PXExtraKeyAttribute attribute. The following code shows an example.

[PXProjection(typeof(
    SelectFrom<ContractWatcher>.
    RightJoin<Contact>.On<
        Contact.contactID.IsEqual<ContractWatcher.contactID>>),
    Persistent = true)] // Mark the ContractWatcher table mutable by default.
public class SelContractWatcher : ContractWatcher
{
  ...
    [PXDBInt(BqlField = typeof(Contact.contactID))]
    [PXUIField(Visibility = PXUIVisibility.Invisible)]
    // Without the [PXExtraKey] attribute, only ContractWatcher table will be mutable.
    [PXExtraKey] // Mark the Contact table as mutable too.
    public virtual Int32? ContactContactID { get; set; }
    public abstract class contactContactID : PX.Data.BQL.BqlInt.Field<contactContactID> { }
  ...
}

In the code above, the Persistent property of the PXProjection attribute has been set to true, causing the ContractWatcher table to be mutable. The contactContactID field of the joined Contact table has been marked with the PXExtraKeyAttribute attribute to mark this table as mutable. The following code shows how these tables could then be updated.

...
foreach (SelContractWatcher watcher in listWatchers.Cast<SelContractWatcher>()
  .Select(item => (SelContractWatcher)Watchers.Cache.CreateCopy(item)))
{
    watcher.ContractID = newContract.ContractID;
    graph.Watchers.Update(watcher); // The ContractWatcher and Contact tables will be updated.
} 
...

Alternatively, you can use the constructor with parameters to explicitly provide the list of mutable tables. The listed tables must be referenced in the Select command of the projection's query. The constructor implicitly sets the Persistent property of the PXProjection attribute to true. The following code shows an example.

[PXProjection(typeof(
    SelectFrom<SOLineSplit>.
    InnerJoin<SOOrderType>.On<
        SOOrderType.orderType.IsEqual<SOLineSplit.orderType>>.
    InnerJoin<SOOrderTypeOperation>.On<
        SOOrderTypeOperation.orderType.IsEqual<SOLineSplit.orderType>.
        And<SOOrderTypeOperation.operation.IsEqual<SOLineSplit.operation>>>),
    new Type[] { typeof(SOLineSplit), typeof(SOOrderType) })] // List of the mutable tables.
public class SOLineSplit2 : PXBqlTable, IBqlTable
{
    [PXDBString(2, IsFixed = true, BqlField = typeof(SOLineSplit.sOOrderType))]
    [PXExtraKey] // Mark the joined SOOrderType table as mutable.
    public virtual String SOOrderType{ get; set; } 
    public abstract class sOOrderType : PX.Data.BQL.BqlString.Field<sOOrderType> { }
    ...
}

In the code above, the SOLineSplit and SOOrderType tables have been listed as mutable by using the new Type[]{} constructor. You can then update these tables as shown in the following code.

...
var split = shipmentEntry.Caches<SOLineSplit2>().Rows.Current;
if (split != null)
{
    split.ShippedQty = 0;
    // Only the SOLineSplit and SOOrderType tables will be updated.
    shipmentEntry.Caches<SOLineSplit2>().Update(split); 
}
...

Using a Projection in Another Projection

You can declare a projection and reference it in the projection query of another projection. The following code shows an example.

// Define the FABookHistoryMax projection.
[PXProjection(typeof(
    SelectFrom<FABookHistory>.
    AggregateTo<
        GroupBy<FABookHistory.assetID>,
        GroupBy<FABookHistory.bookID>,
        Max<FABookHistory.finPeriodID>>))]
public class FABookHistoryMax : PXBqlTable, IBqlTable
{
    [PXDBInt(IsKey = true, BqlField = typeof(FABookHistory.assetID))]
    [PXDefault]
    public virtual Int32? AssetID { get; set; }
    public abstract class assetID : PX.Data.BQL.BqlInt.Field<assetID> { }
 
    [PXDBInt(IsKey = true, BqlField = typeof(FABookHistory.bookID))]
    [PXDefault]
    public virtual Int32? BookID { get; set; }
    public abstract class bookID : PX.Data.BQL.BqlInt.Field<bookID> { }
 
    [GL.FinPeriodID(BqlField = typeof(FABookHistory.finPeriodID))]
    [PXDefault]
    public virtual String FinPeriodID { get; set; }
    public abstract class finPeriodID : PX.Data.BQL.BqlString.Field<finPeriodID> { }
}

/* Use the FABookHistoryMax projection in the PXProjectionAttribute 
   of the FABookHistoryRecon projection. */
[PXProjection(typeof(
    SelectFrom<FABookHistoryMax>.
    InnerJoin<FABookHistory>.On<
        FABookHistoryMax.assetID.IsEqual<FABookHistory.assetID>.
        And<FABookHistoryMax.bookID.IsEqual<FABookHistory.bookID>>.
        And<FABookHistoryMax.finPeriodID.IsEqual<FABookHistory.finPeriodID>>>.
    InnerJoin<FABook>.On<
        FABook.bookID.IsEqual<FABookHistory.bookID>>))]
public class FABookHistoryRecon : PXBqlTable, IBqlTable
{
    [PXDBInt(IsKey = true, BqlField = typeof(FABookHistory.assetID))]
    [PXDefault]
    public virtual Int32? AssetID { get; set; }
    public abstract class assetID : PX.Data.BQL.BqlInt.Field<assetID> { }
 
    [PXDBBool(BqlField = typeof(FABook.updateGL))]
    public virtual Boolean? UpdateGL { get; set; }
    public abstract class updateGL : PX.Data.BQL.BqlBool.Field<updateGL> { }
    ...
}

Using Parameterized Elements in a Projection Query

You can write a projection query that contains parameterized elements, such as the current value of one of the DAC fields. However, if your projection query uses the values of the DAC fields from the current DAC record for these elements, you must access those values by using the CurrentValue BQL operator instead of the Current or Current2 BQL operator. If your query is written by using fluent BQL, you should use the field.FromCurrent.Value operator instead of the field.FromCurrent operator. The following code shows an example of the field.FromCurrent.Value fluent BQL operator being used in a projection query.

[PXProjection(typeof(SelectFrom<Vendor>.Where<Vendor.payToVendorID
  .IsEqual<Vendor.bAccountID.FromCurrent.Value>>))]
public class SuppliedByVendor : Vendor { }

Using the CurrentMatch BQL Operator in a Projection Query

You should use the CurrentMatch BQL operator instead of the Match BQL operator in your projection queries to enable row-level security. This operator matches only the data records that the specified user has access to. The following code shows an example.

[PXProjection(typeof(
		SelectFrom<SOLine>
			.InnerJoin<SOOrder>
				.On<SOLine.FK.Order>
			.InnerJoin<SOOrderType>
				.On<SOOrder.FK.OrderType
				.And<SOOrderType.behavior
                            .IsIn<SOBehavior.bL, SOBehavior.sO, SOBehavior.tR, SOBehavior.rM>>>
			.InnerJoin<SOOrderTypeOperation>
				.On<SOOrderTypeOperation.FK.OrderType
				.And<SOOrderTypeOperation.operation.IsEqual<SOOperation.issue>>
				.And<SOOrderTypeOperation.active.IsEqual<True>>>
			.LeftJoin<Customer>
				.On<SOOrder.FK.Customer>
			.InnerJoin<InventoryItem>
				.On<SOLine.FK.InventoryItem
				.And<InventoryItem.stkItem.IsEqual<True>>
				.And<CurrentMatch<InventoryItem, AccessInfo.userName>>>
			.InnerJoin<SOLineSiteAllocation>
				.On<SOLineSiteAllocation.FK.OrderLine
				.And<SOLineSiteAllocation.siteID
                            .IsEqual<SalesAllocationsFilter.siteID.FromCurrent.Value>>>
		.Where<SOLine.isSpecialOrder.IsNotEqual<True>
			.And<SOOrderType.behavior
                     .IsEqual<SOBehavior.tR>.Or<Customer.bAccountID.IsNotNull
                     .And<CurrentMatch<Customer, AccessInfo.userName>>>>
			.And<SOLine.pOCreate.IsNotEqual<True>.Or<SOLine.pOSource
                     .IsEqual<INReplenishmentSource.purchaseToOrder>>>
			.And<SOLine.completed.IsNotEqual<True>>>
		), Persistent = false)]
public class SalesAllocation : PXBqlTable, IBqlTable
{
  ...
}