How to use BQL In Operator with Select2 query / PXProjection and list of values

Question

I am trying to replicate the following type of SQL query that you can perform in SQL Server...the important part here is the WHERE clause: Select InventoryCD from InventoryItem WHERE InventoryCD IN ('123123', '154677', '445899', '998766')

It works perfectly using the IN3<> operator and a series of string constants: i.e. And<InventoryItem.inventoryCD, In3<constantA,constantB,constantC>,

However, I need to be able to do this with an arbitrarily long list of values in an array, and I need to be able to set the values dynamically at runtime.

I'm not sure what type I need to pass in to the IN<> statement in my PXProjection query. I have been playing around with the following approach, but this throws a compiler error.

public class SOSiteStatusFilterExt : PXCacheExtension<SOSiteStatusFilter>
{
    public static bool IsActive()
    {
        return true;
    }
    public abstract class searchitemsarray : PX.Data.IBqlField
    { 
    }
    [PXUnboundDefault()]
    public virtual string[] Searchitemsarray { get; set; }

}

I think maybe I need an array of PXString objects? I'm really not sure, and there isn't any documentation that is helpful. Can anyone help?

This shows how to do it with a regular PXSelect: https://asiablog.acumatica.com/2017/11/sql-in-operator-in-bql.html

But I need to be able to pass in the correct type using Select2...

Answer 1

For reference I will post here the example mentioned by Hugues in the comments.

If you need to generate a query with an arbitrary list of values generated at runtime like this:

Select * from InventoryItem InventoryItem
Where InventoryItem.InventoryCD IN ('123123', '154677', '445899', '998766')
Order by InventoryItem.InventoryCD

You would write something like this:

Object[] values = new String[] { "123123", "154677", "445899", "998766" };

InventoryItem item = PXSelect<InventoryItem,
      Where<InventoryItem.inventoryCD,
      In<Required<InventoryItem.inventoryCD>>>>.Select(Base, values);

Please note that In<> operator is available only with Required<> parameter and you need to pass array of possible values manually to Select(…) method parameters. So you need to fill this array with your list before calling the Select method.

Also, the Required<> parameter should be used only in the BQL statements that are directly executed in the application code. The data views that are queried from the UI will not work if they contain Required<> parameters.

Answer 2

I ended up creating 100 variables and using the BQL OR operator, i.e.

  And2<Where<InventoryItem.inventoryCD, Equal<CurrentValue<SOSiteStatusFilterExt.Pagefilter1>>,
        Or<InventoryItem.inventoryCD, Equal<CurrentValue<SOSiteStatusFilterExt.Pagefilter2>>,
        Or<InventoryItem.inventoryCD, Equal<CurrentValue<SOSiteStatusFilterExt.Pagefilter3>>,
        Or<InventoryItem.inventoryCD, Equal<CurrentValue<SOSiteStatusFilterExt.Pagefilter4>>,

 etc...etc...

You can then set the value of Pagefilter1, 2, etc inside of the FieldSelecting event for SOSiteStatusFilter.inventory, as an example. The key insight here, which isn't that obvious to the uninitiated in Acumatica, is that all variables parameterized in SQL Server via BQL are nullable. If the variable is null when the query is run, SQL Server automatically disables that variable using a "bit flipping" approach to disable that variable in the SQL procedure call. In normal T-SQL, this would throw an error. But Acumatica's framework handles the case of a NULL field equality by disabling that variable inside the SQL procedure before the equality is evaluated.

Performance with this approach was very good, especially because we are querying on an indexed key field (InventoryCD isn't technically the primary key but it is basically a close second).