Dynamic Branch Filters in Acumatica Generic Inquiries

Acumatica's Generic Inquiry feature is a powerful tool for creating custom reports and data views without writing code. However, when you need to filter inquiries based on dynamic values like the current user's branch, you need a custom solution. In this article, we'll explore how to create a reusable CurrentBranchPlaceholder that can be used as a variable in any Generic Inquiry.

The Challenge

Acumatica Generic Inquiries support parameters, but they typically require user input. For branch-based filtering, you want the system to automatically detect the user's branch and apply the filter without any user intervention.

The solution involves creating a custom BQL (Business Query Language) operand that dynamically returns the current user's branch ID. This operand can then be used as a variable in any Generic Inquiry.

The Solution: CurrentBranchPlaceholder

The CurrentBranchPlaceholder class creates a DAC (Data Access Class) with a calculated field that returns the current user's branch ID at runtime.

Complete Code

using PX.Data;
using PX.Data.SQLTree;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PX.Objects.Common
{
    [PXCacheName("Current Branch Placeholder")]
    public class CurrentBranchPlaceholder : IBqlTable
    {
        #region CurrentBranch

        public sealed class CurrentBranchOp :
            IBqlCreator, IBqlOperand
        {
            public void Verify(PXCache cache, object item, List<object> pars, ref bool? result, ref object value)
            {
                value = cache.Graph.Accessinfo.BranchID;
            }

            // For Acumatica 2018 R1 and earlier
            public void Parse(PXGraph graph, List<IBqlParameter> pars, List<Type> tables, 
                List<Type> fields, List<IBqlSortColumn> sortColumns, 
                StringBuilder text, BqlCommand.Selection selection)
            {
                if (graph != null && text != null)
                {
                    PXMutableCollection.AddMutableItem(this);
                    text.Append(' ');
                    text.Append(graph.Accessinfo.BranchID);
                    text.Append(' ');
                }
            }

            // For Acumatica 2019 R1 and later
            public bool AppendExpression(ref SQLExpression exp, PXGraph graph, 
                BqlCommandInfo info, BqlCommand.Selection selection)
            {
                if (graph == null || !info.BuildExpression) return true;
                PXMutableCollection.AddMutableItem(this);
                exp = new SQLConst(graph.Accessinfo.BranchID);
                return true;
            }
        }

        public abstract class currentBranch : IBqlField { }

        [PXDBCalced(typeof(CurrentBranchOp), typeof(int?))]
        [PXUIField(DisplayName = "Current Branch")]
        public virtual int? CurrentBranch { get; set; }

        #endregion
    }
}

Code Walkthrough

1. DAC Declaration

[PXCacheName("Current Branch Placeholder")]
public class CurrentBranchPlaceholder : IBqlTable

• PXCacheName: Gives the DAC a friendly name for display in the system
• IBqlTable: Marks this as a BQL-compatible table
• Purpose: This DAC doesn't map to a database table; it's a placeholder for dynamic values

2. The IBqlOperand Implementation

public sealed class CurrentBranchOp :
    IBqlCreator, IBqlOperand

The CurrentBranchOp class implements two interfaces:

• IBqlOperand: Allows this class to be used as a BQL operand in queries
• IBqlCreator: Enables the class to generate SQL expressions

3. The Verify Method

public void Verify(PXCache cache, object item, List<object> pars, ref bool? result, ref object value)
{
    value = cache.Graph.Accessinfo.BranchID;
}

The Verify method is called when the field is evaluated. It:

• Retrieves the current user's branch ID from Accessinfo.BranchID
• Sets it as the value of the field
• This is the primary method that returns the dynamic value

4. The Parse Method (Legacy Support)

public void Parse(PXGraph graph, List<IBqlParameter> pars, List<Type> tables, 
    List<Type> fields, List<IBqlSortColumn> sortColumns, 
    StringBuilder text, BqlCommand.Selection selection)
{
    if (graph != null && text != null)
    {
        PXMutableCollection.AddMutableItem(this);
        text.Append(' ');
        text.Append(graph.Accessinfo.BranchID);
        text.Append(' ');
    }
}

The Parse method is used in older versions of Acumatica (2018 R1 and earlier). It:

• Adds the current item to the mutable collection
• Appends the current branch ID directly to the SQL text
• Ensures backward compatibility with older Acumatica versions

5. The AppendExpression Method (Modern Approach)

public bool AppendExpression(ref SQLExpression exp, PXGraph graph, 
    BqlCommandInfo info, BqlCommand.Selection selection)
{
    if (graph == null || !info.BuildExpression) return true;
    PXMutableCollection.AddMutableItem(this);
    exp = new SQLConst(graph.Accessinfo.BranchID);
    return true;
}

The AppendExpression method is used in newer Acumatica versions (2019 R1 and later):

• Creates a SQLConst expression with the current branch ID
• Uses the modern SQL expression API
• More efficient and maintainable than the Parse method

6. Field Declaration

public abstract class currentBranch : IBqlField { }

[PXDBCalced(typeof(CurrentBranchOp), typeof(int?))]
[PXUIField(DisplayName = "Current Branch")]
public virtual int? CurrentBranch { get; set; }

• currentBranch: Abstract class defining the BQL field
• PXDBCalced: Marks this as a calculated field using CurrentBranchOp
• PXUIField: Sets the display name for the UI
• CurrentBranch: The actual property that stores the value

How to Use in Generic Inquiries

Step 1: Publish the Customization

1. Add the code to your Acumatica customization project
2. Build and publish the customization
3. The CurrentBranchPlaceholder DAC will now be available in the system

Step 2: Create or Modify a Generic Inquiry

1. Go to Generic Inquiry (SM208000)
2. Create a new inquiry or open an existing one
3. Add the CurrentBranchPlaceholder as a data source

Step 3: Add the Filter Condition

1. In the Conditions tab, add a new condition
2. Select the field you want to filter (e.g., BranchID)
3. Choose the operator (e.g., Equals)
4. Select the CurrentBranch field from CurrentBranchPlaceholder
5. Save the inquiry

Example: SOOrder Inquiry with Branch Filter

Here's how a Generic Inquiry filtering sales orders by branch would look:

Using in Custom Code

You can also use this placeholder directly in your C# code:

public class MyGraph : PXGraph<MyGraph>
{
    public PXSelect<SOOrder, 
        Where<SOOrder.branchID, Equal<CurrentBranchPlaceholder.currentBranch>>> Orders;

    public void GetBranchOrders()
    {
        // This will automatically filter by current branch
        var orders = Orders.Select();
    }
}

Version Compatibility

Best Practices

Common Pitfalls to Avoid

Extending the Concept

You can create similar placeholders for other dynamic values:

• CurrentUserPlaceholder: Returns the current user's ID
• CurrentCompanyPlaceholder: Returns the current company/tenant ID
• CurrentDatePlaceholder: Returns the current system date
• CurrentYearPlaceholder: Returns the current fiscal year

// Example: Current User Placeholder
public sealed class CurrentUserOp : IBqlCreator, IBqlOperand
{
    public void Verify(PXCache cache, object item, List<object> pars, ref bool? result, ref object value)
    {
        value = cache.Graph.Accessinfo.UserID;
    }
    
    // ... Parse and AppendExpression methods
}

Conclusion

The CurrentBranchPlaceholder is a powerful tool for implementing automatic branch-based filtering across your Acumatica Generic Inquiries and custom code. By creating a reusable BQL operand, you ensure that users only see data relevant to their branch without any manual intervention.

This approach is both secure and user-friendly. It reduces the risk of data leakage, eliminates the need for users to remember to apply filters, and provides a consistent experience across all inquiries.