Hi, I’ve been asked to write a query that is basically “Show $ amount of all Open Purchase orders under $10”. The query is fairly simple, but I’m running into the problem of I cant add a range onto the SUM of purchLine.LineAmount so I only show records under $10.
Does anyone have a suggestion? Oh and this is for a Cue so it has to be a Query.
Code:
query query = new query();
queryBuildDataSource qbds_purchTable, qbds_purchLine;
queryRun qr;
purchLine purchLine;
;
qbds_purchLine = query.addDataSource(tableNum(purchline));
qbds_purchTable = qbds_purchLine.addDataSource(tableNum(purchTable));
qbds_purchTable.addLink(fieldNum(purchLine,purchId),fieldNum(purchTable,purchId));
qbds_purchLine.addGroupByField(fieldNum(purchLine,purchId));
qbds_purchLine.addSelectionField(fieldNum(purchLine,lineAmount),selectionField::Sum);
qbds_purchTable.addRange(fieldNum(purchTable,purchStatus)).value(sysQuery::value(purchStatus::Backorder));
// qbds_purchLine.addRange(fieldNum(purchLine,lineAmount)).value("<10"); //This adds the range at the line level not the aggregate value
qr = new queryRun(query);
while(qr.next())
{
purchLine = qr.get(tableNum(purchLine));
info(StrFmt("%1 %2",purchLine.PurchId, purchLine.LineAmount));
}