Ax 2012: How to add TWO links between two datasources in X++ Query

In my Customization I need to get the From warehouse and To warehouse transaction from the Inventory Transfer Journal.

for this i need to relate  InventJournalTrans and InventTransOrigin tables with InventTransId and ToInventTransId, But here InventTransId is a direct relation, but if i use this relation then i can get only the From warehouse information. The linking is like the below

qbds1.relations(true);
qbds1.addLink(fieldnum(InventJournalTrans,InventTransId),fieldnum(InventTransOrigin,InventTransId));

but i can’t make two relations like

qbds1.relations(true);
qbds1.addLink(fieldnum(InventJournalTrans,InventTransId),fieldnum(InventTransOrigin,InventTransId));qbds1.addLink(fieldnum(InventJournalTrans,ToInventTransId),fieldnum(InventTransOrigin,InventTransId));

so i code like Range setting in X++ query.

CODE: JOB

static void GetFromAndToWarehouse(Args _args)
{
InventJournalTable inventJournalTable;
InventJournalTrans inventJournalTrans;
InventTransOrigin inventTransOrigin;
InventTrans inventTrans;
InventDim inventDim;

Query query;
QueryBuildDataSource qbds,qbds1,qbds2;
QueryBuildRange qbr,qbr1;
QueryRun queryRun;

query=new Query();

qbds=query.addDataSource(tableNum(InventJournalTrans));
qbr = qbds.addRange(fieldNum(InventJournalTrans, JournalId));
qbr.value(queryValue(“00081”));

qbds1 = qbds.addDataSource(tableNum(InventTransOrigin));
qbr1 = qbds1.addRange(fieldNum(InventTransOrigin, InventTransId));
qbr1.value(strFmt(“((%1.INVENTTRANSID == %2.INVENTTRANSID) || (%1.INVENTTRANSID == %2.TOINVENTTRANSID))”,qbds1.name(), qbds.name()));

qbds2=qbds1.addDataSource(tableNum(InventTrans));
qbds2.joinMode(JoinMode::InnerJoin);
qbds2.relations(true);

queryRun = new QueryRun(query);

while (queryRun.next())
{
inventTrans = queryRun.get(tableNum(InventTrans));
inventDim = InventDim::find(inventTrans.InventDimId);
info(strFmt(“%1”,inventDim.InventLocationId));
}

}

 

Advertisements

How to Get Financial Dimensions for a particular Customer using X++ code in Ax 2012

TWO Ways:

Way 1:

static void GetFinancialDimensionValue(Args _args)
{
CustTable custTable ;
DimensionAttributeValueSet DimensionAttributeValueSet;
DimensionAttributeValueSetItem DimensionAttributeValueSetItem;
DimensionAttributeValue DimensionAttributeValue;
DimensionAttribute DimensionAttribute;

while select * from custTable
where custTable .AccountNum == “CU0000001″
join RecId from DimensionAttributeValueSet
where DimensionAttributeValueSet.RecId == custTable .DefaultDimension
join RecId, DisplayValue from DimensionAttributeValueSetItem
where DimensionAttributeValueSetItem.DimensionAttributeValueSet == DimensionAttributeValueSet.RecId
join RecId from DimensionAttributeValue
where DimensionAttributeValue.RecId == DimensionAttributeValueSetItem.DimensionAttributeValue
join RecId, Name from DimensionAttribute
where DimensionAttribute.RecId == DimensionAttributeValue.DimensionAttribute
{
info(DimensionAttribute.Name+”—-“+ DimensionAttributeValueSetItem.DisplayValue);
}
}

Way 2:

static void GetFinancialDimensionValue1(Args _args)
{
CustTable custTable = CustTable::find(“CU0000001”);
DimensionAttributeValueSetStorage dimStorage;
Counter i;

dimStorage = DimensionAttributeValueSetStorage::find(custTable.DefaultDimension);

for (i=1 ; i<= dimStorage.elements() ; i++)
{

//if you want particular Dimension then put if condition like this
//if(strFmt(“%1″,DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name)==”Specialty”)
// {
info(strFmt(“%1 = %2”, DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name,
dimStorage.getDisplayValueByIndex(i)));
//}
}

}

 

How to import CSV file with Progress bar indication in Ax 2012 & Ax 2009

static void ImportCSVwithProgresBar(Args _args)

{

Filename                    fileName;

container                   files,inRecord,toTRec;

CommaIO                     commaIO;

Dialog                      _dialog;

DialogField                 _dialogFieldFileName;

counter                     counter = 0;

counter                     ErrCounter = 0;

counter                     progressCounter = 0;

SysOperationProgress        progress = new SysOperationProgress();

 

InventTable                 inventTable;

ItemID                      searchItem;

int                         totRows;

 

TextBuffer tb;

;

 

_dialog              = new Dialog(‘ImportCSV’);

_dialogFieldFileName = _dialog.addField(ExtendedTypeStr(FileNameOpen), ‘@SYS53125’, ‘@JKI58’);

_dialogFieldFileName.lookupButton(2);

 

if (_dialog.run())

{

Setprefix(‘@JKI57’);

fileName = _dialogFieldFileName.value();

if(!fileName)

{

info(strfmt(‘@SYS18624’));

return;

}

 

tb = new TextBuffer();

tb.fromFile(fileName);

totRows = tb.numLines();

 

commaIO = new CommaIO(fileName, ‘@SYS76352’);

inRecord = commaIO.read();

progress.setCaption(“@SYS4128”);

#AviFiles

progress.setAnimation(#AviUpdate);

inRecord = commaIO.read();

 

progress.setTotal(totRows);

//progress.setText(strfmt(“Updating Item”, searchItem));

 

ttsbegin;

while (!commaIO.status())

{

searchItem = (conPeek(inRecord, 1));

SetPrefix (strfmt(‘@JKI32’, (progressCounter+1), searchItem));

 

select firstonly forupdate inventTable where inventTable.ItemId == searchItem;

if (inventTable)

{

 

if (InventTable.DimGroupId != “@JKI59” && InventTable.DimGroupId != “@JKI60”)

{

inRecord = commaIO.read();

warning(strfmt(“@JKI61”, InventTable.DimGroupId));

continue;

}

 

inventTable.PdsShelfLife  = str2num(conPeek(inRecord, 2));

inventTable.PdsShelfAdvice = str2num(conPeek(inRecord, 3));

inventTable.PdsBestBefore = str2num(conPeek(inRecord, 4));

 

if (inventTable.validateWrite())

{

counter++;

//inventTable.update();

info(strfmt(“@JKI62”, SearchItem));

}

else

{

ErrCounter++;

error(strfmt(“@JKI63”, progressCounter));

}

}

else

{

warning(strfmt(“@JKI64”, SearchItem));

}

inRecord = commaIO.read();

progressCounter++;

progress.setText(strfmt(“Updating Item %1%”, (progressCounter/totRows)*100));

progress.incCount();

}

 

if (!ErrCounter)

{

ttsCommit;

info(strfmt(‘@JKI66’, counter));

}

else

{

ttsabort;

error(‘@JKI47’);

info(strfmt(‘@JKI67’, ErrCounter));

}

info(strfmt(‘@JKI65’, progressCounter));

}

}