Hi,
Please find the example below to get total using query classes,
static void Query_Example(Args _args)
{
Query q;
Queryrun qr;
QueryBuildRange qbr;
QueryBuildDataSource qbds;
InventTrans iv;
Real Total;
str range;
// Instantiate the query class.
q = new query("Inventory_Transactions");
// Create a data source by using the InventTrans table.
qbds = q.addDataSource(tablenum(InventTrans));
// Select only the Qty field, and then sum the Qty field.
qbds.addSelectionField(fieldnum(InventTrans,Qty),selectionfield::Sum);
// Set the range to the ItemId field.
qbr = qbds.addRange(fieldnum(InventTrans,ItemId));
// The range for the where statement specifies an 'or' statement.
qbr.value(strfmt('((%1 == "%2") || (%1 == "%3"))',fieldstr(inventtrans,ItemId),'OL-1500','OL-1000'));
// The following is the alternative way to enter the range.
// This also limits the selection on the ItemId values.
range = strfmt('((ItemId == "%1")||(ItemID =="%2"))',queryvalue('OL-1500'),queryvalue('OL-1000'));
qbr.value(range);
// Create the join to the InventDim table.
qbds = qbds.addDataSource(tablenum(InventDim));
// Specify the table relationship.
qbds.relations(true);
// Indicate the order mode as the grouping.
qbds.orderMode(ordermode::GroupBy);
// Specify the grouping on the InventBatchId field.
qbds.addSortField(fieldnum(InventDim,InventBatchId));
// Instantiate the QueryRun class for the form.
qr = new QueryRun(q);
// While there are records in the query, continue.
while (qr.next())
{
// Set the value of the query to the iv table.
bufferiv = qr.get(tablenum(InventTrans));
// Create the value of the total field.
total = iv.Qty;
}
// Specify the quantity for the item.
info(strfmt("Quantity: %1",total));
// Indicate the SQL string that is used for the query.
info (qr.query().dataSourceNo(1).toString());
}
Best,
Uday.
