Creating and Posting Inventory ProfitLoss journals in DAX using .NET Business Connector

Yesterday, I was helping a friend from Canada create a small solution that would create and post ProfitLoss journals in Microsoft Dynamics AX 4.0 using .NET Business Connector.

In the end, we created 2 solutions - one was entirely written in X++, and only simple class static method calls were made from C#. The other was completely written in C#, using various classes, available with .NET business connector.

I uploaded the solution to the following link, in case anyone would be interested to download it and play around with it or use in their projects.
Download ProfitLossPostingAppl

Also, here is the source code - it is a small console application, and I tried to add as many comments as possible, so that even complete X++ beginners would be able to easily read the code.

Notice that in the solution, there is a reference to the business connector dll.
You can find the Microsoft.Dynamics.BusinessConnectorNet.dll in the Client\Bin folder of your Dynamics AX installation. This dll is what provides you with access to the Dynamics AX application and the set of classes to use AX tables and classes.

using System;
using Microsoft.Dynamics.BusinessConnectorNet;

namespace ProfitLossPostingAppl
{
    class AxProfitLossPostingEngine
    {
        static void Main(string[] args)
        {
            Axapta ax = new Axapta();
            // company name, language, object server, configuration
            // this uses Windows Authentication
            ax.Logon("cmul", null, "localhost", null);
            
            try
            {
                // Start a transaction
                ax.TTSBegin();

                // AxaptaRecord is a class that allows to work with Tables in AX
                AxaptaRecord header = ax.CreateAxaptaRecord("InventJournalTable");
                AxaptaRecord journalName = ax.CreateAxaptaRecord("InventJournalName");
                AxaptaRecord line = ax.CreateAxaptaRecord("InventJournalTrans");
                AxaptaRecord inventTable = ax.CreateAxaptaRecord("InventTable");
                AxaptaRecord warehouse = ax.CreateAxaptaRecord("InventDim");

                // You can call static table methods using the following syntax
                journalName = ax.CallStaticRecordMethod("InventJournalName", "find", "IPL") as AxaptaRecord;

                // There is a set of predefined methods on the AxaptaRecord class, like the clear(), initValue, DML operations, etc.
                header.Clear();
                header.InitValue();
                // You can call table object methods as well, not only static
                header.Call("initFromInventJournalName", journalName);
                header.Insert();

                line.Clear();
                line.InitValue();
                line.Call("initFromInventJournalTable", header);
                // You cannot use table fields directly as in X++. Instead you have set/get methods
                line.set_Field("itemId", "B-R14");

                // Instead of using static table methods (like find) you can execute a direct SQL statement and receive the result in the AxaptaRecord object
                inventTable.ExecuteStmt("select * from %1 where %1.ItemId == 'B-R14'");
                // If you receive more that one record you can iterate through them using Next (as in AX)
                line.Call("initFromInventTable", inventTable);

                line.set_Field("Qty", 160.0);

                warehouse.Clear();
                warehouse.set_Field("InventLocationId", "MW");

                warehouse = ax.CallStaticRecordMethod("InventDim", "findOrCreate", warehouse) as AxaptaRecord;
                
                line.set_Field("InventDimId", warehouse.get_Field("inventDimId"));
                line.Insert();

                // Notice AxaptaRecord is passed by reference here
                ax.CallStaticRecordMethod("InventJournalTable", "initTotal", header);
                header.Update();

                ax.TTSCommit();
                
                // You can call static class methods the same way you call table static methods, but using a different method on Axapta class
                // So in case you wrote the posting in X++, you would be able to call it, passing the JournalId as the argument
                // int numOfLinesPosted = (int)ax.CallStaticClassMethod("DEV_ProfitLossEngine", "postProfitLossJournal", header.get_Field("JournalId"));
                
                // Or, you can use the AxaptaObject class to accomplish the same from C# 
                // You can initialize a new class using the Axapta class method
                // AxaptaObject journalCheckPost = ax.CreateAxaptaObject("InventJournalCheckPost");
                
                // Or using a static method, if that suites your needs better
                // Notice here that an object of type AxaptaRecord is passed into a method that expects InventJournalTable as the argument
                AxaptaObject journalCheckPost = ax.CallStaticClassMethod("InventJournalCheckPost", "newPostJournal", header) as AxaptaObject;
                // You can object methods the same way you would on a table
                journalCheckPost.Call("parmShowInfoResult", false);
                journalCheckPost.Call("parmThrowCheckFailed", true);
                journalCheckPost.Call("parmTransferErrors", false);
    
                journalCheckPost.Call("run");
                int numOfLinesPosted = (int)journalCheckPost.Call("numOfPostedLines");

                Console.WriteLine(String.Format("{0} line(s) have been successfully posted", numOfLinesPosted));
                Console.WriteLine("JournalId is " + header.get_Field("JournalId"));
                Console.WriteLine("Press any key to continue ...");

                ax.Logoff();
            }
            catch (Exception ex)
            {
                ax.TTSAbort();
                ax.Logoff();

                Console.WriteLine(ex.Message);
            }

            Console.ReadKey();
        }
    }
}

