Adding a range against an aggregate value

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.


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_purchLine.addRange(fieldNum(purchLine,lineAmount)).value("<10"); //This adds the range at the line level not the aggregate value

qr = new queryRun(query);



purchLine = qr.get(tableNum(purchLine));

info(StrFmt("%1 %2",purchLine.PurchId, purchLine.LineAmount));



Sorry for the late response.

Take a look at this :

Very interesting post, thanks for that, I’d almost forgotten I had put this up here.

I did actually come up with a way to solve the problem though, I used a view on PurchLine with a group by PURCHID and sum on LINEAMOUNT. You can then base a query object on that and put a range on the summed LINEAMOUNT. Works perfectly.