Join first line in AX 2012

You sometimes need to join only one related record for each parent record, for example one order line for each order header. I’m always suspicious of such requirements, because it often means that users/analysts didn’t realize that there may be more than one record, or they didn’t think enough of identifying the one record they actually need. Nevertheless there are valid scenarios when showing only the first or the last record is exactly what’s required.

If you wrote it in T-SQL, you could use a subquery, but how to achieve the same in AX? If you read my blog post about Subqueries in views, you already know that computed columns can be for building subqueries. Let’s apply the same approach to this problem.

In my example, I’ll create a form showing information from sales lines and from the last shipment for each sales line. It’s based on a real implementation, nevertheless it’s simplified so we don’t have to deal with too complex computed column or form. (Also please don’t forget that computed columns are not supported in AX 2009 and older versions).

View

First of all, create a view and call it SalesLineLastShipmentView. Add SalesLine datasource and drag InventTransId field from the datasource to the Fields node.

ViewWithInventTransId

InventTransId is the primary key of the SalesLine table and we’ll later use it for a join with SalesLine. The view will also contain ShipmentId (the primary key of WMSShipment table), which will be used for a join as well. To get the ID of the last shipment, first create a method on the view and put the following code there:

public static server str lastShipmentId()
{
    SysDictTable orderTransDt = new SysDictTable(tableNum(WMSOrderTrans));
 
    SysDictTable productCategoryDt = new SysDictTable(tableNum(EcoResProductCategory));
    DictView dv = new DictView(tableNum(SalesLineLastShipmentView));
 
    str s = strFmt('SELECT TOP 1 %1 FROM %2 WHERE %2.%3 = %4 ORDER BY %5 DESC',
        orderTransDt.fieldName(fieldNum(WMSOrderTrans, ShipmentId), DbBackend::Sql),
        orderTransDt.name(DbBackend::Sql),
        orderTransDt.fieldName(fieldNum(WMSOrderTrans, InventTransId), DbBackend::Sql),
        dv.computedColumnString(tableStr(SalesLine), fieldStr(SalesLine, InventTransId), FieldNameGenerationMode::WhereClause),
        orderTransDt.fieldName(fieldNum(WMSOrderTrans, DlvDate), DbBackend::Sql));
 
    return strFmt('ISNULL((%1), \'\')', s);
}

If it’s not clear what it does, wait a moment until we look at the resulting SQL query; it will be much more readable.

Now right-click the Fields node of the view and choose New > String Computed Column. Open properties of the column and set them as follows:

  • Name = ShipmentId
  • ExtendedDataType = WMSShipmentId
  • ViewMethod = lastShipmentId

Properties

Save the view, open it in table browser if you have some shipments in your system, you should see them mapped to InventTransIds.

ViewInTableBrowser

If you’re curious, open SQL Server Management Studio, find views in your AX database, right-click SalesLineLastShipmentView view and choose Design. It opens a view designer and you can find the actual SQL query there. This extremely useful especially if your view doesn’t work as expected and you want to see how exactly was translated to SQL.

This is the definition of our view:

SELECT INVENTTRANSID, DATAAREAID, PARTITION, RECID, CAST(ISNULL
	((SELECT TOP (1) SHIPMENTID
		 FROM dbo.WMSORDERTRANS
		 WHERE (INVENTTRANSID = T1.INVENTTRANSID)
		 ORDER BY DLVDATE DESC), '') AS NVARCHAR(10)) AS SHIPMENTID
FROM dbo.SALESLINE AS T1

You should recognize the subquery defined in our view method. It finds the first only (TOP (1)) WMSOrderTrans record for each SalesLine and return the ShipmentId. If none is find, it returns an empty string instead of NULL (because AX doesn’t expect NULL values in this case). That’s handled by ISNULL() function.

Note that you should always use ORDER BY if you look for first or last records, because otherwise the order is not defined and a different row may be returned every time you execute the query.

Form

Now we have a mapping between InventTransId and ShipmentId and because we want to see more fields in a form, we have to join tables to our view and build a form showing the fields.

Create a new form and add three datasources:

  • SalesLine (the root datasource)
  • SalesLineLastShipmentView (inner-joined to SalesLine)
  • WMSShipment (joined SalesLineLastShipmentView with outer join, because some sales lines have no shipment at all)

Because we don’t have any relation between SalesLine and SalesLineLastShipmentView, we have to add it in code. Override init() method of SalesLineLastShipmentView datasource and add the following code below super():

this.queryBuildDataSource().addLink(fieldNum(SalesLine, InventTransId),
                                    fieldNum(SalesLineLastShipmentView, InventTransId));

