Hotkeys and Find vs Filter in Dynamics AX 2009

In Microsoft Dynamics AX 2009 a number of changes regarding Filtering in grids were introduced. I would like to talk about one of them today.

As most of you know, in AX 4.0 the Ctrl+F hotkey was replaced with Ctrl+K, which was very confusing for customers that were using AX 3.0 at the time. (see one of my previous posts with a .dll go fix that)

Well, I am happy to tell you, that Ctrl+F is now back as a regular (not Global) Find option in AX 2009. But Ctrl+K is also available. So it was decided, that having to identical commands is not really smart. So now they actually perform different actions.

Ctrl+F is Find
Ctrl+K is Filter = Filter by Field from the context menu on the grid line.

Now, you might wonder what the difference is. I will explain it on an example:

You have a grid with items of different Item Type (BOM, Item, Service) and different Item Group (Parts, Bulbs, etc.)

Scenario 1: (Notice the dialog caption is Filter)
1. Press Ctrl+K on Item Type "BOM"
2. Press OK (the value BOM is already inserted in the search field).
3. Only BOM items are shown
4. Press Ctrl+K on Item Group "Parts"
5. Press OK (the value Parts is already inserted in the search field).
6. Now only BOM items with Item Group = Parts are shown.

Scenario 2: (Notice the dialog caption is Find)
1. Press Ctrl+F on Item Type "BOM"
2. Input "BOM" and press OK (you have to input the value you are searching for every time).
3. Only BOM items are shown
4. Press Ctrl+F on Item Group "Parts"
5. Input "Parts" and press OK.
6. Now only items with Item Group = Parts are shown, including both BOM and other Item types.
So, basically, all previous filtering on the grid was removed before applying the new search criteria.

As a side note, I would also like to mention, that some changes were made to hotkeys in the Editor: Now, Editor Scripts are opened using Alt+R hotkey, instead of Alt+M, as in previous AX versions. (Alt+M was reserved for the global menu toolbar, which is one of the new features in AX 2009 as well)

Find all reports with datasources innerjoined 1:n

I haven't really written much X++ code in the past 5 months, so a work-related task on X++ was like a holiday :)

I needed to find all reports, in which there is a child datasource (on some level) that is inner joined with the parent datasource as 1 to many.

Here is one of the possible solutions of the problem.
I would also want to talk a little bit about different methods used in this scenario, in case some of you are still unfamiliar with those:

static void FindReports(Args _args)
{
   #AOT
   Report                  report;
   TreeNode                treeNode = TreeNode::findNode(#ReportsPath);
   TreeNodeIterator        iterator = treeNode.AOTiterator();
   QueryBuildDataSource    qbds;

   boolean  find1nInnerJoin(QueryBuildDataSource _qbdsParent)
   {
       int i;
       QueryBuildDataSource qbdsChild;
       boolean    ret;
       ;
       for (i = 1; i <= _qbdsParent.childDataSourceCount(); i++)
       {
           qbdsChild = _qbdsParent.childDataSourceNo(i);
           if (qbdsChild)
           {
               if (qbdsChild.joinMode() == JoinMode::InnerJoin && qbdsChild.fetchMode() == QueryFetchMode::One2Many)
                   return true;

               if (qbdsChild.childDataSourceCount() > 0 && find1nInnerJoin(qbdsChild))
                   return true;
           }
       }
       return ret;
   }
   ;

   treeNode = iterator.next();
   while (treeNode)
   {
       if (treeNode.sysNodeType() == 202) //Report
       {
           report = treeNode;
           if (report && report.query().dataSourceCount() > 1)
           {
               qbds = report.query().dataSourceNo(1);
               if (find1nInnerJoin(qbds))
                   info(report.name());
           }
       }
       treeNode = iterator.next();
   }
}

Here are some keypoints:

1. Notice the use of TreeNodeIterator class. This is an example of an Iterator applied to the AOT. Very convenient and easy to use. You can read more about this class and its methods on MSDN

2. Notice the use of BaseEnums QueryFetchMode and JoinMode - I have seen many developers specifying integer values instead. I would suggest using these enumerations instead - the code will be much easier to read later on.
I don't know the enum for return value of method treeNode.sysNodeType() though. So if someone does, write a comment - it would be nice to know.

3. Notice the use of an implicit conversion from a base type object (TreeNode class) to a derived type object (Report). This is allowed, because, as you know, X++ type system is a "weak" type system. But beware, because this might lead to run-time errors. For example, if you call a method on the Report class, that is not inherited from the TreeNode class, you must be absolutely sure that the object is actually of type Report. Otherwise, you will get a run-time error.
This is why before the conversion, I verify that only Report objects get through to the following code.

4. The last, but not least: Notice the methods exposed by the QueryBuildDataSource class - basically, it allows to receive a lot of information about a specified query. Again, for more information, refer to MSDN

A not-so-easy game about Earth

Here is a fun game that challenges your brain. It's not really about AX, but can drive you crazy as well :)
I am still stuck on level 9, and it turns out there are so many places I haven't even heard of, not mentioning actually placing them on the world map. Very entertaining!

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.