Using PXRestrictor with Complex Joins in Acumatica

The PXRestrictor attribute is a powerful tool for implementing validation rules in Acumatica. However, when you need to join tables that aren't directly available, you need advanced techniques [citation:5].

⚡ The Problem: PXRestrictor doesn't support adding joins directly. When you need to validate against data in a table not already included in the query, you need a workaround [citation:5].

The Challenge

Consider this scenario: You want to restrict a field based on a condition from a table that isn't included in the current query [citation:5].

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

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

❌ Problem: The BAccountClass table is not defined in the query from the PXSelector. The Where condition will not work and may cause SQL interpretation errors [citation:5].

Solution: Using EXISTS Subqueries

You can work around this limitation by using a subquery with the Exists<> BQL command [citation:5].

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; }
}

✅ Solution: The Exists subquery checks for the existence of a related record with the required condition [citation:5].

Advanced Technique: Fluent BQL with Foreign Keys

Acumatica provides even more elegant solutions using Fluent BQL and Foreign Key APIs [citation:5].

[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>>>>),
    "Worksheet has no confirmed pick lists")]

💡 Pro Tip: SOPicker.FK.Worksheet uses the Foreign Key API which defines the relationship between SOPicker and SOPickingWorksheet. This is more maintainable and performant [citation:5].

Foreign Key Definition

To use the Foreign Key API, you define the relationship in your DAC [citation:5]:

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

This defines the relationship between SOPicker and SOPickingWorksheet by worksheetNbr, enabling cleaner BQL syntax [citation:5].

Performance Considerations

While these techniques are powerful, they should be used with performance in mind:

Subqueries: The Exists approach may have performance implications for large datasets [citation:5]
Foreign Keys: Using FK APIs is generally more performant and cleaner than raw subqueries [citation:5]
Not Always Applicable: Consider alternative approaches when performance is critical [citation:5]

Best Practices

✅ Use Foreign Keys When Possible

Define FK relationships for cleaner, more maintainable code [citation:5].

✅ Test Performance

Validate performance with your data volumes before production deployment [citation:5].

✅ Use Not<Exists>

You can also use Not<Exists<...>> for negative conditions [citation:5].

✅ Keep It Simple

Consider alternative approaches when the subquery becomes too complex [citation:5].

Conclusion

📌 Key Takeaway: When PXRestrictor can't join to the required table directly, use Exists subqueries or the Foreign Key API to implement complex validation rules. The FK approach is cleaner and more performant when available [citation:5].