P.S. Of course, it would probably be a better idea to use classed InventJournalTableData, InventJournalTransData, etc.
But for the simplisity of the example, everything is done directly with tables.

Microsoft Dynamics AX 2009 - some of the new development features

Alexei Eremenko from Microsoft Russia has posted a number of articles about the new features that will be available in DAX 2009.
The original link, which is in Russian (but the main thing to look at is the code, which is universal): http://blogs.msdn.com/aeremenk/archive/2008/01/15/7118429.aspx

I would like to make a brief review of the features he talked about for the English-speaking population and another feature I liked that Alexei did not mention.

Let's start with the support for union in SQL statements (but only when using Query* classes).

    query = new Query();  
    query.queryType(QueryType::Union); // The other value of QueryType is "Join"

Another Exception type has been introduced, which now actually allows to catch the DuplicateKey exception:

   Table t;

    try   
   {      
        while select forupdate t
       {
            test.Field1 = ‘xyz’;
            t.update();
        }
   }
   catch ( Exception::DuplicateKeyException, t )
   {
       infolog(‘Record already exists - ‘ + t.Field1 );  
   }

The bulk DML statements now allow using inner/outer joins, and you can access the result of the update_recordset operation to get the number of rows that were updated:

update_recordset batchJob setting
      Status = BatchStatus::Canceled,
      EndDateTime = thisDate,
      Finishing = 1
  where batchJob.Status == BatchStatus::Cancelling
  notexists join batch
  where (
          (batch.Status == BatchStatus::Ready ||
               batch.Status == BatchStatus::Executing ||
               batch.Status == BatchStatus::Hold ||
               batch.Status == BatchStatus::Cancelling)
          && batch.BatchJobId == batchJob.RecId
          );

  rowsUpdated = (batchJob.RowCount() > 0); // get the number of updated rows with rowCount()

And, the feature I enjoyed, is that we now have crossCompany support in X++. Meaning you can access data from tables from a number of companies in one query.
Here are code snippets to explain what I mean:

static void DataBaseAccess_CrossCompany(Args _args)
{   
    InventTable inventTable;   
    container   companyContainer = ['IN1', 'QMS'];   
    ;   
    while select crossCompany : companyContainer inventTable       
        where inventTable.ItemId == "B-R14"   
    {       
        print inventTable.ItemId, " -- ", inventTable.dataAreaId;   
    }   
    pause;
}

This code will print ItemId from 2 companies, even though InventTable has the property SaveDataPerCompany set to Yes.

The same functionality is available with the Query classes:

static void DataBaseAccess_CrossCompany_Query(Args _args)
{   
    Query                   query = new Query();   
    QueryBuildDataSource    qbds  = query.addDataSource(tableNum(InventTable));
    QueryRun                queryRun;
    InventTable             inventTable;
    ;
    qbds.addRange(fieldNum(InventTable, ItemId)).value(queryValue("B-R14"));

    query.allowCrossCompany(true);
    query.addCompanyRange("IN1");
    query.addCompanyRange("QMS");

    queryRun = new QueryRun(query);
    while (queryRun.next())
    {
        inventTable = queryRun.get(tableNum(InventTable));
        print inventTable.ItemId, " -- ", inventTable.dataAreaId;
    }
    pause;
}

If you don't add any specific company ranges, the data will be retrieved from all companies you have access to.

Microsoft Dynamics AX 2009 has a lot more to offer, of course. But enough for today.

Inside Microsoft Dynamics AX 4.0 in Russian

This entire week is actually special.

The Inside Microsoft Dynamics AX 4.0 book will very soon be available in Russian.
I was the one helping with the translation.
I am now finished with all 18 chapters with just some minor corrections left to other parts of the book.
So I will try to get back to posting about AX very soon.

Next week, on Tuesday (November 27th) Moscow will be hosting an annual Microsoft Technical Event.
You can read more about it on http://platforma2008.ru/ (Russian language only)
The new book will be presented there, so make sure and visit if you can.

Microsoft Dynamics AX Community recognition :)

Today is a special day for me. And for the community as well, I hope :)

I was replying to one of the posts on the community and noticed, that now I have a bronze medal near my name.

Here is what it means:
http://www.microsoft.com/wn3/locales/help/help_en-us.htm#Levels
(it means I had 51 replies marked as Answers)

List panels in Dynaics AX - a short description of SysListPanel class

SysListPanel class – what is it?

Whenever a user needs to select a number of values from a certain list, so that the selected list gets processed and saved, a list panel can be used. Another use of this type of control is when you are assigning certain properties/options to a specific object.

A good example is the “Administration\Users” form, where each user is assigned to one or more user groups, giving him certain access to DAX functionality.

Here is how it looks:

SysListPanel is the basic class for a large hierarchy of classes showing different list panels throughout the application.

The hierarchy tree for this class is shown below (DAX 4.0 SP2):

