Ax Technical Blog: May 2017

Import Fixed Assets From Excel(with duplicate location, bookid, assetgroup) To AX

static void importAsset2(Args _args)
{
Dialog dialog;
Dialogfield dialogfield;

SysExcelApplication application;
SysExcelWorkbooks workBooks;
SysExcelWorkbook workBook;
SysExcelWorksheets workSheets;
SysExcelWorksheet workSheet;
SysExcelCells cells;

AssetLocation assetLocation;
AssetGroup assetGroup;
AssetTable assetTable;
AssetBook assetBook;
AssetBookTable assetBookTable;

AssetLocationId location;
AssetGroupId assetGrp;
AssetBookId assetBkId;
AssetId assetId;
Name name;
AssetAcquisitionDate assetAcqDate;
AssetAcquisitionPrice assetAcqPrice;
AssetDepreciate assetDepreciate;
AssetServiceLife assetServiceLife;
AssetPostingProfile assetPostingProfile;
AssetStatus assetStatus;

Filename fileName;
COMVariantType type;
int row = 1 ;
int recordcnt;
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 "";
}

application = SysExcelApplication::construct();
workBooks = application.workbooks();
dialog = new Dialog("FileOpen");
dialogfield = dialog.addField(extendedTypeStr(Filenameopen), "File Name");
dialog.run();

if (dialog.run())
{
filename = (dialogfield.value());
}

//fileName = @"C:\Users\Sandeep.Madupu\Desktop\Mine.xlsx";
try
{
workBooks.open(fileName);
}
catch (Exception::Error)
{
throw error("File Cannot be opened");
}

workBook = workBooks.item(1);
workSheets = workBook.worksheets();
workSheet = workSheets.itemFromNum(1);
cells = workSheet.cells();

//try
//{
do
{

row++;
location = cells.item(row, 11).value().bStr();
assetGrp = cells.item(row, 1).value().bStr();
assetBkId = cells.item(row, 3).value().bStr();
assetId = cells.item(row, 2).value().bStr();
name = cells.item(row, 4).value().bStr();
assetAcqDate = cells.item(row, 6).value().date();
assetDepreciate = str2enum(assetDepreciate,cells.item(row, 10).value().bStr());
assetStatus = str2enum(assetStatus,cells.item(row, 9).value().bstr());
assetPostingProfile = cells.item(row, 7).value().bstr();
assetServiceLife = cells.item(row, 8).value().double();
assetAcqPrice = cells.item(row, 5).value().double();

ttsBegin;

select assetLocation where assetLocation.Location == location;

select assetGroup where assetGroup.GroupId == assetGrp;

select assetBookTable where assetBookTable.BookId == assetBkId;

select assetTable where assetTable.AssetId == assetId;

select assetBook where assetBook.BookId == assetBkId;

if(assetLocation.Location)
{
select forupdate assetlocation where assetLocation.Location == location;
ttsbegin;
assetlocation.location = location;
assetlocation.name = location;
assetlocation.Update();
ttscommit;

}

else
{
assetlocation.clear();
assetLocation.initValue();
assetLocation.Location = location;
assetLocation.Name = location;
assetLocation.insert();
}
//assetTable.Location = assetLocation.Location;

if(assetGroup.GroupId)
{
select forUpdate assetGroup where assetGroup.GroupId == assetGrp;
ttsBegin;
assetGroup.GroupId = assetGrp;
assetGroup.Name = assetGrp;
assetGroup.Location = assetLocation.Location;
assetGroup.Update();
ttsCommit;

}

else
{
assetGroup.clear();
assetGroup.initValue();
assetGroup.GroupId = assetGrp;
assetGroup.Name = assetGrp;
assetGroup.Location = assetLocation.Location;
assetGroup.insert();

}

if(assetTable.AssetId)
{

select forUpdate assetTable where assetTable.AssetId == assetId;

ttsBegin;
assetTable.initValue();
assetTable.AssetId = assetId;
assetTable.Name = name;
assetTable.AssetGroup = assetGroup.GroupId;
assetTable.Location = location;
assetTable.Update();
ttsCommit;
}

else
{
assetTable.clear();
assetTable.initValue();
assetTable.AssetId = assetId;
assetTable.Name = name;
assetTable.AssetGroup = assetGroup.GroupId;
assetTable.Location = location;
assetTable.insert();
}

if(assetBookTable.BookId)
{
select forUpdate assetBookTable where assetBookTable.BookId == assetBkId;
ttsBegin;
assetBookTable.initValue();
assetBookTable.BookId = assetBkId;
assetBookTable.Description = assetBkId;
assetBookTable.Update();
ttsCommit;

}
else
{
assetBookTable.clear();
assetBookTable.initValue();
assetBookTable.BookId = assetBkId;
assetBookTable.Description = assetBkId;
assetBookTable.insert();
}

select forupdate assetBook join assetTable where assetBook.AssetId == assetTable.AssetId
&& assetTable.AssetId == assetId
&& assetBook.BookId == assetBkId;

if(assetBook.BookId)
{

ttsBegin;
assetBook.BookId = assetBookTable.BookId;
assetBook.AcquisitionDate = assetAcqDate;
assetBook.AcquisitionPrice = assetAcqPrice;
assetBook.ServiceLife = assetServiceLife;
assetBook.PostingProfile = assetPostingProfile;
assetBook.Status = assetStatus;
assetBook.Depreciation = assetDepreciate;
assetBook.AssetId = assetTable.AssetId;
//assetBook.selectForUpdate(true);
assetBook.Update();
ttsCommit;

}

else
{
assetBook.clear();
select assetBookTable where assetBookTable.BookId == assetBkId;

select assetTable where assetTable.AssetId == assetId;

assetBook.initValue();
assetBook.BookId = assetBookTable.BookId;
assetBook.AcquisitionDate = assetAcqDate;
assetBook.AcquisitionPrice = assetAcqPrice;
assetBook.ServiceLife = assetServiceLife;
assetBook.PostingProfile = assetPostingProfile;
assetBook.Status = assetStatus;
assetBook.Depreciation = assetDepreciate;
assetBook.AssetId = assetTable.AssetId;
assetBook.insert();
}

ttsCommit;

type = cells.item(row+1, 1).value().variantType();

}
while (type != COMVariantType::VT_EMPTY);

