The value 1 is not found in the map

We were getting the error, “The value 1 is not found in the map”, in one of our rather complex processes in AX 2012. It occurred only sometimes, which made debugging difficult, but finally I managed to track it down. The cause lies in something that many people use, therefore it’s possible that you will, or already did, run into the same problem.

I found the issue in AX 2012 R2 CU7 and reproduce it also in AX 2012 R3.

The problem can be demonstrated by running the following piece of code. It creates a progress bar, waits for some time and calls incCount() to update the progress. The last call to incCount() throws the error.

SysOperationProgress progress;
#AviFiles
 
progress = SysOperationProgress::construct();
progress.setAnimation(#AviUpdate);
progress.setCaption("Some caption");
 
sleep((progress.updateInterval() + 1) * 1000);
 
// The progress form gets initialized here.
progress.incCount();
 
// Wait for more than 10 seconds
sleep(11000);
 
progress.incCount();

You see that there is nothing suspicious going on – I just simulate a process that runs for some time and occasionally updates the progress. I’ll explain the important of waiting a little bit later.

If you debug the code, you’ll find that the error is thrown at line 33 of SysOperationProgressBase.updateTime(). It’s this line:

r3 = totalValue.lookup(1)-progress;

It should be clear what happens in this code – it tries to get the value for the key 1 from totalValue map but it doesn’t exist (→ The value 1 is not found in the map).

Why the map doesn’t contain the key? It occurs that the initialization is done in setTotal(), which isn’t called in the code above. Therefore if you always call setTotal() when creating SysOperationProgress, you won’t ever get into this problem. Nevertheless you have to do it even if you actually don’t know the total – then you have to set it to zero:

progress.setTotal(0);

It’s also worth noting that setTotal() gets called if you create the instance by SysOperationProgress::newGeneral(), which developers often do anyway.

Nevertheless why the error occurs only sometimes? The SysOperationProgressBase class usually checks whether the key exists in the map before trying to use it. The exception is – obviously – in updateTime() and several conditions must be met before this piece of code gets called. The most tricky one is this:

if (time-laptime > #tenSeconds)

It must be ten seconds since the last update and if it took less, this code doesn’t get executed and no error is thrown. Therefore we got this error as a side effect of something slowing the system down. Now it’s also clear why my script waits for more than ten seconds before calling incCount() again.

Always calling setTotal() is a relatively easy workaround, but it would be better if SysOperationProgress handled the problem in a better way. It should either accept that the value 1 doesn’t have to exist in totalValue (and check if it’s there before using it) or it should throw an error to inform the developer that he’s trying to use an object that hasn’t been properly initialized.

This blog post doesn’t suggest that The value 1 is not found in the map is always caused by a progress bar – it may refer to any other map. Nevertheless it’s one of things that you may want to check.

Connection string for LINQ to SQL

In my article about connecting to external databases with the help of LINQ to SQL, I already mentioned how to parametrize which database to use, because you’ll likely use different databases for development, test and live environments.

I wrote: In real implementations, I usually store DB server and DB name in AX database, create a connection string from them (with the help of a connection string builder) and pass the connection string to data context’s constructor.

Let’s look at what it means in practice.

First of all, let me remind you the .NET class:

public class Data
{
    public void Import()
    {
        TestDBDataContext db = new TestDBDataContext("); 
        …
    }
}

Here I create the data context without any parameters, therefore it connects to the database that I used when designing the library in Visual Studio. It’s also possible to change it in the designer.

A common way for specifying connection details is using connection strings like this:

Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=True

This approach is supported even by LINQ to SQL and you could simply pass a connection string to the constructor of a data context class:

new TestDBDataContext("Data Source=.;Initial Catalog=TestDB;Integrated Security=True");

I could construct such a string by myself, but I’m not interested in details of the syntax, in concatenating strings and so on. A connection builder will deal with it for me. Therefore I’ll add a field to the Data class holding the connection builder instance:

SqlConnectionStringBuilder connStringBuilder;

I also have to add

using System.Data.SqlClient;

on the top of the file, because that’s the namespace containing SqlConnectionStringBuilder class.

I have to decide what I want to include in my connection string and usually a database server name and a database name are enough, therefore these two values will be my parameters. I add a constructor to the Data class that accepts these parameters, creates a connection string builder and set its properties:

public Data(string dbServer, string dbName)
{
    //TODO: parameter validation
 
    connStringBuilder = new SqlConnectionStringBuilder();
    connStringBuilder.DataSource = dbServer;
    connStringBuilder.InitialCatalog = dbName;
    connStringBuilder.IntegratedSecurity = true;
}

Now we can get the connection string at any time from ConnectionString property of the builder, therefore let’s pass it to the data context:

new TestDBDataContext(connStringBuilder.ConnectionString);

Put all together, the class now looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
 
namespace ExtSystem
{
    public class Data
    {
        SqlConnectionStringBuilder connStringBuilder;
 
        public Data(string dbServer, string dbName)
        {
            //TODO: parameter validation
 
            connStringBuilder = new SqlConnectionStringBuilder();
            connStringBuilder.DataSource = dbServer;
            connStringBuilder.InitialCatalog = dbName;
            connStringBuilder.IntegratedSecurity = true;
        }
 
        public void Import()
        {
            TestDBDataContext db = new TestDBDataContext(connStringBuilder.ConnectionString);}
    }
}

Rebuild the solution and open Dynamics AX (restart the client if it was running).

Our test job in AX contained the following code:

new ExtSystem.Data().Import();

It doesn’t compile anymore, because we have to specify the arguments (database server + database name). The following implementation connects to exactly the same database as before.

new ExtSystem.Data('.', 'TestDB').Import();

I can easily prove that the parameters are utilized by specifying an invalid database name such as WrongDB. I’ll get an error saying “Cannot open database “WrongDB” requested by the login”.

Now the only remaining thing is to create fields in AX database for the parameters and use them when creating the Data class.

Also note that if I wanted, I could select the data from AX database directly in the .NET class (by creating a proxy to the table etc.). But I think that it would be an unnecessary coupling between the library and the table. Using parameters is more flexible and it also make testing easier.

Connection to external database

You sometimes need to connect from Dynamics AX to another database and read or write data there. The usual approach uses OdbcConnection class, as described on MSDN in How to: Connect to an External Database from X++ Code [AX 2012]. Although it definitely is a possible solution (I used it already in AX 3.0), it has so many problems that I almost never use it these days.

My intention is to show one of possible alternatives that solves many of these issues. Although the core idea can be used in AX from version 4.0, this blog post uses AX 2012 and depends on several features that don’t exist in older version.

First of all, look at how you’re supposed to construct the query for OdbcConnection:

sql = "SELECT * FROM MYTABLE WHERE FIELD = "
            + criteria
            + " ORDER BY FIELD1, FIELD2 ASC ;";

and how to get results:

print resultSet.getString(1);
print resultSet.getString(3);

(This is taken from the sample code on MSDN.)

You have to define the query as a simple string, therefore you have to write SQL code by yourself, you won’t get any IntelliSense, compile-type control or anything like that. You have to execute your SQL against the target database to find errors such as misspelled field names.

You also depend on column indexes when accessing data, which becomes cumbersome very quickly and it’s easy to break. For example, notice that the query above selects all fields. What will happen if I add another field, e.g. the query starts returning ANewField, Field1 and Field2 instead of Field1 and Field2? Column indexes now refers to different fields and it again won’t be found until actually running the code.

It’s simply unpleasant for development and horrible for maintenance.

Wouldn’t it be better if we could simply refer to field as if we do in normal X++ development, get compile-type control and so on? Good news – we can!

Ssms_Table

Before we actually start, let me show you the table that will simulate my external data. It has four fields: ID and Name that I’m going to import to AX, Status used for tracking what needs to be processed and UpdateTime storing the data and time of the update.

It can be created by the following script:

CREATE TABLE [DataForAX](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](30) NOT NULL,
    [Status] [tinyint] NOT NULL,
    [UpdateTime] [datetime] NULL,
    CONSTRAINT [PK_DataForAX] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )
)

You can also put some test data there:

INSERT INTO DataForAX
VALUES 	(N'Croup', 0, null),
	(N'Vandemar', 0, null)

Note that it’s important for the table to have a primary key, otherwise we wouldn’t be able to update the data.

AxObjects

Let’s also prepare some objects in AX. We’ll need a table storing the data and because the Status field is an integer representing various states, we can very naturally model it as an X++ enum.

Status field values:

  • 0 = Ready
  • 1 = Imported
  • 2 = Failed

Now start Visual Studio, because most of our work will happen there. Create a new C# class library, add it to AOT and drag the table and the enum from Application Explorer to your project. We’ll need them later.

Open Server Explorer (View > Server Explorer) and create a connection to your database. I’m using a SQL Server database on the current machine, but you can use ODBC, connect to remote servers or anything you need.

ServerExplorerAddConnection

ChooseDataSource

AddConnection

Now you should see your tables directly in Server Explorer in Visual Studio:

ServerExplorerTable

Right-click your project in Solution Explorer, choose Add > New Item… and find LINQ to SQL Classes. Set the name (TestDB) and click Add.

AddLinqClass

A database diagram will be added to your project and its design surface will open. Drag the table from Server Explorer to the design surface.

DesignSurface

It will not only render the table in the designer, it will also generate a class representing the table (and a few other things needed for LINQ to SQL). If you want, you can open TestDB.designer.cs and verify that there is a class for DataForAX table:

public partial class DataForAX : INotifyPropertyChanging, INotifyPropertyChanged
{
    private int _ID;
    private string _Name;
    private byte _Status;
    private System.Nullable _UpdateTime;
 
    …
}

It’s a more advanced topic, but notice that it’s a partial class. It allows you to extend the class without meddling with code generated by the designer. Extending the class is useful in many scenarios, but we don’t need it today.

There is one thing we should change, though – the type of the Status field is byte while would like to use our enum from AX. Go to the designer, open properties of the Status field and type the name of the enum (ExtRecordStatus) into the Type field.

StatusFieldProperties

MapToEnum

It might look as nothing special, but I think it’s pretty cool. We’re mapping a field from an external database to an X++ enum (albeit through a proxy) and we’ll be able to use in LINQ queries, when assigning values to the field and so on.

Now add a new class to your project, call it Data, for example, make it public and add the Import() method as shown below:

public class Data
{
    public void Import()
    {
        TestDBDataContext db = new TestDBDataContext();
 
        var query = from d in db.DataForAXes
                    where d.Status == ExtRecordStatus.Ready
                    select d;
 
        foreach (DataForAX record in query)
        {
            ExtTable axTable = new ExtTable()
            {
                ID = record.ID,
                Name = record.Name
            };
 
            if (axTable.ValidateWrite())
            {
                axTable.Insert();
                record.Status = ExtRecordStatus.Imported;
            }
            else
            {
                record.Status = ExtRecordStatus.Failed;
            }
 
            record.UpdateTime = DateTime.UtcNow;
        }
 
        db.SubmitChanges();
    }
}

Let me explain it a little bit. Firstly we create a data context:

TestDBDataContext db = new TestDBDataContext();

It’s a class that defines where to connect, track changes and so on. In real implementations, I usually store DB server and DB name in AX database, create a connection string from them (with the help of a connection string builder) and pass the connection string to data context’s constructor.

Then we create the LINQ query.

var query = from d in db.DataForAXes
            where d.Status == ExtRecordStatus.Ready
            select d;

This simple example can’t show the full power of LINQ, but it’s obvious that we’re not building SQL by ourselves; we use strongly-typed objects instead. Notice also how we filter the data by the X++ enum.

The foreach loop fetches data from database and fill it to an instance of DataForAX class. Later we simply access fields by name – nothing like resultSet.getString(3) anymore.

This code:

ExtTable axTable = new ExtTable()
{
    ID = record.ID,
    Name = record.Name
};
 
if (axTable.ValidateWrite())
{
    axTable.Insert();
}

sets data to an AX table buffer (ExtTable is the table we created in AX at the very beginning) and calls its validateWrite() and insert() methods as usual. I typically pass records back into AX and process them there, but inserting them to AX database from here works equally well.

Then the code changes values of Status and UpdateTime fields. Finally db.SubmitChanges() writes changed data to the external database. We could also insert or delete records in the external database if needed.

That completes our library; the remaining step is calling it from AX. Open project properties and choose where to deploy the library. We’ll run it from a job for demonstration, therefore we have to deploy it to the client.

DeployToClient

Rebuild the project and restart your AX client, if it was running.

Create a job in AX and call the Import() method from there:

try
{
    new ExtSystem.Data().Import();
}
catch (Exception::CLRError)
{
    throw error(AifUtil::getClrErrorMessage());
}

That’s all what we need to do here, because reading the data, inserting them to AX database and updating the external database is already handled by the .NET library.

The whole application has just a few lines of code, doesn’t contain any strings with SQL commands, it doesn’t depend on the order of columns, the compiler checks whether we use data types correctly and so on.

It may require to learn a few new things, but I strongly believe that AX developers should look more at how things are done these days instead of sticking to old ways and low-level APIs such as the OdbcConnection class. Many great frameworks and language features waits for you to use them to increase your productivity and develop things hardly achievable in X++ – and AX 2012 made it easier than ever. It’s would be a pity not to benefit from all the progress done in recent years.

I couldn’t really discuss LINQ or the database designer while keeping a sensible length of this post. Fortunately you’ll find a plenty of resources about these things. LINQ to SQL also isn’t the only way – maybe ADO.NET would be more suitable for your particular project. Nevertheless I hope I managed to show you how to use these frameworks from AX – and that it’s not difficult at all.

AX management shell – multiple models

Even if you’re not familiar with PowerShell, you probably don’t have much trouble with using AX management cmdlets. You just call the right command, set some parameters and it’s done.

# Just for example
Export-AXModel -Model "MyModel" -File "c:\my.axmodel"

If you don’t know the parameters, you’ll simply use Get-Help or some of its aliases, such as man:

man Export-AXModel -Detailed

But if you don’t know PowerShell well, you may not know how, say, to work with multiple models at once. This is often useful, therefore it’s worth showing how to do it.

Before we look at any code, ensure yourself that you have a development environment such as PowerShell ISE or PowerGUI. These applications will help you saving your scripts, discovering commands and parameters, setting breakpoints, reviewing variables and many other things. No need to do everything directly in console!

But there is a problem – if you try to use AX cmdlets outside AX management shell, they won’t be recognized. Fortunately the fix is easy – add the following line to your script (including the dot at the beginning):

. 'C:\Program Files\Microsoft Dynamics AX\60\ManagementUtilities\Microsoft.Dynamics.ManagementUtilities.ps1'

You can also run it directly in console or even to add it to your profile, so it’s loaded automatically every time you run PowerShell.

Let’s warm up with a few simple commands (using PowerShell 3.0 syntax):

# Lists all AX modules - nothing new here
Get-AXModel
 
# Lists just model names and layers, to make things more readable
Get-AXModel | select Name, Layer
 
# Lists models in a specific layer
Get-AXModel | ? Layer -eq USR

The “| ” symbol is a pipe and it passes the output of one command to the input of another command. It would be great if could use it with AX cmdlets. e.g. for passing a list of models to Export-AXModel. Unfortunately it’s not supported and AX cmdlets also don’t accept arrays as arguments, therefore we have to call each command for a single model at a time. It’s not that bad as it might sound, because we can use a loop to sequentially execute a command for every element in an array.

# Let's set some parameters first 
$toDir = 'C:\Models'
$layer = 'USR'
 
# Export all models in a given layer to file
Get-AXModel | ? Layer -eq $layer | % { Export-AXModel -Model $_.Name -File "$toDir\$($_.Name).axmodel" }
 
# Uninstall all modules from a given layer
Get-AXModel | ? Layer -eq $layer | % { Uninstall-AXModel -Model $_.Name -NoPrompt }

It might look a little bit cryptic at first, but you see it’s rather simple and you shouldn’t have problems to apply the same approach in your own scripts. It’s much faster to write (or copy) something like this than typing, running and waiting for several individual commands.

Refactoring of financial reasons

If you’re not sure what “refactoring” means, note that it is a change of the structure of code without changing how the application behaves. It might sound useless, but it’s actually very important. You must realize that the quality of code doesn’t depend only on its runtime behavior – whether you can easily maintain and extend the code is equally important.

I tend to do refactoring and implementation of new features as two distinct activities, therefore I always know whether I’m trying to change the behavior or keep it the same, and I can test the code accordingly. If you’re lucky enough to have automated tests, they will help you to check whether your refactoring didn’t accidentally change the behavior.

I often spend more time refactoring existing code than implementing something new. That doesn’t mean that I’m wasting time. It often means that the change itself is a single line of code – as soon as the code is made easily extensible.

I work on improving and extending existing code bases very often, therefore I could show many typically problems and refactorings, nevertheless I obviously don’t want to discuss my clients’ code publicly. What pushed me to write this blog post was an interesting refactoring of a class from the standard AX application.

My goal was adding a new module to financial reasons in AX 2012:

Financial reasons

I thought it would be pretty easy, because it looks like a type of change that original developers could expect and prepare for. Unfortunately the implementation makes this kind of change quite difficult and error prone.

Let’s take one specific method as an example. This is validateDelete() method of ReasonFormTable class, which is called from Reasons form:

public boolean validateDelete(ReasonTable _reasonTable)
{
    boolean ret = true;
 
    switch(reasonCodeAccountType)
    {
        case ReasonCodeAccountTypeAll::FixedAssets:
 
            // Validation fails if any fields are checked except the fixed asset field.
            // <GEERU>
            if (_reasonTable.Bank || _reasonTable.Cust || _reasonTable.Ledger || _reasonTable.Vend || _reasonTable.rCash || _reasonTable.rAsset)
            // </GEERU>
            {
                ret = false;
            }
            break;
        case ReasonCodeAccountTypeAll::Bank:
 
            // Validation fails if any fields are checked except the bank field.
            // <GEERU>
            if (_reasonTable.Asset || _reasonTable.Cust || _reasonTable.Ledger || _reasonTable.Vend || _reasonTable.rCash || _reasonTable.rAsset)
            // </GEERU>
            {
                ret = false;
            }
            break;

I’m showing just first two cases, but there are eight in total, all with almost identical code. The duplicity itself tells me that the code has a couple of problems. If you want to add a new module, you’ll have to change all existing cases. You’ll have to change a lot of code in many places, which means many opportunities for errors. And it’s not very readable – it doesn’t express the intention (“check if the reason isn’t used in other modules”) very well and it would be difficult to spot the error if you incidentally used a wrong field somewhere.

You probably noticed that the developer implementing Russian functionality must have already modified every case. He didn’t refactor the solution to something better, he blindly followed the existing pattern. But it doesn’t mean that the problem remained exactly the same as before – it actually become much worse, because the number of modules to handle increased from five to seven.

Instead of changing a few dozen places in the class, the developer should have refactored the solution to be more extensible and then modified just a few places. That’s the approach that all developers should follow – you can start with a simple solution, but when it becomes causing problems, don’t wait and refactor it. It will help not only with the current task, but also with all later extensions and bug fixes.

Let’s look at my refactoring. First of all, I realized that I would need some mapping between modules and table fields. I used a similar switch as in the original code (although I had more options, such as using a map); the difference is that it’s now encapsulated in a separate method that doesn’t do anything else. It can be used from many places and if I add a new module, I’ll know where to go and simply add one more scenario there.

private FieldId typeToFieldId(ReasonCodeAccountTypeAll _type)
{
    switch (_type)
    {
        case ReasonCodeAccountTypeAll::FixedAssets:
            return fieldNum(ReasonTable, Asset);
 
        case ReasonCodeAccountTypeAll::Bank:
            return fieldNum(ReasonTable, Bank);
 
        case ReasonCodeAccountTypeAll::Cust:
            return fieldNum(ReasonTable, Cust);
 
        case ReasonCodeAccountTypeAll::Ledger:
            return fieldNum(ReasonTable, Ledger);
 
        case ReasonCodeAccountTypeAll::Vend:
            return fieldNum(ReasonTable, Vend);
 
        case ReasonCodeAccountTypeAll::RCash:
            return fieldNum(ReasonTable, rCash);
 
        case ReasonCodeAccountTypeAll::RAsset:
            return fieldNum(ReasonTable, rAsset);
 
        default:
            return 0;
    }
}

To be able to work with “all module fields except a specific one”, I need a list of all fields representing module check boxes. I could define it in code, but the application already contains such a definition – in a field group on the table. Therefore I can use reflection to read the list of fields from there (and I cache it to a member variable, to avoid running the same code too often).

private Set accountFields()
{
    DictFieldGroup fieldGroup;
    int            i;
 
    if (!accountFields)
    {
        accountFields = new Set(Types::Integer);
        fieldGroup = new DictFieldGroup(tableNum(ReasonTable), tableFieldGroupStr(ReasonTable, AccountType));
 
        for (i = 1; i <= fieldGroup.numberOfFields(); i++)
        {
            accountFields.add(fieldGroup.field(i));
        }
    }
 
    return accountFields;
}

Now I have everything what I need to iterate over module fields and check their values:

public boolean isUsedForAnotherModule(ReasonTable _reasonTable)
{
    FieldId       fieldToCheck;
    FieldId       acceptedField = this.typeToFieldId(reasonCodeAccountType);
    SetEnumerator enumerator    = this.accountFields().getEnumerator();
 
    while (enumerator.moveNext())
    {
        fieldToCheck = enumerator.current();
 
        if (fieldToCheck != acceptedField && _reasonTable.(fieldToCheck) == NoYes::Yes)
        {
            return true;
        }
    }
 
    return false;
}

It should be easy to follow. The acceptedField variable contains the field used for the current module (I used my new method for mapping from module type to the field ID). Then it enumerates module fields and check if any field (excluding the accepted one) has the value equal to NoYes::Yes.

Now the part of validateDelete() that checks for other modules can be reduce to a single method call. This is our new implementation:

public boolean validateDelete(ReasonTable _reasonTable)
{
    boolean ret = !this.isUsedForAnotherModule(_reasonTable);
 
    // Do additional check for Letter of Guarantee fields
    …
 
    return ret;
}

Isn’t it better? It’s easy to read, because you simply read the name of the method and don’t have to deal with dozen lines of code. And if you need to add a new module, you don’t have to change validateDelete() in any way.

I almost hear some developers refusing to start such a refactoring, saying that it takes too much time or it would be too much code. I strongly disagree. It might have taken more time than the mindless copy & paste, but it was still just a few minutes – that’s really nothing to worry about. And it will save time whenever we need to extend or fix the code.

Regarding the length of the code – the solution is already significantly shorter than the original method and the real saving would start only from now. For example, look at datasourceInitValue():

public void datasourceInitValue(ReasonTable _reasonTable)
{
    switch(reasonCodeAccountType)
    {
        case ReasonCodeAccountTypeAll::FixedAssets:
            // default the asset account type
            _reasonTable.Asset = true;
            break;
        case ReasonCodeAccountTypeAll::Bank:
            // default the bank account type
            _reasonTable.Bank = true;
            break;
 
    // … and the same for other modules …
}

You see that there is another instance of the switch statement mapping account types to fields. Because we already extracted the logic to a separate method, we can reuse it and replace the whole method with this:

public void datasourceInitValue(ReasonTable _reasonTable)
{
    if (reasonCodeAccountType != ReasonCodeAccountTypeAll::AllValues)
    {
        _reasonTable.(this.typeToFieldId(reasonCodeAccountType)) = true;
    }
}

Notice that I threw away almost all code of datasourceInitValue() and that’s nothing unusual when refactoring not-so-good code. I often wonder how much time it took to develop several hundred lines of code that I’m removing, because they aren’t actually needed. It’s not refactoring what leads into a lot of code – it’s the lack of refactoring. And having more code than actually needed is bad, because it means more code to read, more code to maintain, more places where bugs can hide and so on.

I didn’t invent refactoring, DRY, SOLID or anything like that; it’s all known for years and you can find many books about these things. But I see that many developers are still not aware of these principles and techniques, therefore it’s important to repeat them. Hopefully seeing real code will help some developers who are willing to improve themselves but who aren’t fans of abstract definitions.