Extend Acumatica Projection Based DAC Query

Question

Is there any way to extend/modify the projection query of a projected DAC.

For example, if I need to add a join statement to the projection and then use the newly joined table to the available fields.

Adding a custom field to the PXCacheExtension works as expected, but specifying the PXProjection query at the top of the PXCacheExtension DAC don't seems to have any effect.

Original:

[Serializable]
    [PXProjection(typeof(Select5<EPApproval, InnerJoin<Note,
                                    On<Note.noteID, Equal<EPApproval.refNoteID>,
                                 And<EPApproval.status, Equal<EPApprovalStatus.pending>>>,>,
                                 Where2<Where<EPApproval.ownerID, IsNotNull, And<EPApproval.ownerID, Equal<CurrentValue<AccessInfo.userID>>>>, 
                                        Or2<Where<EPApproval.workgroupID, InMember<CurrentValue<AccessInfo.userID>>,
                                        Or<EPApproval.workgroupID, IsNull>>,
                                        Or<EPApproval.workgroupID, Owned<CurrentValue<AccessInfo.userID>>>>>,
                                 Aggregate<GroupBy<EPApproval.refNoteID, 
        GroupBy<EPApproval.curyInfoID, 
        GroupBy<EPApproval.bAccountID, 
        GroupBy<EPApproval.ownerID, 
        GroupBy<EPApproval.approvedByID,
        GroupBy<EPApproval.curyTotalAmount>>>>>>>>))]
    public partial class EPOwned : EPApproval{

Extended:

[Serializable]
[PXProjection(typeof(Select5<EPApproval, InnerJoin<Note,
                                   On<Note.noteID, Equal<EPApproval.refNoteID>,
                                And<EPApproval.status, Equal<EPApprovalStatus.pending>>>,
                                LeftJoin<RQRequest, On<RQRequest.noteID, Equal<Note.noteID>>>>,
                                Where2<Where<EPApproval.ownerID, IsNotNull, And<EPApproval.ownerID, Equal<CurrentValue<AccessInfo.userID>>>>,
                                       Or2<Where<EPApproval.workgroupID, InMember<CurrentValue<AccessInfo.userID>>,
                                       Or<EPApproval.workgroupID, IsNull>>,
                                       Or<EPApproval.workgroupID, Owned<CurrentValue<AccessInfo.userID>>>>>,
                                Aggregate<GroupBy<EPApproval.refNoteID,
       GroupBy<EPApproval.curyInfoID,
       GroupBy<EPApproval.bAccountID,
       GroupBy<EPApproval.ownerID,
       GroupBy<EPApproval.approvedByID,
       GroupBy<EPApproval.curyTotalAmount>>>>>>>>))]
public class EPOwnedExt : PXCacheExtension<EPApprovalProcess.EPOwned> {

Thanks

Answer 1

To modify the projection query of a projected DAC, you should create an inherited DAC and decorate it with the PXSubstituteAttribute. Below is the sample for the FAAccrualTran DAC:

[Serializable]
[PXProjection(typeof(Select2<GLTran,
    LeftJoin<FAAccrualTran, On<GLTran.tranID, Equal<FAAccrualTran.tranID>>>,
    Where<GLTran.module, NotEqual<BatchModule.moduleFA>, And<GLTran.released, Equal<True>>>>), new Type[] { typeof(FAAccrualTran) })]
[PXSubstitute(GraphType = typeof(AssetGLTransactions))]
...
[PXSubstitute(GraphType = typeof(AssetMaint))]
public partial class FAAccrualTranCst : FAAccrualTran
{
    ...
}

You can decorate a DAC with PXSubstituteAttribute multiple times: 1 PXSubstituteAttribute per 1 BLC, for which custom FAAccrualTranCst will be used instead of the base FAAccrualTran class.

If you specify no value for GraphType property of the PXSubstituteAttribute, your custom DAC will replace its base DAC in all BLCs.