application.quit();
workbooks.close();
info("Done");
}

importing general journal using DIXF
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
https://blogs.msdn.microsoft.com/axsupport/2014/07/17/importing-a-general-journal-using-data-importexport-framework-ax-2012/

TFS
--------------------------------------
https://blogs.msdn.microsoft.com/axinthefield/why-you-should-be-using-team-foundation-server-for-dynamics-ax-lifecycle-management/

Rfresh,re-read,re search
-------------------------------------------------------------------------------------------------------------------------
https://community.dynamics.com/ax/b/mafsarkhan/archive/2010/05/26/refresh-reread-research-executequery-which-one-to-use

diff between VIEW and table
-------------------------------------------------------------------------------------------------------
http://www.geekinterview.com/question_details/29308

dmf in R2 and R3
-----------------------------------------------------------------------------------------
https://us.hitachi-solutions.com/blog/data-migration-microsoft-dynamics-ax-2012-r2-r3/

dmf/dixf
--------------------------------------------------------------
http://ax2012dixf.blogspot.com/

X++ code to Read/Write Dynamics Ax data to excel
--------------------------------------------------------------------------------------------
http://learnax.blogspot.com/2010/01/x-code-to-readwrite-dynamics-ax-data-to.html

https://dynamicsaxinsight.wordpress.com/2015/03/25/ax-2012-off-the-shelf-excel-reader/

dynamicsw 365
================================================
http://axguide.blogspot.in/

Dialog Runbase Example in AX 2012
------------------------------------------------------------------------------
http://axhelper.blogspot.com/2014/02/dialog-runbase-example-in-ax-2012.html

interview questios
------------------------------------------------------------------------
http://arsalanax.blogspot.com/2012/01/dynamics-ax-technical-consultant.html

Custom AIF Service in Dynamics Ax 2012 R3 from Scratch.
--------------------------------------------------------------------------------------
http://www.tech.alirazazaidi.com/custom-aif-service-in-dynamics-ax-2012-r3-from-scratch/

How to make Relation in Extended Data Type and add 'View Details' for field on a form in AX 2012
------------------------------------------------------------------------------------------------------------------------------
http://axvuongbao.blogspot.in/2013/09/how-to-make-relation-in-extended-data_19.html

Run, Save and Email Purchase order report through X++ code
=======================================================================
http://theaxapta.blogspot.in/2015/01/run-save-and-email-purchase-order.html

passing parameters between forms in AX
-------------------------------------------------------------------------
https://calebmsdax.wordpress.com/2013/02/22/passing-parameters-between-forms-in-ax/

Add Controls to the Filter Pane
-------------------------------------------
https://msdn.microsoft.com/en-us/library/cc577231.aspx

workflow
---------------------------
http://dynamicsaxforum.blogspot.in/2014_07_01_archive.html

Custom Workflow from scratch, Dynamics Ax 2012
------------------------------------------------------
http://www.tech.alirazazaidi.com/custom-workflow-from-scratch-dynamics-ax-2012/

SSRS Reports
------------------------------------------------------------------------------------------------------
https://stoneridgesoftware.com/report-parameters-in-dynamics-ax-2012-ssrs-reports/

https://axssrsguide.wordpress.com/

https://ax2012anant.blogspot.com/2015/02/developing-ssrs-report-using-report.html

https://community.dynamics.com/ax/b/dynamics101trainingcenterax/archive/2013/09/16/developing-a-ssrs-report-using-the-report-data-provider-in-microsoft-dynamics-ax-2012

http://dynamics-resources.com/ssrs-tutorial-2/

https://community.dynamics.com/ax/b/faisalfareedaxlibrary/archive/2014/12/27/rdp-contract-ui-builder-and-controller-classes-for-ssrs-report-development

Ax Technical Blog

Monday, 29 May 2017

import fixed assets through X++

Important Links