Load trade agreements using Excel add-on

Today, a colleague asked me to help loading trade agreements. I tried Excel add-on and there is a little trick. When I opened in Excel the PriceDiscAdmTrans table where the trade agreements reside, got an error

Here is what I did:

1. 1     Manually create a trade agreement in AX and take a note of the journal number. Leave lines blank.
   2.  Go to AOT and modify the followings on the usr layer 
   a.       Go to table PriceDiscTable, Indexes PriceDiscIdx, change AlternateKey to Yes
   b.      Go to table PriceDiscTable, Properties, change ReplacementKey to PriceDiscIdx
   c.       Save changes
   d.      Click Generate incremental CIL button
   3.  Now use Excel add-on to populate the fields for table PriceDiscAdmTrans and you should be good to go. See my previous post on how to work with the Excel add-on.
   4. After all this of course remember to delete all changes on the usr layer and re-compile.

Override permissions in a role

I recently came across a trick on setting up security in AX 2012. We wanted to create a new role by copying an existing role and removing a few duties from the role. I managed copying the role through some coding and that went well. However, when I tested the new role, it doesn't behave exactly the same as the original role. After poking around a bit, I found this nice new function in AX 2012 "Override Permissions" on the role form. As Microsoft noted, "Overrides for securable objects are not associated with specific duties or privileges. If you apply an override, the access level for the object is set for the role, regardless of access levels specified by the duties and privileges assigned to that role." I found this is better and easier than changing the duties or privilege itself.

You can also go to the AOT and look up in the table SecurityRolePermissionOverride and see what permissions are overridden.

One last note, you don't want to override too many permissions as it'll slow down your system performance.


Updated on 2/29/2012
Today had two discoveries on AX 2012 security settings.

1. Unlike AX 2009, any change from the Security Role/Privileges form (e.g. adding a new role, adding/removing a duty from a role) auto triggers a change on the AOT under the Security section! If you're working from a usr layer (as a regular functional consultant would do), the changes will be on the USR layer!! I learned this in a hard way as I found all my security settings gone after a recent code deployment. So, if you don't want to keep redo your security settings in AX 2012, log onto the CUS layer and do the configuration, or merge the code after you're done on the USR layer. 
2. Another way to duplicate an existing role is to go to AOT>Security>Roles and duplicate the role. Then all duties and permissions will get copied to the new role. Much better.

AX 2012 Data Export/Import

Meant to do a blog on data import in AX 2012 for a long time but didn't have time. In previous AX versions, data can be import/export using Excel template in AX. Data migration in AX 2012 is very different because of the data structure changes in tables. For some reason it's been a mysterious puzzle in AX 2012 implementation for a lack of documentation, and it's become a non-intuitive process. Based on sources available from AX online community and Microsoft support, the followings are the options for data import in AX 2012:

1. Write code to use AIF web services. This is actually recommended by Microsoft support. Problem is you'll need a developer.
2. Use AX export/import definition group. This option exists in previous versions too and it creates a .dat file and .def file. Problem is the exported data is hard to edit and the .def file is difficult to understand. It's a good tool make a duplicate company but not for loading master records.
3. Microsoft just released an online service RapidStart last October, which is meant to facilitate initial company config and data import. Though I had a bad dream with this service for the first time played it, I still have my faith in this tool. Microsoft engineer has come back to us and asked for more testing, but I just didn't get time to take on this thread for now. Will report back if I get time to test it more. Feel free to drop me a line if you have experience with the service.
4. Use Excel AX add-on. This is by far the most efficient tool we've found for data import. Similar to the old Excel template and easy to use.

Here is how to load items into AX 2012 using Excel add-on. To make our example more interesting, I'll load products and items (i.e. released product) together. Assume product and item setups have been properly configured in AX. 

Open Excel and click Dynamics AX tab. Click Connections, select Legal Entity and click Ok

Select Add Data - Add Table, and add the following tables and click Ok

• EcoResProduct
• EcoResTrackingDimensionGroupProduct
• EcoResStorageDimensionGroupProduct
• EcoResProductTranslation 
• EcoResProductDimensionGroupProduct
• InventTable 
• InventModelGroupItem
• InventItemGroupItem
• InventTableModule (optional)
• InventItemSetupSupplyType  (optional)

InventItemSetupSupplyType is optional. But, if it's not included, item won't show on the item dropdown on purchase order lines.

Fill in data on each worksheet. If need to look up field value, can click Field Lookup button on the ribbon. A few tricks on working with the tables:

• Close the Field Chooser side column before your edit data
• Only include table fields you'll put data in.
• Make sure your cursor is outside the work area, or otherwise Excel would complain your data is incomplete. 
• DO NOT tab at the end of the table, or otherwise it'll generate a new line at the end and cause data incomplete error.

After filled in all data needed, click on Publish Data - Publish Options. You must tell AX the sequence how to load the tables, for example, the above listed tables are in a correct sequence. Or otherwise, when you try to load data, you'll get an error saying data doesn't exist in other tables. 

Finally, click Publish Data - Publish All to load data. If everything goes well, you should get a separate worksheet and it shows the number of records that have been loaded successfully or how many were failed. 

Here is the template file I generated. Very simple and you can expand it to include more fields to fit your needs.

Excel AX add-on seems like a great tool for data migration. I'm still learning all the tricks myself too. Just find another blog on the same topic today Dynamics AX 2012 Products Import using Excel Add-in. It has great details. If you have any experience or comment on data migration in AX 2012, feel free to let me know.