Solution
An example of Query to find InventSerials, when Works Order is known.
An example of achieving same result using While/Select statements.
Class declaration
public class YourClass
{
// Query objects
Query query;
QueryRun queryRun;
QueryBuildDataSource qbdsInventTransOrigin;
QueryBuildDataSource qbdsInventTrans;
QueryBuildDataSource qbdsInventDim;
QueryBuildDataSource qbdsInventSerial;
QueryBuildRange qbr;
// Declare a table buffer
InventTransOrigin inventTransOrigin;
InventTrans inventTrans;
InventDim inventDim;
InventSerial inventSerial;
}
A Method
public void getInventSerials(InventRefId _inventRefId, InventTransType _inventTransType)
{
query = new Query();
// Data sources
qbdsInventTransOrigin = query.addDataSource(tableNum(inventTransOrigin));
qbdsInventTrans = qbdsInventTransOrigin.addDataSource(tableNum(inventTrans));
qbdsInventTrans.relations(true);
qbdsInventTrans.joinMode(JoinMode::InnerJoin);
qbdsInventDim = qbdsInventTrans.addDataSource(tableNum(inventDim));
qbdsInventDim.relations(true);
qbdsInventDim.joinMode(JoinMode::InnerJoin);
qbdsInventSerial = qbdsInventDim.addDataSource(tableNum(inventSerial));
qbdsInventSerial.relations(true);
qbdsInventSerial.joinMode(JoinMode::OuterJoin);
// Ranges and Values
qbr = qbdsInventTransOrigin.addRange(fieldNum(inventTransOrigin, ReferenceCategory));
qbr.value(enum2str(_inventTransType));
qbr.status(RangeStatus::Locked);
qbr = qbdsInventTransOrigin.addRange(fieldNum(inventTransOrigin, ReferenceId));
if(_inventRefId != "")
{
qbr.value(_inventRefId);
}
else
{
error("InventRefId value is empty");
}
qbr.status(RangeStatus::Locked);
// Results sorting
qbdsInventTransOrigin.addSortField(fieldNum(inventTransOrigin, InventTransId));
queryRun = new QueryRun(query);
if(queryRun.prompt()) // Comment 'if' statement if you don't want
{ // to see query dialogue at runtime
while (queryRun.next())
{
inventTransOrigin = queryRun.get(tableNum(inventTransOrigin));
inventTrans = queryRun.get(tableNum(inventTrans));
inventDim = queryRun.get(tableNum(inventDim));
inventSerial = queryRun.get(tableNum(inventSerial));
// Print out results received
info(strFmt("RecId: %1, InventTransId: %2, InventDimId: %3, InventSerialId: %4",
inventTransOrigin.RecId,
inventTransOrigin.InventTransId,
inventTrans.inventDimId,
inventDim.inventSerialId)
);
}
}
}
Main method to run
public static void main (Args _args)
{
YourClass yourClass = new YourClass();
// Provide existing Works Order number and InventTransType as parameters
yourClass.getInventSerials("DON1-000034", InventTransType::Production);
}
Query dialogue (if implemented)
Comments !