emeadaxsupport: How to copy a budget to a new budget in AX 2012 using Excel Add-in

[Blog bot]

Источник: http://blogs.msdn.com/b/axsupport/ar...el-add-in.aspx
==============

In this blog I will summarize which options user has when he wants to copy old budget into a new budget using the Excel Add-in functionality. I will be focused only on Excel part, so if you need to establish the connection between AX 2012 and Excel, please use following links:



Editing Budgets with Microsoft Excel 2010

This is interactive demo how to edit Budget entries in AX 2012. Really nice and simple, suitable for end users. It also explains the “Matrix field” functionality supported by Excel Add-In. Very useful feature that can be useful for customers.



Importing a Budget via the Excel Add-in

Nice blog entry about setting up the interface between AX2012 and Excel 2010 for Budget entries.



Intro

Resources above explain how to setup the connection between AX 2012 and Excel 2010, and also how to import new budget entries into the system. But when user needs to copy the old budget entries into new budget entries he might face some design limitations. I would like to explain the current design and ways how to go around it.

Data structure of BudgetTransactionService

When connecting Excel 2010 to the AX 2012 user needs to expose the BudgetTransactionService as Document data source in the system. BudgetTransactionService exposes Budget entries data in a format of two tables:

• Budget register entries – the header document for budget entries. The primary key is “Entry number” and “Ledger name”.
• Budget account entries – the lines with register entries, linked to the header by “Entry number” field.

Budget model SOURCE

For each table user has to create a separate data-table in the Excel document. It is recommended to use separate sheets for each table, as it helps to the user for better structure understanding. For this blog I have created a new budget model SOURCE. In the AX client I have created two Budget register entries (000527_1032 and 000528_1032); and for each of them I inserted some Budget account entries. Then I have imported budget entries for model SOURCE into Excel document into following sheets:







Copy SOURCE budget model entries into TARGET budget model entries

I have also defined a new budget model TARGET in the AX client and we will copy entries from SOURCE model into TARGET model. We need to do following:

1. Create a copy of current entries
2. Create a new Entry numbers for both tables. We cannot use the old Entry number, because it is Primary key and system will manage our new entries as entries for old model.
3. Update the Budget model field.

A. Copy & Paste

The first thing each user tries is to simple copy & paste lines in the tables.



You see that system copied all lines as we expected, but this is the end for good news. In the background of the Excel document is a routine monitoring the data-tables and keys for those tables. As you copy more than one line, system commits those lines and automatically saves the Entry number as the key for new lines. It means that for user it is now not possible to change the Entry number. This is not acceptable, because when user publishes data to the AX2012, system will skip those records, because Entry numbers were already used in the system for previous lines. This method works correctly only when you copy one register entry and one account entry, but this is not really efficient.

B. Copy & Paste – without Entry number

The solution for previous method would be to copy Excel lines without Entry number. Because of the missing Entry number, system won’t be able to “commit” those lines and we can easily update them. The user has to update the “Entry number” manually and correctly for both tables. And it is also necessary to update the Budget model info.





Unfortunately when I was testing this method I have found that it is not working correctly in AX 2012 CU3. When you try to publish data you get a message “There are incomplete records in your worksheet”. If you ignore the message and publish you data, system will insert only some of them. Please do not use this method. Our Dynamics AX support team will escalate the issue to our development for analysis.



C. Copy & Paste – new workbook

The mixture of previous method is to copy all required data into a new workbook. Create a new Excel document and copy both tables. Now you can copy complete tables including “Entry number” because there’s no data validation in the new document.







Now it is necessary to update Entry numbers and Budget model and copy data back into original Excel document:







As you can see the tables look exactly the same like in the scenario B “Copy & Paste – without Entry number”. It is true, but this time it is possible to publish data into the AX 2012 without getting an error message. After clicking “Publish data” the system will transfer new lines into TARGET model.



D. Track changes

The last method is very efficient, but also very dangerous. There are Publishing Options in the Excel Add-in. To access the dialog click on “Publish Data” drop down and select “Publish option”. Here it is possible to disable “Track changes” functionality. This is the functionality maintaining the data validation and primary key logic for the AX 2012. If you disable the function then system will treat all lines as new lines. This means that if you set “Track changes” to No, and you click “Publish data” – system will publish all entries from your Excel into AX 2012 as new entries. This can easily cause a duplication all budget entries in the system.



So why this method is efficient ? Because to copy the budget entries into our TARGET model we need to update only the Budget model field. The procedure is following:

1. Set Track changes to NO
2. Change Budget model to TARGET
3. Publish data to the AX 2012
4. Set Track changes to Yes
5. Refresh data in AX 2012

Please always make sure that the “Track changes” option is set Yes, when you finish the copying, otherwise this can cause serious problems.



Conclusion

In the current version (AX 2012 CU3) system supports only 2 methods for copying Budget entries through Excel into AX 2012: the copy & paste using new workbook and method using “Track changes” option. The “copy & paste using new workbook” method can be easily automated by Excel Macro functionality, so it will be very easy for end user to copy Budget entries.




Источник: http://blogs.msdn.com/b/axsupport/ar...el-add-in.aspx