Adding a range against an aggregate value

Finance and Operations/AX Developer Forum
1

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));

}

2

Hi,

Sorry for the late response.

Take a look at this :

http://www.axaptapedia.com/Having_Clause

3

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.