Joins with PXRestrictor

Hi There,

There is a chance that you wanted to use PXRestrictor, but there is no needed table to provide a validation.

public class DAC
{
    [PXSelector(typeof(BAccount.baccountID))]
    public int Field {get; set;}
}

public class DACExt : PXCacheExtension<DAC>
{
    [PXMergeAttribute(Method = MergeMethod.Append)]
    [PXRestrictorAttribute(typeof(Where<CRCustomerClass.IsInternal, Equal<True>), "")]
    public int Field {get; set;}
}

Please note that “BAccountClass” table is not defined in the querry from the PXSelector. Resultantly, the Where condition “rictorAttribute(typeof(Where<BAccountClass.IsInternal, Equal<True>” will not work and will give you an SQL Interpretatino error.

Further more, there is not way to add a Join to BAccountClass table from the PXRestrictor attribute, since this is not something supported by Acumatica framework yet.

However, there is a relatively easy way to workaround this with subselect query from Where statement using the Exists<> BQL Command.

public class DAC
{
    [PXSelector(typeof(BAccount.baccountID), "")]
    public int Field {get; set;}
}

public class DACExt : PXCacheExtension<DAC>
{
    [PXMergeAttribute(Method = MergeMethod.Append)]
    [PXRestrictorAttribute(typeof(Where<Exists<
        Select<CRCustomerClass,
            Where<CRCustomerClass.IsInternal, Equal<True>,
                And<CRCustomerClass.cRCustomerClassID, Equal<BAccount.ClassID>>>>>>))]
    public int Field {get; set;}
}

This is not a perfect way from the performance perspective, and not always applicable, but can solve quite some of the common cases.

By the way, you also can use FluentBQL and Not<Exists<…>> statement too. Moreover, you can levelup your code using the Foreign Key API. Here is the real example form Acumatica code.

[PXSelector(typeof(
    SelectFrom<SOPickingWorksheet>.
        OrderBy<SOPickingWorksheet.worksheetNbr.Desc>.
        SearchFor<SOPickingWorksheet.worksheetNbr>))]
[PXRestrictor(typeof(Where<Exists<
    SelectFrom<SOPicker>.
        Where<
            SOPicker.FK.Worksheet.
                And<SOPicker.confirmed.IsEqual<True>>>>>),
Msg.WorksheetHasNoConfirmedPickLists)]

Note here “SOPicker.FK.Worksheet”. This is a Foreign Key which defines the relationship between SOPicker and SOPickingWorksheet.

public class Worksheet : SOPickingWorksheet.PK.ForeignKeyOf<SOPicker>.By<worksheetNbr> { }

Have an easy development!