Lookup methods tutorial + custom list lookup implementation

One of the great features available in Dynamics AX is the support of lookup forms that provide a user-friendly interface for selecting a value for a field from a list, and are highly customizable, allowing the developer great flexibility in meeting user needs.

Recently, I was posed a question of how to present a user with a list of custom-defined values in a lookup form.

The simple (and suggested) approach here is to create a new BaseEnum, containing all the specified values, and add a ComboBox control for this enumeration to the form. If the values are to be displayed conditionally (only a subset is shown each time), you can build a superset of all values in AOT, and use SysFormEnumComboBox class to limit the actual output at runtime.

But, this would not work in my case, because the values in the list were dependent on other data in the form plus the current date. Using a temporary table for this scenario seemed like an overkill (but possible). So i decided to investigate the system class SysLookup for hints on how this can be done with minimum effort. The implementation of this method is provided below.

As part of posting this implementation, I decided to also briefly describe some of the other lookup method options available to an AX developer, turning this post into a small tutorial on lookup methods.

1. The approach used most in the application is the implicit lookup based on table fields. As you can see from the control properties, DataSource and DataField specify which table field the control is based on. In my example, it is based on SalesTable.CustAccount, which uses the ExtendedDataType CustAccount, which has a relation to CustTable.AccountNum. Therefore, any time you add this field to a form, it will automatically provide a lookup button to select one of the customers.

Relations being specified on EDTs is at times confusing to people, who are used to seeing the relations between tables on the tables themselves. Technically speaking, such definitions on EDTs are incorrect. But no worries, AX supports the “correct” scenario out of the box as well. The relation to CustTable could have been specified on the table just as well.

2. 2nd most used approach is to specify the ExtendedDataType on the control directly. This is used to allow the user to specify a value to filter the records on directly from the form, for example (without the need to go to Extended Query form). It is also indirectly used on all RunBase dialogs in the system (when creating a DialogField, we specify the EDT to be used, which is transfered to the ExtendedDataType property on the corresponding control). In the tutorial, it is again the CustAccount EDT that is specified in the properties.

3. The 2 above examples both used metadata to define what lookup is to be displayed. The 3rd most used approach is relying on SysTableLookup class and builds the lookup at runtime. The code is relatively straightforward, and is described in more detail on MSDN. Using this approach, the developer can specify the query to filter the data being displayed in the lookup form. Note, that SysTableLookup only allows to have data from one table in the lookup form (+ display methods on this table). SysMultiTableLookup is an extension I have created a while ago, that adds this and other useful functionality to SysTableLookup class.

4. The remaining 2 standard approaches are rarely used in the application. Lookup based on ExtendedDataType is very similar to approach described under #2, but is executed from code at runtime. This way, you can change the lookup data displayed dynamically, based on some conditions (For example, on Ledger Journal lines, the offset account lookup shows vendors, customers, banks, etc. based on the offset account type specified on the line).

5. BaseEnum values are usually represented as a ComboBox (and, in special cases, CheckBox or RadioButton) control on forms in Dynamics AX. The lookup is provided by the kernel automatically for this type of control. But, sometimes, it is required to show the values of an enumeration in a string control – most common scenario is providing filter capabilities based on enums, where multiple values can be specified at once, similar to extended query form filters. In fact, this approach is actually used on SysQueryForm for enum fields. As you can see from the code, the lookup call is also very simple in this case.

6. This approach does not currently exist in the application, and the goals for its implementation were already described above. The lookup method code in this case looks rather straightforward, here it is:

public void lookup()
{
    Counter yearCount;
    List    valueList = new List(Types::String);

    for (yearCount = 0; yearCount < 5; yearCount++)
    {
        valueList.addEnd(strFmt("Year %1", year(SystemDateGet()) - yearCount));
    }

    SysLookup::lookupList(this, valueList, "List of years");
}

As you can see, the data displayed in this lookup depends on current system state (date) and other data (yearCount). It uses the class List to hold the values to be displayed.

You can download the project (Compatible with Axapta 3.0 – Dynamics AX 2009) with the tutorial and custom list lookup implementation by following the link below:

Note: The xpo contains changes to SysLookup class/form (only usr layer has been exported, for your convenience). Be careful when importing those, and don’t import them into the production environment.

Be careful with join clauses when writing complex queries

Join clause evaluation is dependent on its position in the sql statement. In SQL Management Studio, if you try to specify the conditions incorrectly, you will receive the following error message:

In X++, you will not receive any compilation errors, nor any runtime errors. In complex scenarios with a lot of queries, this might go unnoticed, and will be extremely hard to weed out at a later stage, when data inconsistencies crawl in.

Take, as an example, the following job. At first glance, the 2 methods look exactly the same, and it seems as if they should work just fine. But in reality, the second method will return incorrect results, because inventSum.InventDimId will be treated as a constant (empty string as the default value) and not as a table field used in the same select statement.

QueryBuild classes have a major advantage in this situation, as you cannot easily add join clauses (links) unless adding to the child (joined) queryBuildDataSource.

