Thursday, November 22, 2012

XML transformation from Excel spreadsheet

Being inspired from sample code in Inside Microsoft Dynamics AX 2012 I have made a small sample of how you can use an Excel spreadsheet as input for an AIF XML transformation.

To emphasize on the important bits and pieces, I have left out all the sugar and bells and whistles.
Also the XML code is not exactly formatted as AIF expects it, but you can follow this article to figure out how to do that.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Data;
using System.Data.OleDb;
using Microsoft.Dynamics.IntegrationFramework.Transform;
namespace TestExcelTransform
    public class TransformExcel : ITransform
        public void Transform(System.IO.Stream input, System.IO.Stream output, string config)
            string excelFilename = @"C:\TEST\output.xlsx";
            // Save the received XML in a location available from the AOS
            TransformExcel.saveExcelFile(input, excelFilename);
            TransformExcel.saveAsXML(excelFilename, output);
        private static void saveExcelFile(System.IO.Stream input, string filename)
            System.IO.Stream excelOutput = new System.IO.FileStream(filename, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write);
            // The CopyTo method requires .NET 4.0
            // In older versions you need to save the stream with a bit more code:
            // public static void CopyStream(Stream input, Stream output)
            // {
            //     byte[] buffer = new byte[8 * 1024];
            //     int len;
            //     while ( (len = input.Read(buffer, 0, buffer.Length)) > 0)
            //     {
            //         output.Write(buffer, 0, len);
            //     }    
            // }
        static private DataSet ReadDataFromExcel(string filename)
            string connectionString;
            OleDbDataAdapter adapter;
            // The connection string required to access the spreadsheet as a datasource
            connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;"
                + "Data Source=" + filename + ";"
                + "Extended Properties='Excel 12.0 Xml;"
                + "HDR=YES'";  // Sheet has row header with column titles
            adapter = new OleDbDataAdapter(
                "SELECT * FROM [sheet1$]",
            DataSet ds = new DataSet();
            // Get the data from the spreadsheet
            adapter.Fill(ds, "Customers");
            // Return the dataset
            return ds;
        private static void saveAsXML(string filename, System.IO.Stream output)
            XmlTextWriter xwriter = new XmlTextWriter(output, Encoding.UTF8);
            string rootName = "Customers";
            string rowName = "Customer";
            // Start writing the XML file.
            xwriter.Formatting = Formatting.Indented;
            xwriter.WriteComment("customers.xml file");
            // Get the Customers table from the data source
            DataTable table = TransformExcel.ReadDataFromExcel(filename).Tables["Customers"];
            foreach (DataRow row in table.Rows)
                string accountNum = row["Account"] as string;
                string name = row["Name"] as string;
                // Loop through each line of data in the file.
                // Write field elements
                xwriter.WriteElementString("Account", accountNum);
                xwriter.WriteElementString("Name", name);
                // Write the </Customer> end element.
            // Write the </Customers> end element.

And here's the contents of the spreadsheet (account numbers are formatted as text):
And the result:

Thursday, November 15, 2012

Thursday, November 8, 2012

Wednesday, November 7, 2012

Update cross reference in batch

In AX 2012 R2 you can update cross reference in batch.

You just need to add the class xRefUpdateIL to a bacth job task.

It seems like a batch task for this is automatically created when doing a full compilation, but I haven't really investigated the details of that. Any comments on that are very welcome.

Monday, November 5, 2012

Two new AX books from Packt Publishing

Microsoft Dynamics AX 2012 Security How-To - Available now
I read it, but find it a bit thin. There's not really any new information that you can't already read from the available Microsoft documention. Note the low page count!

Microsoft Dynamics AX 2012 Services -  Available for pre-order
I'm one of the reviewers of this book, so I have already read it. I can really recommend this book to anyone who needs to work with services in AX.

Friday, November 2, 2012

Team Foundation Service is now live

The Team Foundation Service is now live.

If you have tried the preview version, you'll eventually have to change the URL to the service. The new URL is

You'll still need to install the hotfix mentioned here, if you are new to the service.

You can learn more about the service here:

The service is to some extend free to MSDN subscribers.

Wednesday, October 31, 2012

Doing a CIL compilation

Compiling on the the R2 virtual machine where I have assigned 12Gb of memory. I wonder if there is any upper limit to how much memory a CIL compilation can consume.

Monday, October 15, 2012

Saving the Financial Statement as PDF

This is just a small proof of concept on how you can save the Financial Statement as PDF without any further user interaction.

The example builds on the Excel export, but rather than showing the spreadsheet to the user, the spreadsheet is saved as PDF. When saved, the spreadsheet is closed without the user ever seeing it, and the PDF is opened in stead.

Download an XPO with the modifed class LedgerBalanceSheetDimPrintExcelEngine here.

Friday, August 31, 2012

Wednesday, August 29, 2012

Inside Microsoft Dynamics AX 2012 available for pre-order

You can now pre-order the newest member of the Inside Microsoft Dynamics AX book series from Amazon.

It doesn't seem like it's comming in a Kindle edition, but one can only hope.

UPDATE November 22, 2012:
The book is in fact available for Kindle now. From O'Reilly you can get the book in several other e-Formats.

Thursday, August 9, 2012

Combining file paths

When I develop something where file paths or file names must be combined, I’m always in doubt how to make sure backslashes are in the right place.

The simple example is when you need to combine a file path with a filename. Should the path end with a backslash or not? Did the user enter the path with an ending backslash or not in e.g. the parameters for the job?

You end up either ignoring one scenario or adding code to figure both out. This could eventual be a method where you decide it always returns the path with the ending backslash.

Well, in .NET there’s a static method, System.IO.Path.Combine, taking care of all this. You just pass it a number of paths and it ensures that backslashes are in the proper places in the returned string.

Here's a small example in X++:
static void PathCombine(Args _args)
    str         path1 = @"C:\TestDir";
    str         path2 = @"C:\TestDir\";
    str         path3 = "MyFolder";
    str         combination;

    combination = System.IO.Path::Combine(path1, path3);

    info (strFmt("Combinig '%1' with '%2' gives '%3'", path1, path3, combination));
    combination = System.IO.Path::Combine(path2, path3);

    info (strFmt("Combinig '%1' with '%2' gives '%3'", path2, path3, combination));

Friday, August 3, 2012

Using Team Foundation Service with AX 2012

Microsoft is working on enabling Team Foundation Server as a cloud based service. You can try it out for free for the time being and you can even use it with AX 2012.

You can sign up for the service here:

In order to connect to the site from AX (and Visual Studio) you need to install the following:
  • Visual Studio 2010 SP1
  • KB2662296 for Visual Studio 2010 SP1
If AX is open when installing KB2662296, you'll need to restart it before configuring version control.

The address  for the Team Foundation Server to use for setup in AX is:

The rest of the setup in AX is the same as usual.

These sources made it possible for me to figure this out - thanks:

I haven't tried to use this from AX 2009, but I'd imagine that would work as well.

Wednesday, August 1, 2012

"View details" bug in collection letter and interest note forms

When you hit "View details" from the customer account number fields in one of the collection letter and interest note related fields, you'll open a form showing the customers open transactions and not the customer main data as expected.

The tables for collection letters and interest notes has two relations from their AccountNum fields; one for CustTable and one for CustTransOpen. For some reason AX chooses to base the view on the CustTransOpen relation.

You can fix this by adding the following code to the jumpRef method of the AccountNum field on the respective datasources:
public void jumpRef()
    // There are relations from the AccountNum field to both CustTable 
    // and CustTransOpen,
    // and AX chooses to use the CustTransOpen relations by default

Monday, July 9, 2012

Do not try to update Dynamics AX 2012 Feature Pack 1 Demo Virtual Machine (VM) with CU3

It won't work.

The documentation for CU3 states the following (which I regret not reading before going ahead with the update):
  • Updating Dynamics AX 2012 Cumulative Update 3 on Dynamics AX 2012 Feature Pack 1 Virtual Machine (VM) 6.0.1100.0 does not update the system with Dynamics AX 2012 Cumulative Update 3 Feature Pack Patch (FPP) model
  • Updating DynamicsAX 2012 Cumulative Update 3 on DynamicsAX 2012 Feature Pack 1 Virtual Machine (VM) 6.0.1100.0 may result in the termination of Application Object Server (AOS) during compilation, done as a part of upgrade

There's no information whether this will be fixed or not, or if there'll soon become a new image available.


Thursday, June 28, 2012

Quick warning about the fill utility

I have reported some issues with the fill utility to Microsoft support, and apparently the has a severe bug if you try to update fields from a datasource that is not the main datasource of the form you called it from.

If you attempt to use the fill utility in a form to change a field that is not from the main datasource, data in fields from the main data source is updated in stead.

This is unfortunately a bug that the product team will not fix. If you contact Microsoft support with a reference to case reference number 297616, they'll be able to provide you with code that disables the fill utility when called from a non-main datasource.

Monday, June 25, 2012

Check if two layer nodes are in fact equal

Here's an example on how to check if two layers of a treenode match with no differences:

static void CompareTwoHighestLayers(Args _args)
    TreeNode    treeNode = TreeNode::findNode(@'\Data Dictionary\Tables\CustTable');
    SysTreeNode node1;
    SysTreeNode node2;

    node1 = SysTreeNode::newTreeNode(SysTreeNode::getLayeredNode(treeNode, 0)); // 0 = Highest layer of the treenode
    node2 = SysTreeNode::newTreeNode(SysTreeNode::getLayeredNode(treeNode, 1)); // 1 = Next highest layer of the node
    info (strFmt("Equal (%1 <--> %2): %3", SysTreeNode::getLayer(node1.parmTreeNode()), SysTreeNode::getLayer(node2.parmTreeNode()), SysCompare::silentCompare(node1, node2)));    

Friday, June 8, 2012

Free ebook: Introducing Windows Server 2012

Chapter 1 The business need for Windows Server 2012 
The rationale behind cloud computing Making the transition
Technical requirements for successful cloud computing
Four ways Windows Server 2012 delivers value for cloud computing Foundation for building your private cloud

Chapter 2 Foundation for building your private cloud 
A complete virtualization platform
Increase scalability and performance Business continuity for virtualized workloads

Chapter 3 Highly available, easy-to-manage multi-server platform
Continuous availability
Cost efficiency
Management efficiency

Chapter 4 Deploy web applications on premises and in the cloud 
Scalable and elastic web platform
Support for open standards

Chapter 5 Enabling the modern workstyle 
Access virtually anywhere, from any device
Full Windows experience
Enhanced security and compliance

Tuesday, June 5, 2012

"Microsoft Dynamics AX 2012 Development Cookbook" review

Mindaugas Pocius has updated his development cookbook for AX 2009 to this “Microsoft Dynamics AX 2012 Development Cookbook”.
The book gives you detailed recipes for solving common development tasks such as importing data to journals or creating and posting sales and purchase orders.

Each chapter contains an introduction of the tasks covered, the detailed solution and a detailed explanation of how the solutions works. Many chapters also holds a section where the solution is either tweaked a bit or expanded to do more. Accompanied with lots of screen clips you'll never feel lost while working with any of these recipies.

Chapter 1 is about processing data. It's on of the more diverse chapters. It covers tasks like creating new number sequences, adding document handling notes, building query objects, executing direct SQL, working directly with XML files,  using the date effective feature and more. 

I don't think executing direct SQL can be considered an everyday task. I would have preferred more about regular AX queries in stead. But the explanation itself of direct SQL is all right. 

I'm very happy that there's an example on how to enhance the data consistency check. Many developers are not aware of the existence of these checks.

Chapter 2 is all about forms, or nearly all about forms. I'm missing stuff about list pages and security on forms. This chapter doesn't seem to changed much from the previous book.

The chapter covers however important areas, such as how to create dialogs, handle dialog events, build dynamic forms, add form splitters, create modal forms, store usage data, use tree controls and more. 

Chapter 3 is about working with data in forms. It covers areas like using a number sequence handler, creating custom filters, building selected/available lists, preloading images, creating wizards, processing multiple records, coloring records and more. Personally I loath colored records. I prefer other indicators on the lines, such as icons. I don't agree with the author that the colored records makes the system more intuitive and user friendly. 

With the new  checkbox interface for selecting several records, I hope to see more solutions where users are able to work with several records at a time. And I hope that this section for the next book will be expanded to also explain the client/server issues you could get yourself into, if users selects many records and your business logic is executed on the server-tier as it should. 

Chapter 4 is an entire chapter devoted to building lookups. These are automatic lookups, dynamic lookups, form based lookups, tree lookups and more. I can't really think of a lookup type that is not covered. Excellent chapter.

Chapter 5 is called "Processing business tasks" and covers a variety of AX module related business tasks. This is the only chapter you can say is really about module functionality. It covers good ground about using the new segmented entry control, creating journals of different types, creating and posting ledger vouchers, creating and posting sales and purchase orders and more. I think this chapter really hits the nail on the most common, or unavoidable, business tasks you will encounter as an AX developer.

Chapter 6 is about integration with Microsoft Office. It explains, with good examples, how you can work with Excel, Word, and Project files. And it also explains how to send e-mail using Outlook. In relation to Outlook, I would have liked to see some information about the possibilities for getting rid of the dreaded "A program is trying to send an e-mail message on your behalf...." dialog. That's the first thing a user will complain about when implementing a solution where Outlook is used to send e-mail from AX. 
Also I'm missing just a little information about the new service based integration with Excel, even though it might not be directly considered a development task. At some point you will most likely be asked to debug it though.

Chapter 7 covers services; system query services, metadata services, document services, enhanced document  services, custom services and external services. If you need to work with services, you can learn a lot from this chapter.
Chapter 8 is about improving development efficiency and covers stuff like creating editor templates, modifying the Personalization form, modifying the application version and more. This a chapter I'd might have replaced with something else, if I only had a certain number of pages for the book. 

I'm glad however to read the stuff about modifying the application version. I have seen this done wrong, in ways that choke AX's upgrade algorithms, so many times. 

Chapter 9 is about improving the performance of the product. It's an all right chapter, but I might have sacrificed the previous chapter to make more room for this chapter. It explains how to calculate code execution time, write efficient SQL statements, caching display methods, using the Dynamics AX Trace Parser and the SQL Server Database Engine Tuning Advisor. 

I would have liked to see more about reducing client/server chattiness through RPC. If you have too many calls back and forth or move to much data between server and client, it doesn't really matter much how efficient your SQL statements are.

You could always ask for more in a book like this, but for a book of this size the content is well prioritized. I would however suggest that Mindaugas for the next book maybe hook of with some co-writers in order add a few hundred pages more. 

Some of the areas I'm missing recipes about is:
.NET CLR Interop
Writing code in .NET with Visual Studio
Enterprise Portal

The book is well worth the money and if you have it I'm sure you'll be using it regularly, cutting off time from your development projects.

Read more and order the book here:

Monday, May 14, 2012

Book Give-away: Hold a chance to win free copy of the 'Microsoft Dynamics AX 2012 Development Cookbook', just by commenting!

Packt Publishing have two copies of Microsoft Dynamics AX 2012 Development Cookbook to be given away to two lucky readers of this blog.

How you can win:
To win your copy of this book, all you need to do is come up with a comment below highlighting the reason "why you would like to win this book”.

Duration of the contest and selection of winners:
The contest is valid up until June 1st., and is open to everyone. Winners will be selected on the basis of their comment posted.

Packt Publishing writes about this book: "Microsoft Dynamics AX 2012 Development Cookbook is written by Mindaugas Pociusis, a recognized Microsoft Certified Professional for Dynamics AX who is also one of the leading experts in Microsoft Dynamics AX. This is a practical cookbook, designed to demonstrate advanced development techniques for exploring data manipulation concepts in Dynamics AX. With the creation of custom lookups using AOT forms and generating dynamically from the X++ code, readers can enhance applications by using advanced form controls to create various Microsoft Office documents for exporting/importing business data for further analysis.

After reading this extensive cookbook, readers will not only be able to create and manage purchase and sales orders from code, but also create a custom electronic payment format and process a vendor payment using it. This book will help Dynamics AX developers to go beyond the basics of Dynamics AX programming and use implementations which can also be applied to learn the functional aspects of Dynamics AX."

Please note that comments are moderated and your comment will not appear immediately.

"Nick Peterson" and "Gorjan".

Friday, May 11, 2012

Microsoft Dynamics AX 2012 Development Cookbook is out

A new cookbook for AX 2012 has just been released:

Reactivating windows license on demo images

If you are using the demo version Hyper-V image (eventual with VirtualBox) you can re-activate the license up to 3 times using the rearm script

In order to "rearm" Windows on the image, open a command prompt (Start / Run / Cmd) and type the following and enter:
slmgr -rearm

It will eventually tell you that re-arm was successful and to restart your computer (the image). After you've restarted the image, you will need to activate Windows again to continue using the image.

Go to Start and in the search field type in 'Activate Windows' and follow the instructions. (you will need to be connected to the internet from your image)

That will give you a licence for another 180 days.

On the newest image you can just click the "Rearm windows" script in C:\Program Files\Microsoft\rearm

Monday, April 23, 2012

How to change the UUID of a virtual disc for use with VirtualBox

The virtual disc images we get from Microsoft usually have the same UUID as earlier disc images from Microsoft.

This is a problem if you wan't to run several images with VirtualBox because VirtualBox assumes, well that the different discs have different UUID's.

Here's how to change the UUID of a new disc image.

Open a command prompt and go to the VirtualBox program folder. This is most likely C:\Program Files\Oracle\VirtualBox

Run the vboxmanage internalcommands sethduuid [file] command. For example:
vboxmanage internalcommands sethduuid "C:\DEMO\AX2012-A.VHD"

Tuesday, April 17, 2012

MS internal procedures in the "How to Write Upgrade Scripts for Microsoft Dynamics Ax 2012"

This is just a heads up, so you don't let yourself be confused about the instructions.

In the Coding Best Practices chapter there's a sub-chapter called Deleting a Table or Field from the Data Model. The specific instructions under 1.a and 1.b are internally instructions related to how Microsoft's works with their internal version control system.

The rest seems relevant enough; You need to rename the elements, pre-fixed with "DEL_". You need to apply the elements to the relevant "SysDeletedObects..." configuration key. You have to move the elements manually to your own "Upgrade" model.

Friday, March 16, 2012

How to open a form in a running AX client from an external application

You can call the various startup commands in AX from an url like "dynamics://0/?".

Check this article for an overview of how to do it, and to get startup command code to open a specific form:

I have tried to create another pipe in order to make my own implementation of the pipe handling, but it seems like anything else than the pipe name for Events is ignored.

Monday, March 12, 2012

Preventing users from opening new workspaces

In AX 2009, before hotfix roll-up 6, AX has some issues when users open multiple workspaces with different company accounts.

I have seen issues where voucher numbers were pulled from the wrong company account and I have seen a data import going totally awry.

You can't completely prevent users from opening new workspaces through the security model, but you can add code that immediately closes these aging. Add the the following code to \Classes\Info\workspaceWindowCreated:

void workspaceWindowCreated(int _hWnd)
    // Put workspace window specific initialization here.
    // Begin -->
    // End <--

Report Developer Resources

TJ Vassar has made a great list over report developer resources, that I'd like to share:

Friday, March 9, 2012

AX 2012 "Doh" upgrade experience

When preparing to upgrade your code by importing an old layer from the previous version, make sure that you don't import any changes to the SysCheckList classes.

If one of these won't compile on AX 2012 you're kind of stuck, unable to start AX properly until you uninstall the new model again.

Monday, March 5, 2012

AX 2012 upgrade tip for lazy idiots like me

I didn't read the upgrade material carefully enough, and imported the upgrade framework to my AX 2009 application, before activating multisite in all companies. Now there's an issue with one of the tables of the framework, preventing me from activating multisite.

At this point I could of course uninstall the upgrade framework again, but my laziness is in the way of that solution.

Adding the table to the list of tables the multisite activation wizard shouldn't concern itself with seems to fix the problem:

Use this tip at your own risk.

I'd however like to consider myself a bit excused, when the upgrade guide contains the following statement:

Friday, February 10, 2012

Inserting code snippets in the AX 2012 X++ editor

In the AX 2012 X++ you can quickly insert fragments of code to avoid tedious typing, such as typing for a loop.

To insert a snippet, you must enter the snippets alias (which is usually the first symbol of the code), and press tab. For example, typing switch followed by tab, gives you this snippet:
switch ()
    case :

    case :

I don't have a clear picture of all the available aliases, but these work:
  • switch
  • for
  • do
  • while
  • try
In the regular Visual Studio editor you can add your own code snippets. I haven't figured out yet, if this is also possible for the X++ editor.

UPDATE: Brandon, a fellow blogger, wrote a great blog post about these scripts: AX 2012 - xppSource Exposed: Inserting code snippets

Preventing client crashes when applying new event handlers

Almost every time I create a new pre- or post-event handler and try to use use it immediately on a new Event Handler Subscription, the AX client crashes.

You can prevent these crashes by saving the event handler class and restoring it, before addressing it on a new Event Handler Subscription. Restore is placed on right click / Restore.

Thursday, February 2, 2012

"Find as you type" in the AX 2012 X++ editor

As you may know the new X++ editor in AX 2012, is actually the Visual Studio editor.

One of the sweet things that gives us, is the ability to do "Find as you type" searches in the code.

Press CTRL + I once, and set of binoculars pops up. Now start typing, and you can see how the editor starts to find matching values in your code. If you want to stop the typing, and just find the next instance of the same text, just press CTRL + I again.

To search upwards in the code, press CTRL + Shift + I.

Tuesday, January 24, 2012

Company Visualizer is updated

I have updated the company visualizer, fixing the issue I had about database lookup.

Orginal post.

Tuesday, January 17, 2012

Test target in unit tests

When you set the SysTestTargetAttribute in a unit test case class, you should take care in assuring the validity of the attribute. There are no warnings or errors if you point to an element that does not exist.

If you run unit tests with code coverage, your test will be unresponsive for longer than expected. This is because the code coverage is calculated against the entire AOT if the element from SysTestTargetAttribute doesn't exist.

Click here, for the MSDN section on AX 2012 unit tests.

Wednesday, January 11, 2012

FTP with C#

A couple of links with information on how to work with a FTP server from C#:

I have used this information to write a specific FTP client class in a Visual Studio assembly in AX.

AIF: Execute messages on demand

Sometimes when you try to debug problems with an AIF message, you'd like to just execute it rather than wait for the batch server to pick it up.

This job can do just that. It requires the GUID of the message and a call to \Classes\AifInboundProcessingService\processAsUser:
static void debugAIF(Args _args)
    AifMessageId    messageId = str2guid("8D2D6B9C-B0A9-48FA-B58E-0CAAD415E65B");


Spelling issue with the LogisticsAddresssCity table

If you need to use the LogisticsAddresssCity table, the editor's intellisense feature will quickly enough offer you the LogisticsAddressCity option.

But beware; this is the extended data type LogisticsAddressCity, which the icon and the tooltip correctly enough indicates.

The table though has the addressing part of its name spelled with three s'es: LogisticsAddresssCity. So you'll find that quite a long way further down in the intellisense dropdown.

That nearly made me insane yesterday, until my eyes crossed the third s of the table name.

Sample file import with StreamReader

Here is one example of how you can import files with .NET's StreamReader:
static void ImportWithStreamReader(Args _args)
    Filename                filename = @'C:\Temp\importme.txt';           
    System.IO.StreamReader  reader;
    System.String           line;
    InteropPermission       interopPermission;
    interopPermission = new InteropPermission(InteropKind::ClrInterop);

    reader = new System.IO.StreamReader(filename, 

    line = reader.ReadLine();

    while (!System.String::IsNullOrEmpty(line))
        // Do something with line

        line = reader.ReadLine();

If you can work with the file as on big chunk of text you can get away with just reading once, using the ReadToEnd method.

Read more about StreamReader on MSDN.