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

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).