static void JoinClauseWarningJob(Args _args)
{
    #define.ItemId("ESB-005")
   
    void testCorrectJoin()
    {
        InventTable inventTable;
        InventSum   inventSum;
        InventDim   inventDim;
   
        select inventTable
            where inventTable.ItemId == #ItemId
            join inventSum
            where inventSum.ItemId == inventTable.ItemId
            join inventDim
            where inventDim.inventDimId == inventSum.InventDimId;

        info(strfmt("Correct join where clause: %1", inventSum.AvailPhysical));
    }

    void testIncorrectJoin()
    {
        InventTable inventTable;
        InventSum   inventSum;
        InventDim   inventDim;

        select inventTable
            where inventTable.ItemId == #ItemId
            join inventDim
            where inventDim.inventDimId == inventSum.InventDimId
            join inventSum
            where inventSum.ItemId == inventTable.ItemId;

        info(strfmt("Incorrect join where clause: %1", inventSum.AvailPhysical));
    }
    // Actually execute the code
    testCorrectJoin();
    testIncorrectJoin();
}

So, the suggestion is simple: Use QueryBuild classes (or AOT queries) whenever possible, and pay attention to the order of tables and join clauses in the select statements that you write.

Microsoft Dynamics Convergence 2009 New Orleans

Well, I cannot start with any statement other than a quote by Steve Ballmer as of today. Of course, most of you have already read this one place or the other:

In light of the further deterioration of global economic conditions, Microsoft announced additional steps to manage costs, including the reduction of headcount-related expenses, vendors and contingent staff, facilities, capital expenditures and marketing. As part of this plan, Microsoft will eliminate up to 5,000 jobs in R&D, marketing, sales, finance, legal, HR, and IT over the next 18 months, including 1,400 jobs today. These initiatives will reduce the company’s annual operating expense run rate by approximately $1.5 billion and reduce fiscal year 2009 capital expenditures by $700 million. ...

Nevertheless, Microsoft Dynamics product line continues to grow, expand the feature set and customer base, and I am very confident it will have great success on the market in the future as well, even in the largest economy recession in many years.

Therefore, I would like to invite all of you to Convergence 2009, taking place March 10-13 in New Orleans, Louisiana, USA.
Microsoft Dynamics Convergence is one of the most exciting events in our business. It is an opportunity to listen to Microsoft plans for Microsoft Dynamics and related products, learn about all the possibilities your company can gain with these products, as well as meet with other business decision makers, Microsoft employees, and industry experts to gain knowledge, increase their understanding of Microsoft Dynamics business management solutions, as well as get some business insights how to thrive in the challenging economy.

Here are some links to get you started:

The session catalog (containing 225 sessions) is now live on the Convergence marketing site.

The Complete Guide to Convergence for Partners (PartnerSource access is required to access this link) is a valuable planning resource on PartnerSource. There are tips, tools and templates to help partners create the most worthwhile Convergence experience for their company and their customers’ companies.

I hope that many of you will be able to attend, and have a fun time there.

Supply Chain Management in Dynamics AX - a new Microsoft blog

For quite some time now, the MDCC (Microsoft Development Center Copenhagen) team has been nurturing the idea of launching our own blog about the Supply Chain Management area in Microsoft Dynamics AX.

And yesterday, it finally happened!
We posted the first article about Quality Management upgrade process for TQM solution (by Fullscope) users.


http://blogs.msdn.com/DynamicsAxSCM

I recommend all of you to add this link to your favorites, as we plan to post on a regular basis, and about things important to all of you. That said, I would like to invite all of you to post article ideas or questions related to Microsoft Dynamics AX SCM modules as comments to this message or directly on the SCM blog. We, in turn, will try to cover those in our blog posts.

Tool for protecting your Dynamics AX source code

"Is there a way to protect/hide my X++ code?"
People asked me this question a million times by now.

To most of them, I described 2 possible ways to go, that I know of:
- Do not share the source code, but instead provide the customers with an AOD file.
- Use a tool that would produce obfuscated code out of your modifications.

Both ways are not perfect, of course. If you provide the code as an AOD file, you need to be sure that the customer does not have X++ license. If you provide obfuscated code, the customer developers (if they are present) will have a hard time interacting with your code. And, eventually, both ways leave the customer at a disadvantage. As soon as the vendor is not there to support (provide fixes in terms of an updated AOD file or XPO with obfuscated code) them for whatever reason, the customer has no way of supporting the add-on solution on their own.

But this post is not about the cons of trying to protect your code.
It's about the possibilities. Today, finally, I can support my previous suggestion #2 with an actual link to such a tool: Next Innovation Code Scrambler Homepage

Here is a screenshot of how the code looks after Code Scrambler is finished with it:


Note, that the tool is not free, and I have not actually tried it out.
But there is a sample project showing the resulting scrambled code. You can download it for a first impression, and contact the company if you are interested in the tool.

Another note - this is not an advertisement, I am not in any way related to Next Innovation Company.