am using ax 2009 i write this code to retrive slow item movment using dialog to enter from to date (period)
but its not working
the Code :
InventTable inventtable ;
InventTrans inventtrans ;
Date datefrom , dateto ;
/////////////////////////////// Dialog code ////////////////////////////
dialog dlg;
dialoggroup dlgGroup;
dialogfield dlgFielddate1 ;
dialogfield dlgFielddate2 ;
dlg = new dialog(“Item Movment”);
dlgGroup = dlg.addGroup(“Item Movment”);
dlgFielddate2 = dlg.addField(TypeID(FromDate),‘From’);
dlgFielddate1 = dlg.addField(TypeID(ToDate),‘To’);
dlg.run();
if(dlg.closedOk() && dlgFielddate2.value()&& dlgFielddate1.value() )
{
datefrom =(dlgFielddate2.value() );
dateto = (dlgFielddate1.value());
super() ;
////////////////////////////////////////// code for retrieving item not moved during specific period //////////////////////////////
while select inventTable notExists join inventTrans where ( (inventtable.ItemId == inventtrans.ItemId ) && ( inventtrans.DatePhysical>= datefrom ) && (inventtrans.DatePhysical <= dateto ))
}
can any one help me
Which part of the code doesn’t work? The dialog? The query?
the query it is returning item after the to-date
All right, so if I remove all unrelated code, your problem is that the following query doesn’t return what you expect:
select inventTable
notExists join inventTrans
where inventTable.ItemId == inventtrans.ItemId
&& inventTrans.DatePhysical >= datefrom
&& inventTrans.DatePhysical <= dateto;
Can you give us an example when it returns unexpected data?
firstly thanks for your help,for example i have new item (01830) which is a added at 3/14/2014
when am inserting perid (from 12/1/2011 to 9/30/2013) it return that item ( 01830 ) !
What transaction do you have for item 01830? I obviously don’t know the content of your database.
Trans Type is purchased order but really i want to return item not exist in inventtrans table during the period i insert through the dialog
I don’t believe that the problem is in AX kernel or SQL Server - you probably have different InventTrans than you think. Run a query directly against transactions too see which ones are returned. You’re the only one who can do it, because we don’t have your database.