How to use current company in BQL query

user_6668657 0
Viewed 220

I need to know how I can get the user's current company so that I can use it in the Where clause of a BQL query. I'm not talking about the tenant "company". I'm talking about the companies that are defined on screen CS101500. I'm not seeing anything on the AccessInfo that seems to indicate the current Company. The current Branch ID is there but, I need the company to which the branch belongs.

Using version 21.203

TIA!

acumatica
3 Answers

This will require the creation of a custom BQL element class.

IBqlOperand - A BQL Scalar operand

IBqlCreator - A Bql elements creator that requires implementation of AppendExpression and Verify methods.

public class CurrentOrganization : IBqlCreator, IBqlOperand
{

    #region Methods

    #region AppendExpression

    /// <summary>
    /// Appends the SQL tree expression that corresponds to the BQL command to an SQL tree query.
    /// </summary>
    /// <param name="exp">The SQL tree expression to be appended.</param>
    /// <param name="graph">A graph instance.</param>
    /// <param name="info">The information about the BQL command.</param>
    /// <param name="selection">The fragment of the BQL command that is translated to an SQL tree expression.</param>
    /// <returns></returns>
    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.BranchOrganizationID());

        return true;
    }

    #endregion

    #region Verify

    /// <summary>
    /// No Clue.
    /// </summary>
    /// <param name="cache"></param>
    /// <param name="item"></param>
    /// <param name="pars"></param>
    /// <param name="result"></param>
    /// <param name="value"></param>
    public void Verify(PXCache cache, object item, List<object> pars, ref bool? result, ref object value)
    {
        value = cache.Graph.BranchOrganizationID();
    }

    #endregion

    #endregion

}

The method call .BranchOrganizationID() is a PXGraph extension method that returns the OrganizationID from a Branch IPrefetchable

Calls to this prefetchable can be replaced with a BQL PXSelect :

PXSelect<Branch, Where<Branch.branchID, Equal<Current<AccessInfo.branchID>>>>.Select(...)

Example usage would be as follows :

    [PXDefault(typeof(Search<Branch.branchID,Where<Branch.organizationID,Equal<CurrentOrganization>>>))]
user_16778900

You can try to use this code

 public int? GetOrganizationID(PXGraph graph, int? branchID)
 {
    int? accountID = ((Organization)OrganizationMaint.FindOrganizationByID(graph, PXAccess.GetParentOrganizationID(branchID))).BAccountID;
    return accountID;
 }
user_71988050

This is the final code that got what I needed, per Josh's solution. Van Hoesen for the win!!

public class CurrentOrganization : IBqlCreator, IBqlOperand
{
    public virtual 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.BranchOrganizationID());
        Branch branch = PXSelect<Branch, Where<Branch.branchID, Equal<Current<AccessInfo.branchID>>>>.Select(graph);
        exp = new SQLConst( branch.OrganizationID);

        return true;

    }
    public void Verify(PXCache cache, object item, List<object> pars, ref bool? result, ref object value)
    {
        Branch branch = PXSelect<Branch, Where<Branch.branchID, Equal<Current<AccessInfo.branchID>>>>.Select(cache.Graph);
        value = branch.OrganizationID;
        //value = cache.Graph.BranchOrganizationID();
    }
}
user_66686570
Related