Hi Pioneers,
I am new to Axapta, actually I am looking forward to develop a report , with following fields and functionality…kindly suggest me the possible code, for my knowledge.
Dialog : ToDate, FromDate, Warehouse
Fields Table
Item Group InventTable
Item No InventTrans
ItemName InventTable
QTY Invent Trans
ONLY FOR INWARD TRANSACTION OF THE WAREHOUSE, NOT OUTWARD.
Hi Sudhansu,
Consider Inventtrans table with status receipt as Received / Purchased…
and status issue as received/ purchased…
and consider the posting type(reference category) in Inventtransorigin table as Inventtransaction, Purch, sales, Production, etc…
based on the recid relation between inventtrans and inventtransorigin tables(In 2012)…
Inventtrans table is considered in 2009…(based on Inventtrans type)
how to use dialogs:
public Object dialog(Object _dialog)
{
DialogRunbase dialog = _dialog;
;
fieldFromDate = dialog.addField(typeid(FromDate));
fieldToDate = dialog.addField(typeid(ToDate));
return dialog;
}
public boolean getFromDialog()
{
str dateInString;
boolean ret;
;
date1 = fieldFromDate.value();
date2 = fieldtodate.value();
// date2 = today();
if (date1 == datenull())
{
select firstonly ledgerperiod;
date1 = ledgerperiod.PeriodStart;
}
/*if (date2 == datenull())
{
date2 = today();
}*/
// info(strfmt(‘%1’,date2));
else if(date2 != today())
{
ret = checkfailed(‘Todate should be current date’);
}
else if (date1 > date2)
{
ret = checkfailed(‘Fromdate should not be more than todate’);
}
else
{
ret = true;
}
return ret;
}
filter the inventory transactions with warehouse that is selected at the dialog…
Thanks for your prompt reply, but how we will fetch the data ???
let me know whether you are working on this report in ax 2009 or ax 2012…
AX 2009
Hi,
while select inventTrans
order by inventTrans.DatePhysical
join inventDim
where inventTrans.ItemId == inventTable.ItemId
&& inventTrans.inventDimId == inventDim.inventDimId
&& inventDim.InventlocationId == warehousevalue
&& (inventTrans.TransType == InventTransType::Purch
|| inventTrans.TransType == InventTransType::Production
|| inventTrans.TransType == InventTransType::ProdLine
|| inventTrans.TransType == inventTransType::InventTransaction
|| inventTrans.TransType == inventTransType::InventCounting
|| inventTrans.TransType == InventTransType::InventLossProfit
|| inventTrans.TransType == inventTransType::Sales)
&& (inventTrans.StatusReceipt == StatusReceipt::Received
|| inventTrans.StatusReceipt== StatusReceipt::Purchased)
&& inventTrans.Qty > 0
&& inventTrans.DatePhysical >= fromdatevalue
&& inventTrans.DatePhysical <= todatevalue
{
writ your logic…
}
where warehousevalue, fromdatevalue, todatevalue are the values that we are selecting at the dialogs…
Thanks A Ton…
Well can we fetch the data using the QUERY ???
Yes, we can,
But handling multiple datasources is difficult in writing a query in a report…
Sir if u don’t mind, may I have such one please ??? I shall be very grateful to you.
Hi,
static void Job45(Args _args)
{
str warehouseval;// get this value from dialog;
query q = new query();
querybuilddatasource qbds, qbds2;
;
qbds = q.addDataSource(tablenum(inventtrans));
qbds2 = qbds.addDataSource(tablenum(inventdim));
qbds2.addLink(fieldnum(inventtrans, inventdimid), fieldnum(inventdim, inventdimid));
qbds2.addRange(fieldnum(inventdim, inventdimid)).value(QueryValue(warehouseval));
qbds.addRange(fieldnum(inventtrans, transtype)).value(QueryValue(Inventtranstype::Purch));
qbds.addRange(fieldnum(inventtrans, transtype)).value(QueryValue(Inventtranstype::Production));
qbds.addRange(fieldnum(inventtrans, transtype)).value(QueryValue(Inventtranstype::ProdLine));
qbds.addRange(fieldnum(inventtrans, transtype)).value(QueryValue(Inventtranstype::InventTransaction));
qbds.addRange(fieldnum(inventtrans, transtype)).value(QueryValue(Inventtranstype::InventCounting));
qbds.addRange(fieldnum(inventtrans, transtype)).value(QueryValue(Inventtranstype::InventLossProfit));
qbds.addRange(fieldnum(inventtrans, transtype)).value(QueryValue(Inventtranstype::Sales));
qbds.addRange(fieldnum(inventtrans, statusreceipt)).value(QueryValue(statusreceipt::Received));
qbds.addRange(fieldnum(inventtrans, statusreceipt)).value(QueryValue(statusreceipt::Purchased));
qbds.addRange(fieldnum(inventtrans, statusreceipt)).value(QueryValue(statusreceipt::Purchased));
// filter the dates from the inventtrans table as well…
}
it might solve your query…
Again thanks a ton sir, for your incredible & appreciable effort…one thing, I just wanted to know that, I am also giving the datewise dialog functionality also sir…but here how it will be defined and associated with the query ??? I am new user, so please help me.
static void Job45(Args _args)
{
str warehouseval;// get this value from dialog;
query q = new query();
querybuilddatasource qbds, qbds2;
date1980 d1, d2;// get this value from dialog;
;
qbds = q.addDataSource(tablenum(inventtrans));
qbds2 = qbds.addDataSource(tablenum(inventdim));
qbds2.addLink(fieldnum(inventtrans, inventdimid), fieldnum(inventdim, inventdimid));
qbds2.addRange(fieldnum(inventdim, inventdimid)).value(QueryValue(warehouseval));
qbds.addRange(fieldnum(inventtrans, transtype)).value(QueryValue(Inventtranstype::Purch));
qbds.addRange(fieldnum(inventtrans, transtype)).value(QueryValue(Inventtranstype::Production));
qbds.addRange(fieldnum(inventtrans, transtype)).value(QueryValue(Inventtranstype::ProdLine));
qbds.addRange(fieldnum(inventtrans, transtype)).value(QueryValue(Inventtranstype::InventTransaction));
qbds.addRange(fieldnum(inventtrans, transtype)).value(QueryValue(Inventtranstype::InventCounting));
qbds.addRange(fieldnum(inventtrans, transtype)).value(QueryValue(Inventtranstype::InventLossProfit));
qbds.addRange(fieldnum(inventtrans, transtype)).value(QueryValue(Inventtranstype::Sales));
qbds.addRange(fieldnum(inventtrans, statusreceipt)).value(QueryValue(statusreceipt::Received));
qbds.addRange(fieldnum(inventtrans, statusreceipt)).value(QueryValue(statusreceipt::Purchased));
qbds.addRange(fieldnum(inventtrans, statusreceipt)).value(QueryValue(statusreceipt::Purchased));
//specify date ranges from dialog field datevalues…
qbds.addRange(fieldnum(inventtrans, datephysical)).value(strfmt(‘>=%1’, d1));
qbds.addRange(fieldnum(inventtrans, datephysical)).value(strfmt(‘<=%1’, d2));
// filter the dates from the inventtrans table as well…
}
Sir, Thanks A lot…May I have your email id for adding to my messenger’s list ??? Please send me in private messaging in this website.
waiting for ur reply sir 