How do i get the Count of InventSerialId from InventDim

Question

How do i create a query or using select to get the count of InventSerialId base on a given Itemid, InventLocationId and where the inventSum.PhysicalInvent > 0 or inventSum.Picked > 0.

Answer 1

This is not directly possible using X++.

Consider:

static void _TestDim(Args _args)
{
    ItemId itemId = '123';
    InventSum inventSum;
    InventDim inventDim;
    Query q = new Query();
    QueryBuildDataSource ds = q.addDataSource(tableNum(InventSum), 's');
    QueryRun qr;
    ;
//  ds.addRange(fieldNum(InventSum,ItemId)).value(queryValue(itemId));
    ds.addRange(fieldNum(InventSum,Closed)).value(queryValue(NoYes::No));
    ds.addGroupByField(fieldNum(InventSum,ItemId));
    ds.addSelectionField(fieldNum(InventSum,PhysicalInvent),SelectionField::Sum);
    ds.addSelectionField(fieldNum(InventSum,Picked),SelectionField::Sum);
    q.addHavingFilter(ds, fieldStr(InventSum,PhysicalInvent), AggregateFunction::Sum).value('>0');
//  q.addHavingFilter(ds, fieldStr(InventSum,Picked), AggregateFunction::Sum).value('((s.Picked >0)||(s.PhysicalInvent>0))'); // This is not allowed
    ds = ds.addDataSource(tableNum(InventDim), 'd');
    ds.joinMode(JoinMode::InnerJoin);
    ds.relations(true);
    ds.addGroupByField(fieldNum(InventDim,InventSerialId));
    ds.addRange(fieldNum(InventDim,InventSerialId)).value('>""');
    info(q.dataSourceNo(1).toString());
    qr = new QueryRun(q);
    while (qr.next())
    {
        inventSum = qr.getNo(1);
        inventDim = qr.getNo(2);
        info(strFmt('%1 %2: %3 %4', inventSum.ItemId, inventDim.InventSerialId, inventSum.PhysicalInvent, inventSum.Picked));
        break;
    }
}

Here you aggreate PhysicalInvent and picked, and you can apply a having-filter using the query method addHavingFilter.

However, you cannot have that combined with another having-filter using a SQL or-statement.
If you try with a query expression, you will get a run-time error.

What you can do is create two views with each filter, then combine them using a union view. This is tricky but doable.

The first should select positive PhysicalInvent and the second should select PhysicalInvent == 0 and positive Picked.