We don’t need the same procedure for WMSShipment, because it’s already handled by the table relation on WMSShipmentID data type.

The last thing is to create a form design. To keep is simple, create a grid and drag some fields there from SalesLine and WMSShipment.

FormWithData

Summary

Views are powerful even without computed columns. For example, if you simply wanted the highest shipment ID for each line, you could build a view like this (and join it with SalesLine and WMSShipment tables):

SELECT InventTransId, MAX(ShipmentId) AS ShipmentId FROM WMSOrderTrans
    GROUP BY InventTransId

Nevertheless computed columns add another dimension to views and allow you to use many more features of T-SQL. This ultimately allows you to implement business requirements that would be difficult to achieve by other means. Unfortunately the syntax for defining computed is far from perfect – it’s not easy to write nor read and you get little compile-time control. Here definitely is room for improvement. But this is definitely no argument for giving up all the power that computed columns offer.

Download

Zipped .xpo with the view and the form (built in AX 2012 R3

Index for delete actions

When using delete actions in Dynamics AX, don’t forget that AX will have to look into database to know if there is any related record. And if there is no index, the performance can be really bad.

I’ve just run into such a case, when deleting a customer executed a query running for six seconds. SQL tracing in AX showed me the following query and call stack:

SELECT T1.RECID FROM SOME_CUSTOM_TABLE T1
WHERE (((PARTITION=?) AND (DATAAREAID=?)) AND (CUSTACCOUNT=?))
(C)\Classes\xRecord\doValidateDelete
(C)\Classes\xRecord\validateDelete
(C)\Data Dictionary\Tables\CustTable\Methods\validateDelete - line 8
(C)\Classes\FormDataSource\validateDelete
(C)\Classes\FormDataSource\delete
(C)\Forms\CustTable\Data Sources\CustTable\Methods\delete - line 8

Although CustTable has overridden validateDelete(), the code at line 8 is just a call of super(). Therefore it’s all handled by AX kernel and the obvious explanation is a delete action. As expected, there was a delete action from CustTable, a relation on AccountNum and no corresponding index on the custom table.

Every time you create a delete action, think about how AX will look for the record and whether an index isn’t needed.

Permissions for Configuration Manager

When trying the new Configuration Manager for AX 2012, I got an error when exporting a stored configuration. The message in LCS said only that an unknown error occurred, which obviously wasn’t very helpful. Fortunately I checked the event log on my server with AX and immediately found the problem.

There was a log entry from source “LcsSystemDiagnostics” saying “Access denied to method create in class DMFDefinitionGroupService”. Of course, I forgot to set permissions for service operations for the system diagnostics service account.

That we can easily find the cause of such issues is a very good news. Nicely done.

Getting record from QueryRun in C#

This week I wrote some C# code using .NET Interop to X++ and I needed to get records from a QueryRun object. You surely know how to do it in AX (by calling QueryRun.get()), nevertheless I wanted to do it in a slightly better way. My method is rather short, but it may be difficult to grasp, if you’re not too familiar with C#. It utilizes several great features that don’t exist in X++.

I had several requirements.

I wanted to call the method in a natural way, such as queryRun.GetTable(…), therefore I built it as an extension method.

I wanted a descriptive exception if the table doesn’t exist in the query.

I wanted to get the right type, such as LogisticsPostalAddress instead of Common (all these are .NET proxy classes for X++ objects). It means that the method must be generic and returning the generic type. I also constrained the generic type only to classes extending Common (T GetTable() where T: Common).

Unfortunately queryRun.get() returns an instance of the Common class, not instances of particular tables. Fortunately it’s possible to create the right table proxy by passing the Common object to the constructor (e.g. LogisticsPostalAddress(common)). But we can’t use parametrized constructors of generic types, therefore I ask the caller to provide a method that constructs the table proxy from Common.

I also wanted to avoid hard-coded table names and I decided to simply take the name of the generic type.

This is my implementation:

public static T GetTable(this QueryRun queryRun, Func<Common, T> creator, int occurence = 1)
    where T : Common
{
    string tableName = typeof(T).Name;
    int tableId = Global.tableName2Id(tableName);
    Common common;
 
    try
    {
        common = queryRun.get(tableId, occurence);
    }
    catch (XppException)
    {
        throw new InvalidOperationException(String.Format("The query does not contain {0}", tableName));
    }
 
    return creator(common);
}

And this is how I use it, passing a lambda function as the argument:

var address = queryRun.GetTable(c => new LogisticsPostalAddress(c));

The C# compiler is smart enough to infer the return type and the type of the address variable just from the type returned by my lambda function.

Writing code like this is real fun!

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.