One important thing you need to understand when working with list panels – the class is not enough. :) When there is a class, there is a form, containing the list panel and using this class. But, of course, the class is responsible for most of the things happening when a user works with list panels.

SysListPanel or SysListPanelSet – which class to use?

When I consider, what class to extend when creating a list panel on a form, I start off by figuring out how the selected data is going to be stored. If the data selection is supposed to be reflected in the database (for example, when we add the user to a user group, a record is created in UserGroupList table), then extending from SysListPanel should be your choice in most cases.

SysListPanelSet is already an extension of the SysListPanel class, and has one extra variable declaration – inSet variable of type SET. This is the entity that is going to store the values a user selects in the list panel. They can, when selected, be used for further processing.

The SysListPanelSet_TableBrowser class, used in the DEV_SysTableBrowser project (http://www.axaptapedia.com/DEV_SysTableBrowser) is an example, using this set to create the selected controls in the browser.

You should also notice from the SysListPanel hierarchy tree, that there are a lot of classes already available in the application, so it is wise to re-use one of the existing classes, if it matches your requirements or extend from one of them and not from the base class.

Again, the SysListPanelSet_TableBrowser class is an example, as it is extending from the SysListPanelSet_Fields class. It uses the functionality of this class to get the data about table fields, extending them with information about display methods and interaction with the browser form.

Creating a list panel – what methods need to be implemented?

Let’s say the SysListPanelSet_TableBrowser class does not exist yet, and we are just planning to create it, along with the form.

Creating the form is the easy part. There are not many requirements that have to be fulfilled here.

First of all, of course, you need to have a variable to reference your listPanel class. Put it in the classDeclaration of the form, so that it is visible in all form methods. And after that all that is left to do is to create an object of your listPanel class, and the class will do the rest of the work.

Here is what needs to be done:

1. In the init() method of the form, you need to initialize the listPanel class, specifying, that it is this form you want the list panel to be on, the group that the list panel is going to be in on the form, and any other relevant parameters (for example, when using SysListPanelSet, you usually pass the current state of the values (inSet) as well.
2. Most convenient way to accomplish this is by creating a static method with the needed list of parameters, and call this method from the init() method of the form. Usually, this method is named newForm.
3. The SysListPanel class contains a lot of parm* methods, that you can use to modify the behavior of the list panel. The SysListPanelSet adds another method to that list, allowing to set the current set of selected values (parmInSet). You can see how these methods are used in Classes\SysListPanelSet_Fields_TableBrowser\newForm() method.
4. After basic initialization you have to call the build method. The build method of SysListPanel class is responsible for actually adding all the needed controls to the specified form. Depending on the parameters that you set before calling this method, the list panel will/won’t add the “Add All”/”Remove All” buttons, the “Up” and “Down” buttons, will set the needed number of columns shown in the panels, etc. You can override any of the methods, adding additional functionality (see \Classes\SysListPanelSet_TableBrowser \build() method) or even completely rewrite the code of the basic method (this is not recommended, of course). Notice, that the build method should be called BEFORE super() in the form’s init() method, because FormBuild*Control classes are used to add controls to the form.
5. After build method is executed, all FormBuild*Controls are initialized. You need to call the init method then, so that all Form*Control variables are initialized. This method should be called AFTER the super() call in the form init method, as Form*Controls don’t exist until super() is called.
6. The last thing you have to do is call the fill method of your new class, so that the list panel is filled with needed data. Let’s talk about this method in a little more detail below.

The Classes\SysListPanel\fill() method, basically, consists of 4 parts:

1. getData method is called. This is the method that gathers the data for the left and right part of the list panel. This is an abstract method, and it has to be implemented in the extending class. It returns a container with all the information for the list panel. It is different from class to class and depends on the data being shown in the list panel.
2. Both parts of the list panel are cleared and list panel columns are created (the first time).
3. fillView method is called for both parts of the list panel. These methods use the data generated in step 1.
4. Buttons are enabled depending on the data in both list panel parts. For example, if there are no items selected (left part of the list panel), than the "Remove" and "RemoveAll" buttons are disabled.

The rest of the methods on SysListPanel class are for handling events that occur from user interaction.

The class is the handler of these events in most cases, controlMethodOverloadObject method is used to specify that when the list panel is built.

I guess, the only two methods left that are worth mentioning are the abstract methods addData and removeData. Both of these have to be implemented in the child class, providing the logic for adding and removing items from the left part of the list panel. For example, for SysListPanelSet class the implementation is very simple, just adding or removing the item from the inSet object. For classes extending directly from SysListPanel these methods are where the main logic is located, adding or removing records from the database. In most cases, these methods simply call a server method that does all the needed actions (Remember the rule of thumb – place database logic as close to the database layer as possible).

That is pretty much all you need to know to use and create list panels in Dynamics AX.

Conclusion

Of course, this is not a full description, and much can be added to the information posted here.

If you have any additions, corrections or questions, feel free to post them as comments.

See also

\Forms\Tutorial_SysListPanel