// Container or list to hold the results
List discounts = new List(Types::String); // or whatever collection you're using
while select priceDiscAdmTable
where priceDiscAdmTable.Posted == 1
join priceDiscAdmTrans
order by priceDiscAdmTrans.ModifiedDateTime desc
where priceDiscAdmTrans.JournalNum == priceDiscAdmTable.JournalNum
&& priceDiscAdmTrans.ItemRelation == lineDisc
&& priceDiscAdmTrans.FromDate <= curDate
&& priceDiscAdmTrans.ToDate >= curDate
{
discounts.addEnd(priceDiscAdmTrans);
}
Reason why the second approach works is the order by comes after the join keyword and the X++ parser associates it with the joined table priceDiscAdmTrans.
Alternatively, would recommend using a query object in this case.
Query query = new Query();
QueryBuildDataSource qbdsHeader,qbdsLine;
QueryRun qr;
//Header
qbdsHeader = query.addDataSource(tablenum(PriceDiscAdmTable));
qbdsHeader.addRange(fieldnum(PriceDiscAdmTable,Posted)).value(queryvalue(1));
//PriceDisc Adm lines
qbdsLine = qbdsHeader.addDataSource(tablenum(PriceDiscAdmTrans));
qbdsLine.relations(true);
qbdsLine.joinMode(JoinMode::InnerJoin);
qbdsLine.addRange(fieldnum(PriceDiscAdmTrans,ItemRelation)).value(lineDisc);
qbdsLine.addRange(fieldnum(PriceDiscAdmTrans,FromDate)).valueLessThanDate(curDate);
qbdsLine.addRange(fieldnum(PriceDiscAdmTrans,ToDate)).valueDateGreaterThan(curDate);
//Sort by line ModifiedDateTime
qbdsLine.addSortField(fieldnum(PriceDiscAdmTrans,ModifiedDateTime),SortOrder::Descending);
qr = new QueryRun(query);
while(qr.next())
{
priceDiscAdmTrans = qr.get(tablenum(priceDiscAdmTrans));
discounts.addEnd(priceDiscAdmTrans);
}
Hope this helps. Happy to answer questions, if any.
