Dynamics Community Forum Thread Details

Expression in Query Ranges

Hello Guys!

I am new in Microsoft Dynamics D365 Development and need your help regarding expression in query ranges.

I am trying to develop query into X++ query as follows but it did not work for me.

Query q = new Query();
QueryRun qr;
QueryBuildDataSource qbdsinventtrans, qbdsStockreport;
QueryBuildRange qbr, qbr1;
InventTrans inventTrans;
stockReport Stockreport;

// Add data source//
qbdsinventtrans=q.addDataSource(tableNum(inventTrans));
//Field Selection//
qbdsinventtrans.addSelectionField(fieldNum(InventTrans , ItemId)) ;
qbdsinventtrans.addSelectionField(fieldNum(InventTrans , inventDimId)) ;
qbdsinventtrans.addSelectionField(fieldNum(InventTrans , DatePhysical)) ;
qbdsinventtrans.addSelectionField(fieldNum(InventTrans,CostAmountAdjustment),SelectionField::Sum);
qbdsinventtrans.addSelectionField(fieldNum(InventTrans, Qty),SelectionField::Sum);
qbdsinventtrans.addSelectionField(fieldNum(InventTrans, CostAmountPhysical),selectionField::Sum);
//Add Group by field//
qbdsinventtrans.addGroupByField(fieldNum(InventTrans, ItemId)) ;
qbdsinventtrans.addGroupByField(fieldNum(InventTrans,InventDimId)) ;
qbdsinventtrans.addGroupByField(fieldNum(InventTrans,DatePhysical)) ;
//Add Range//
qbdsinventtrans.addRange(fieldNum(InventTrans,DatePhysical)).value(queryValue(Global::dateNull())) ;
qbdsinventtrans.addRange(fieldNum(inventtrans, Updatestatus)).value(queryValue(Updatestatus::No)) ;
qbdsinventtrans.addRange(fieldNum(InventTrans,DataAreaId)).value(queryValue(curExt())) ;
//Add data other Source
qbdsStockreport=qbdsinventtrans.addDataSource(tableNum(stockReport));
qbdsStockreport.relations(true) ;
qbdsStockreport.joinMode(JoinMode::InnerJoin);
qbdsStockreport.fetchMode(QueryFetchMode::One2One);
//qbdsStockreport.addLink(fieldNum(InventTrans,ItemId), fieldNum(stockReport,ItemId));
//qbdsStockreport.addLink(fieldNum(InventTrans,InventDimId), fieldNum(stockReport,InventDimId));
//Add Range
qbr=qbdsStockreport.addRange(fieldNum(stockReport,TransDate));
qbr1=qbdsinventtrans.addRange(fieldNum(InventTrans,DatePhysical));

Here actualy i want like this

/Where inventTrans.DatePhysical < StockReport.TransDate./

To acheive above expression i am trying as below but it did not work for me. Kindly help

qbr=qbdsStockreport.addRange(fieldNum(stockReport,TransDate));
qbr1=qbdsinventtrans.addRange(fieldNum(InventTrans,DatePhysical));

qbr.value(strFmt('(TransDate > %1)', InventTrans.Datephysical));

qr = new QueryRun(q) ;
while (qr.next())

As you see, knowing just that it "it did not work" doesn't allow you to identify the problem. You'll need do a better job when debugging the problem.

I see that your syntax is wrong, but it'll be beneficial for you to learn more about debugging of queries. First of all, notice that you can see query strings when looking at Query and QueryBuildDataSource objects in debugger. You can also call toString() method on these objects (which is what the debugger does). Looking at the query generated by your code often makes the problem much easier to spot.

In your case, you'll see that your query doesn't resemble what you wanted at all. You aren't comparing TransDate with the value of DatePhysical field, you're comparing it with the value of DatePhysical field of inventTrans buffer, which is always empty.

Also, learn how the syntax should look like: Using Expressions in Query Ranges.

Quick Links