The Data import export Framework has the possibility to import or export data from external sources, but you can also copy data from one company to another within AX itself.
Below you'll find the how to (like importing data, again this is really easy), but first some downsides in my opinion:
- You can't see the selected companies after having run the wizard. I'd like to now this information to double check. You'd want to see the in the [Copy processing group] form.
- Business validations cannot be run when copying the data. I'd want the possibility to check if mandatory metadata is present in my new company and if not, warn me about it, like when importing data. Now you should be careful to validate your data in the staging table and correct errors if there are any. If you forget to do this, you'll enter invalid data in AX and loose your data integrity.
How to copy the data
To use it, you must select option: [Copy entity data between companies]. It lets you select an entity (in this case Asset):
Next up is the selection of the Source and the Target company, simply select one Source company and one or more Target companies:
Click next and finish the wizard to set up the copy template. Now all that needs to be done is to Get the Staging data (hit run to fetch the records).
And finally run the job [Copy data to target] to copy the data in the actual AX table.
The assets are now present in the selected company.
Here's how to create a production order and update it completely up until ending the order through code for AX2009:
static void JLH_ProductionOrderViaCode(Args _args)
{
NumberSeq numberSeq;
ProdTable prodTable;
InventDim inventDim;
ProdMultiCostEstimation prodMultiCostEstimation;
ProdMultiScheduling prodMultiScheduling;
ProdMultiRelease prodMultiRelease;
ProdMultiStartUp prodMultiStartUp;
ProdMultiReportFinished prodMultiReportFinished;
ProdMultiHistoricalCost prodMultiHistoricalCost;
Args args = new Args();
;
ttsbegin;
NumberSeq = NumberSeq::newGetNumFromCode(ProdParameters::numRefProdId().numberSequence);
ProdTable.ProdId = NumberSeq.num();
ProdTable.ItemId = '5001'; // Item
prodTable.initFromInventTable(InventTable::find(prodTable.ItemId));
prodTable.initBOMVersion();
prodTable.initRouteVersion();
inventDim.InventSiteId = 'S1'; // Site
inventDim.InventLocationId = 'MW'; // Warehouse
inventDim = InventDim::findOrCreate(inventDim);
prodtable.InventDimId = inventDim.inventDimId;
prodtable.QtySched = 2; // Quantity
prodtable.insert();
ttscommit;
// Estimation
prodMultiCostEstimation = ProdMultiCostEstimation::construct(args);
runBaseMultiParm::initParm(ProdMultiCostEstimation);
prodMultiCostEstimation.insert(prodTable, ProdMultiCostEstimation.defaultParmBuffer());
prodMultiCostEstimation.run();
// Scheduling
prodMultiScheduling = ProdMultiScheduling::construct(ProdSchedMethod::OperationScheduling);
runBaseMultiParm::initParm(prodMultiScheduling);
prodMultiScheduling.insert(prodTable, prodMultiScheduling.defaultParmBuffer());
prodMultiScheduling.run();
// Release
prodMultiRelease = ProdMultiRelease::construct(args);
runBaseMultiParm::initParm(ProdMultiRelease);
prodMultiRelease.insert(prodTable, prodMultiRelease.defaultParmBuffer());
prodMultiRelease.run();
// Start up
prodMultiStartup = ProdMultiStartUp::construct(args);
runBaseMultiParm::initParm(prodMultiStartup);
prodMultiStartup.insert(prodTable, prodMultiStartup.defaultParmBuffer());
prodMultiStartup.run();
// Report as Finished
prodMultiReportFinished = ProdMultiReportFinished::construct(args);
runBaseMultiParm::initParm(ProdMultiReportFinished);
prodMultiReportFinished.insert(prodTable, ProdMultiReportFinished.defaultParmBuffer());
prodMultiReportFinished.run();
// End the Order
prodMultiHistoricalCost = ProdMultiHistoricalCost::construct(args);
runBaseMultiParm::initParm(ProdMultiHistoricalCost);
prodMultiHistoricalCost.insert(prodTable, ProdMultiHistoricalCost.defaultParmBuffer());
prodMultiHistoricalCost.run();
}
AX2012 R3 comes with the Data Import Export Framework which is a separate module in AX. It is also part of R2 CU7 and available as a download for 2012 Featurepack (via Information Source).
I did install it before in the Featurepack version, but it had some minor bugs which held me from futher investigation this possibly cool tool. But after re-discovering this tool, my first impression for the out of the box options are:
Pros: It looks nice, it's comprehensive and let's you preview data which you'd want to import.
Cons: It stills seems a bit shaky, the slightest mistake can result an error which doesn't explain the reason of the failure. Also the drag and drop isn't very smooth (yet?), but it gets the job done.
So how does it work?
In this blog I'll briefly show how you can import data via CSV, but you can also upload via ODBC or via other AX instances. I'll import 4 new main accounts in this example.
First you need to create a Source Data format. In this case I've created CSV of the type File and have set the column delimiter to Semicolon.
Next, create a new Processing Group (MA with description Main Account in my case) and click Entities, here you can select the type of import (I choose Main Account, but it could also be customers, sales orders, vendors etc.). Select source data format to be CSV and then click Generate Source file to create the input template. Select the fields you want and generate a sample file.
In the Sample File Path, select your enriched file which you'll import. Then the other options in the top menu become available.
Select Generate Source Mapping and based on the header line in your import file, the links are created. You can view (and modify) the link from the import file to the table via the button "Modify Source mapping" as can be seen below. When you modify a link (drag and drop), remember to hit the Save button.
You can preview the file which is to be imported by clicking Preview Source File, it will show the upload to the staging table with the values and fields.
Close the entities form and return to the Processing Group, hit "Get staging data" to upload the data and click Run. In my case, it results in an infolog which says that 4 records have been inserted in the staging table. Via "Execution History" you can preview the data in the staging table which can now be imported to the table(s) in AX. A last check if all data is correct and then click "Copy data to target" and click Run to import.
This resulted in 4 new records and my main accounts are created:
It's really that simple, so go for it and check it out!
Today I installed AX2012 R3 on my laptop and of course I want the demo data to be imported as well (download via Partnersource). You need to use the Test Data Transfer tool which can be downloaded via InformationSource.
The next step is to extract the demo data and to install the test data transfer tool. After that has been done, open the command prompt in admin mode. Navigate to the folder where the test tool is installed and enter the following command:
dp [direction] [directory] [database] [server]
direction = Import or Export (import in this case)
directory = the directory which holds the extracted demo data
database = the name of the AX2012 R3 database
server = name of the server (can be left blank when importing on the local machine)
Hit Y to confirm that this is a test environment and you want to import the data. The import will start to process the files.
When the import is complete, you'll have the new companies! The whole import took approximately one hour, great work.
A cool thing in AX is to keep track of vital changes using the DatabaseLog. A problem however is that your databaselog can grow exponentially so there are two things you need when using it:
1. Choose carefully which data you want to keep track of. Mostly it's enough to track some updates on some fields and some delete actions. You should periodically review if it is still necessary to log these changes.
2. You need to clean up your log for it can grow enormously within time. Disadvantage of the clean up functionality in AX is that it simply deletes your log.
Therefor I created a job which exports your log (with the old and the new value) to a csv file which you can save and afterwards delete the log in AX. The job is just to show what could be possible to save your databaselog. You could improve it with date ranges, delete records after saving them, create a class which can run in batch, only a number of records to keep the file size normal and then on the next file, etcetera.
static void JLH_DatabaseLogToCSV(Args _args)
{
#AviFiles
SysOperationProgress progress = new SysOperationProgress();
int i;
AsciiIo asciiIo;
Filename filename;
Str line;
Container con;
SysDatabaseLog databaseLog;
str dataBaseLogOld;
str dataBaseLogNew;
str dataBaseLogDate;
str dataBaseLogUser;
str tableName;
str fieldName;
container databaseLogContainer;
int containerLength;
int counter;
int countRecords;
int tableId;
int fieldId;
;
progress.setCaption("Export To CSV in progress...");
progress.setAnimation(#AviTransfer);
filename = "C:\\FileShare\\DAX2009\\ImportExport\\ExportToCSV.txt";
asciiIo = new AsciiIo(filename,'W');
asciiIo.outRecordDelimiter('\r\n');
asciiIo.outFieldDelimiter(";");
asciiIo.write('Table;Field;NewValue;OldValue;Date;User');
while select dataBaseLog
{
progress.setText(strfmt("DataBaseLog Recid %1", databaselog.RecId));
containerLength = conLen(dataBaseLog.Data);
for (counter = 1; counter <= containerLength; counter++)
{
dataBaseLogContainer = conPeek(databaseLog.Data, counter);
dataBaseLogOld = conpeek(databaselogContainer,3);
dataBaseLogOld = strKeep(dataBaseLogOld,' 1234567890,.abcdefghijklmnopqrstuvwxyz');
dataBaseLogNew = conpeek(databaselogContainer,2);
dataBaseLogNew = strKeep(dataBaseLogNew,' 1234567890,.abcdefghijklmnopqrstuvwxyz');
dataBaseLogDate = dateTime2str(databaseLog.createdDateTime);
dataBaseLogUser = databaseLog.createdBy;
tableName = tableid2name(databaseLog.table);
tableID = databaseLog.table;
fieldId = conpeek(databaseLogContainer,1);
fieldName = fieldid2name(tableID,fieldId);
if (fieldName != 'RecVersion')
{
asciiIo.write(strfmt('%1;%2;%3;%4;%5;%6',
tableName,
fieldName,
dataBaseLogNew,
dataBaseLogOld,
dataBaseLogDate,
dataBaseLogUser));
}
}
}
asciiIo = null;
}
There are multiple reports in Dynamics AX which you run on a daily or weekly basis and where you need to change the date range every time you run the report. For instance, you want to see your turnover of the past week on every monday. That means that every monday, you need to change your date range.
There a clever query utility which let's you create dynamic ranges.
The different options are (today's date is 25-02-2014):
- (dayRange(-30,0)) - Results in a date range for the last 30 days: "26-01-2014".."25-02-2014"
- (day(-1)) - Results in yesterday's date: 24-02-2014
- (day(0)) - Results in today's date: 25-02-2014
- (day(1)) - Results in tomorrow's date: 26-02-2014
- (greaterThanDate(2)) - Results in every date after today plus 2: > 27-02-2014
- (lessThanDate(-1)) - Results in every date of today minus 1: < 24-02-2014
- (monthRange(0,2)) - Results in first day till the last day of the month's choosen (0 = current month): "01-02-2014".."30-04-2014"
- (yearRange(-1,-1)) - Results in first day till the last day of the chosen year: "01-01-2013".."31-12-2013"
Now, how to apply this in a report. In a date field, simply key in the rangetype you need. Below you see the range (dayRange(-30,0)). The result will be that every day you run this report, it will display the transactions of today minus 30 days up until today. This way, you can set reports like this to run in Batch and to sent them periodically to a fileshare or e-mail them without having the need to always having to change the date range.
To see the results in a job or to play around with the different date options, you can copy and use the job below:
static void JLH_sysQueryRangeUtil(Args _args)
{
str dateRange;
;
// In the following date examples, date of today (25-02-2014) is taken
dateRange = sysQueryRangeUtil::dayRange(-30,0); // Results in a date range for the last 30 days: "26-01-2014".."25-02-2014"
info(strfmt(dateRange));
dateRange = sysQueryRangeUtil::day(-1); // Results in yesterday's date: 24-02-2014
info(strfmt(dateRange));
dateRange = sysQueryRangeUtil::day(1); // Results in tomorrow's date: 26-02-2014
info(strfmt(dateRange));
dateRange = sysQueryRangeUtil::greaterThanDate(2); // Results in every date after today plus 2: > 27-02-2014
info(strfmt(dateRange));
dateRange = sysQueryRangeUtil::lessThanDate(-1); // Results in every date of today minus 1: < 24-02-2014
info(strfmt(dateRange));
dateRange = sysQueryRangeUtil::monthRange(0,2); // Results in first day till the last day of the month's chosen (0 = current month): "01-02-2014".."30-04-2014"
info(strfmt(dateRange));
dateRange = sysQueryRangeUtil::yearRange(-1,-1); // Results in first day till the last day of the chosen year: "01-01-2013".."31-12-2013"
info(strfmt(dateRange));
}
In AX, the ICP and OB declarations could be sent electronically to the tax authority using the PIN method. However, as of 01-02-2014, it is no longer possible to submit your declaration via BAPI with PIN and as of 01-06-2014 it's also no longer possible to sent it via BAPI PKI (but this was never possible in AX). It is to be sent via Digipoort using a "PKIoverheid" service certificate and besides that the declaration should an XBRL format instead of the old XML.
The new certificate can be optained via:
• Digidentity (https://www.digidentity.eu/)
• ESG (http://www.de-electronische-signatuur.nl/)
• KPN (http://certificaat.kpn.com/)
• QuoVadis (http://www.quovadisglobal.nl/)
The new XBRL layout is mandatory (SBR - Standard Business Reporting) and the taxonomy can be found here: http://www.nltaxonomie.nl/ (version 8.0).
For Dynamics AX 2009, AX2012 and AX2012 R2, a hotfix has been created by Microsoft, you can download it here via Partnersource.
Currently, for AX2009 it is only possible to create the XBRL message, you cannot sent it yet, but Microsoft is working on it and has said to release a.s.a.p..
At this moment I've created the new messages in both AX2009 and AX2012, but have not yet send them. As soon as the AX2009 fix is there and all connection strings are complete, I'll try to post an update with the sending process as well.
Creating the declaration
After installing the update, nothing has changed. You create the ICP / OB declaration as usual in AX and then create the message via [General Ledger - Periodic - Sales tax payment - Netherlands - Electronic ICP declaration]. First create the declaration for a given period and then click [Send XML]. Your declaration cannot be sent yet, but the message is created and you can view it in the Outgoing BAPI folder. Open the file and you'll see the new layout according to the required XBRL format. This works the same for both AX2009 and AX2012.
Below you see the new output, looks good :-)!
