class : InventUpdate\writeInventTransAutoDim
Month: December 2014
Modify Query Ranges on Report Controller Class
Create a enum and set it on menu item properties as example below.
override method preRunModifyContract on report controller class as example code below.
protected void preRunModifyContract() { //modify the parameter value of the contract // if( element.args().parmEnumType() == EnumNum( NoYes ) ) if(this.parmArgs().parmEnumType() == enumNum(TIDSalesDailyRecon)) { switch(this.parmArgs().parmEnum()) { case TIDSalesDailyRecon::SalesInvoice : SrsReportHelper::addParameterValueRangeToQuery(this.getFirstQuery(),tableNum(RetailTransactionPaymentTrans),fieldNum(RetailTransactionPaymentTrans, RecId),SysQuery::value(5637149251)); break; case TIDSalesDailyRecon::SalesReturn : SrsReportHelper::addParameterValueRangeToQuery(this.getFirstQuery(),tableNum(RetailTransactionPaymentTrans),fieldNum(RetailTransactionPaymentTrans, RecId),SysQuery::value(this.parmArgs().record().RecId)); break; case TIDSalesDailyRecon::SalesExIndent : break; } } }
this.parmArgs().record().RecId value is come from :
SrsReportRunController controller; Args = new Args() controller = new SrsReportRunController(); args.record(TABLES_record); controller.parmReportName(ssrsReportStr(REPORT_NAME, REPORT_DESIGN_NAME)); controller.startOperation();
source :
1. http://www.dynamics101.com/2014/01/using-controller-class-developing-ssrs-reports-microsoft-dynamics-ax-2012/
2. http://dynamics.folio3.com/x-tips-and-tricks-passing-parameters-microsoft-dynamics-ax/
Copy Lines Code Example
void clicked() { PriceDiscAdmTrans copyLines; super(); copyLines.data(PriceDiscAdmTrans); copyLines.insert(); PriceDiscAdmTrans_ds.research(); }
add mainAccount to LedgerJournalReport
add this code below to LedgerJournalDP\InsertLedgerJournalTmp
if(_accountOffsetAccount == AccountOffsetaccount::Account) { switch(_ledgerJournalTrans.AccountType) { case LedgerJournalACType::Ledger : ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(_ledgerJournalTrans.LedgerDimension).MainAccount).MainAccountId; break; case LedgerJournalACType::Bank : select * from dimAttrValueCombo join Name from bankAccountTable where bankAccountTable.AccountID == dimAttrValueCombo.DisplayValue && dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension; ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(bankAccountTable.LedgerDimension).MainAccount).MainAccountId; ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue, bankAccountTable.Name); break; case LedgerJournalACType::Vend : select firstOnly dimAttrValueCombo where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension; VendLedgerAccounts = VendLedgerAccounts::find(_ledgerJournalTrans.PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup); ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(VendLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId; ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,VendTable::find(dimAttrValueCombo.DisplayValue).name()); break; case LedgerJournalACType::Cust : select firstOnly dimAttrValueCombo where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension; CustLedgerAccounts = CustLedgerAccounts::find(_ledgerJournalTrans.PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup); ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(CustLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId; ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,CustTable::find(dimAttrValueCombo.DisplayValue).name()); break; case LedgerJournalACType::FixedAssets : select firstOnly dimAttrValueCombo where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension; selecT firstOnly ledgerJournalTrans_asset where ledgerJournalTrans_asset.RefRecId == _ledgerJournalTrans.RecId; assetLedgerAccounts = AssetLedgerAccounts::find(ledgerJournalTrans_asset.BookId,_ledgerJournalTrans.PostingProfile,enum2int(ledgerJournalTrans_asset.TransType),TableGroupAll::GroupId,AssetTable::find(dimAttrValueCombo.DisplayValue).AssetGroup); ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(assetLedgerAccounts.LedgerDimension).MainAccount).MainAccountId; break; } } else { switch(_ledgerJournalTrans.AccountType) { case LedgerJournalACType::Ledger : ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(_ledgerJournalTrans.LedgerDimension).MainAccount).MainAccountId; break; case LedgerJournalACType::Bank : select * from dimAttrValueCombo join Name from bankAccountTable where bankAccountTable.AccountID == dimAttrValueCombo.DisplayValue && dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension; ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(bankAccountTable.LedgerDimension).MainAccount).MainAccountId; ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue, bankAccountTable.Name); break; case LedgerJournalACType::Vend : select firstOnly dimAttrValueCombo where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension; VendLedgerAccounts = VendLedgerAccounts::find(VendParameters::find().PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup); ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(VendLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId; ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,VendTable::find(dimAttrValueCombo.DisplayValue).name()); break; case LedgerJournalACType::Cust : select firstOnly dimAttrValueCombo where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension; CustLedgerAccounts = CustLedgerAccounts::find(CustParameters::find().PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup); ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(CustLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId; ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,CustTable::find(dimAttrValueCombo.DisplayValue).name()); break; case LedgerJournalACType::FixedAssets : select firstOnly dimAttrValueCombo where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension; selecT firstOnly ledgerJournalTrans_asset where ledgerJournalTrans_asset.RefRecId == _ledgerJournalTrans.RecId; assetLedgerAccounts = AssetLedgerAccounts::find(ledgerJournalTrans_asset.BookId,AssetParameters::find().PostingProfile,enum2int(ledgerJournalTrans_asset.TransType),TableGroupAll::GroupId,AssetTable::find(dimAttrValueCombo.DisplayValue).AssetGroup); ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(assetLedgerAccounts.LedgerDimension).MainAccount).MainAccountId; break; } }
Delete All TransData for AX 2012
This class purpose is to delete all trans data from database. This class is very useful for pre-live phase.
just run class : SysDatabaseTransDelete.
Filter Query Form DataSource using DynaLink
example code on form VendPurchaseOrderJournal , datasource VendPurchOrderJournal method init()
public void init() { QueryBuildDataSource queryDataSourceLink; super(); if (element.args().dataset() == tablenum(PurchReqLine)) { this.query().dataSourceTable(tablenum(VendPurchOrderJour)).clearDynalinks(); queryDataSourceLink = this.query().dataSourceName(identifierstr(VendPurchOrderJour)); queryDataSourceLink.relations(true); queryDataSourceLink.addDynalink(fieldnum(VendPurchOrderJour, PurchId), element.args().record(), fieldnum(PurchReqLine, PurchId)); } //add by fanddy , for inqueries confirm PO if (element.args().dataset() == tablenum(TECPOGroupHeader)) { this.query().dataSourceTable(tablenum(VendPurchOrderJour)).clearDynalinks(); queryDataSourceLink = this.query().dataSourceName(identifierstr(VendPurchOrderJour)); queryDataSourceLink.relations(true); queryDataSourceLink.addDynalink(fieldnum(VendPurchOrderJour, TIDPOGroupId), element.args().record(), fieldnum(TECPOGroupHeader, POGroupId)); } //END }
Found a BudgetPlanningProcessTmp record instead of an expected BudgetPlanningProcess record.
Version : AX 2012 R2 CU7
 
Found this error this morning when select a record in budgetplanning lookup.
Error detail :
“Found a BudgetPlanningProcessTmp record instead of an expected BudgetPlanningProcess record. Check to see if an override of the resolveReference method is returning a record from the correct table.”
Solution :
Revert static method BudgetPlanGenerateBudgetTrnsForm::lookupBudgetPlanningProcess back to SYS layer using compare.
lookup inventLocation by inventSite on method lookup field datasource form example
public void lookup(FormControl _formControl, str _filterStr) { //coded by fanddy if (TECPOGroupStore.TIDTransferToInventSiteId) { InventLocation::lookupBySiteIdAllTypes(_formControl, TECPOGroupStore.TIDTransferToInventSiteId); } else { super(_formControl, _filterStr); } }
Query value range with Enum example
TECPOGroupHeader_ds.query().dataSourceTable(tableNum(TECPOGroupHeader)).addRange(fieldnum(TECPOGroupHeader,Status)).value(sysquery::value(TECPOGroupStatus::Confirmed));
add filter to form datasource method:ExecuteQuery example code
public void executeQuery() { ; TECPOGroupHeader_ds.query().dataSourceTable(tableNum(TECPOGroupHeader)).clearRanges(); if(statusFilter.valueStr() == enum2str(TEC_AllDraftPosted::Draft)) TECPOGroupHeader_ds.query().dataSourceTable(tableNum(TECPOGroupHeader)).addRange(fieldnum(TECPOGroupHeader,Status)).value(sysquery::value(TECPOGroupStatus::Draft)); if(statusFilter.valueStr() == enum2str(TEC_AllDraftPosted::Posted)) TECPOGroupHeader_ds.query().dataSourceTable(tableNum(TECPOGroupHeader)).addRange(fieldnum(TECPOGroupHeader,Status)).value(sysquery::value(TECPOGroupStatus::Posted)); //add by fanddy if(statusFilter.valueStr() == enum2str(TEC_AllDraftPosted::Confirmed)) TECPOGroupHeader_ds.query().dataSourceTable(tableNum(TECPOGroupHeader)).addRange(fieldnum(TECPOGroupHeader,Status)).value(sysquery::value(TECPOGroupStatus::Confirmed)); //END add by fanddy if(CreatorFilter.value() == true) TECPOGroupHeader_ds.query().dataSourceTable(tableNum(TECPOGroupHeader)).addRange(fieldnum(TECPOGroupHeader,CreatedBy)).value(sysquery::value(curUserId())); super(); }
get Dimension Value from DefaultDimension
public static str 255 getDimensionValueTID(RecId defaultDimension, Name dimName) { DimensionAttributeValueSetStorage dimStorage; ; dimStorage = DimensionAttributeValueSetStorage::find(defaultDimension); return dimStorage.getDisplayValueByDimensionAttribute(DimensionAttribute::findByName(dimName).RecId); }
how to use :
getDimensionValueTID(_ledgerJournalTrans.DefaultDimension, 'BusinessUnit')
can’t set axLedgerJournalTrans.parmOffsetLedgerDimension on AX2012 R3
found a better solution from post comment :
AxLedgerJournalTrans.clearField(fieldNum(ledgerJournalTrans, offsetLedgerDimension), false);
Global Variable in X++ AX 2012
some circumtances i must use global variable.
read :
http://msdn.microsoft.com/en-us/library/aa891830.aspx
more example how to use :
SysGlobalCache globalCache; globalCache = ClassFactory.globalCache(); globalCache.set(strFmt("%1%2",curUserId(),"WhatEver"), 0, "ANY VALUE");
on other class.
SysGlobalCache globalCache; globalCache = ClassFactory.globalCache(); info(strFmt("%1",globalCache.get(strFmt("%1%2",curUserId(),"WhatEver"), 0)));
Auto Convert field from Excel to X++
public static str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0) { switch (_cv.variantType()) { case (COMVariantType::VT_BSTR): return _cv.bStr(); case (COMVariantType::VT_R4): return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2); case (COMVariantType::VT_R8): return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2); case (COMVariantType::VT_DECIMAL): return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2); case (COMVariantType::VT_DATE): return date2str(_cv.date(),123,2,1,2,1,4); case (COMVariantType::VT_EMPTY): return ''; default: throw error(strfmt('@SYS26908', _cv.variantType())); } return ''; }
how to use example :
YourClass::COMVariant2Str(cells.item(row,17).value());
Create Journal Upload with Batch Form
just duplicate class tutorial_runBaseBatch, then modify the following class :
on class declaration :
public class JournalUploadExcextends RunBaseBatch { DialogField dfFileName; FileName fileName ; #define.CurrentVersion(1) #define.Version1(1) #localmacro.CurrentList fileName #endmacro }
on dialog() :
public Object dialog() { #File DialogRunbase dialog = super(); #resAppl ; dfFileName = dialog.addFieldValue(extendedTypeStr(FileNameOpen),fileName); dialog.filenameLookupFilter(["All files", #AllFiles]); return dialog; }
on method getFromDialog() :
public boolean getFromDialog() { //do what ever you want in this class. i call upload journal method with PathFile as parameter JournalUploadExc::JournalUpload(dfFileName.value()); return super(); }
voila.. !! done.. !!
Job Upload General Journal from excel
public static boolean JournalUpload(str _filePath) { container accEntryPattern; container offSetEntryPattern; LedgerJournalACType LedgerJournalACType; ledgerJournalName ledgerJournalName; journalID JournalID; axLedgerJournalTable axLedgerJournalTable; axledgerJournalTrans axLedgerJournalTrans; LedgerJournalType JournalType = ledgerJournalType::Daily; SysExcelApplication application = SysExcelApplication::construct(); SysExcelWorkbooks workbooks = application.workbooks(); SysExcelWorkbook workbook; SysExcelWorksheets workSheets; SysExcelWorksheet workSheet; SysExcelCells cells; SysExcelCell cell; int row; str filename, numSeq; boolean _return = true; ; startLengthyOperation(); //filename = "C:\\GJCashBank30April2013.xlsx"; filename = _filePath; try { if (workbooks.open(filename, false /*Update links*/, true /*Read only*/)) { workbook = workbooks.item(1); workSheets = workbook.worksheets(); workSheet = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1 cells = workSheet.cells(); row = 5; ttsBegin; axLedgerJournalTable = new axLedgerJournalTable(); axLedgerJournalTable.parmJournalName("GenJrn"); axLedgerJournalTable.parmJournalType(JournalType); axLedgerJournalTable.parmName("General Journal"); axLedgerJournalTable.save(); JournalID = axLedgerJournalTable.parmJournalNum(); while (cells.item(row,2).value().bStr() != "") { //Create Trans ... axLedgerJournalTrans = new axLedgerJournalTrans(); axLedgerJournalTrans.parmJournalNum(JournalID); //start from A axLedgerJournalTrans.parmTransDate(cells.item(row,1).value().date()); axLedgerJournalTrans.parmAccountType( str2Enum(LedgerJournalACType, cells.item(row,2).value().bStr())); if(cells.item(row,2).value().bStr() == "Ledger") { accEntryPattern = [ cells.item(row, 3).value().bStr() + "-" + "Disp", cells.item(row, 3).value().bStr(), 4, "BusinessUnit", cells.item(row,17).value().bStr(), "Department", cells.item(row,18).value().bStr(), "ProductCode", cells.item(row,19).value().bStr(), "ProjectCode", cells.item(row,20).value().bStr() ]; axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(accEntryPattern)); } if(cells.item(row,5).value().double() != 0) { axLedgerJournalTrans.parmAmountCurDebit(cells.item(row,5).value().double()); } if(cells.item(row,6).value().double() != 0) { axLedgerJournalTrans.parmAmountCurCredit(cells.item(row,6).value().double()); } axLedgerJournalTrans.parmtxt(cells.item(row,7).value().bStr()); axLedgerJournalTrans.parmOffsetAccountType( str2Enum(LedgerJournalACType,cells.item(row,6).value().bStr())); if(cells.item(row,8).value().bStr() == "Ledger") { offSetEntryPattern = [ cells.item(row, 9).value().bStr() + "-" + "Disp", cells.item(row, 9).value().bStr(), 4, "BusinessUnit", cells.item(row,17).value().bStr(), "Department", cells.item(row,18).value().bStr(), "ProductCode", cells.item(row,19).value().bStr(), "ProjectCode", cells.item(row,20).value().bStr() ]; axLedgerJournalTrans.parmOffsetLedgerDimension(AxdDimensionUtil::getLedgerAccountId(offSetEntryPattern)); } // axLedgerJournalTrans.parmCurrencyCode(cells.item(row,10).value().bStr()); axLedgerJournalTrans.parmExchRate(cells.item(row,11).value().double()); axLedgerJournalTrans.parmInvoice(cells.item(row,12).value().bStr()); axLedgerJournalTrans.parmPaymReference(cells.item(row,13).value().bStr()); axLedgerJournalTrans.parmBankTransType(cells.item(row,14).value().bStr()); axLedgerJournalTrans.parmDocumentNum(cells.item(row,15).value().bStr()); axLedgerJournalTrans.parmDocumentDate(cells.item(row,16).value().date()); // axLedgerJournalTrans.save(); row++; ttsCommit; } info('Imported Items'); } } catch(Exception::Error) { _return = false; } return _return; }
File Path X++ AX 2012
static void main(Args _args) { FileName fileName ; container c; FileIOPermission permission; TextBuffer textBuffer; str readText; str readLine; int etgline; int i = 0; Dialog dialog = new Dialog("Journal Upload Excel"); DialogField dfFileName; str 100 filename2; container con; ; #File dfFileName = dialog.addField(extendedTypeStr(FileNameOpen)); dialog.filenameLookupFilter(["All files", #AllFiles]); if (dialog.run()) { fileName = dfFileName.value(); info(filename); } }