Local TF Build with VSO – Intro

If you want to use a proper source control system with Dynamics AX, you have in fact only one option: Team Foundation Server (TFS). MorphX VCS can’t cover all versioning needs (because it works only for resources in AOT) and it lacks many features, and Visual SourceSafe is not supported anymore.

Because installation and maintenance of Team Foundation Server requires some effort and resources, using the cloud-hosted version, Visual Studio Online, makes good sense for many teams. It can be set up in minutes and you don’t have to care about maintenance, updates and so on.

VSO has some limitations, though – they may represent no problem for you, you may find some workarounds or maybe you depend on some feature so much that you have to use on-premise TFS after all.

One example is the build service. VSO offers a hosted build service, so you don’t need your own build server. Nevertheless it has several restrictions and you would quickly find that you can’t use it for builds of Dynamics AX, because you have to install some additional software, the build will likely run for more than one hour and so on.

Fortunately you don’t have give up using VSO. The solution is to install your own build server and merely connect it to VSO. The setup is almost the same as for on-premise TFS, therefore if you’re familiar with builds with TFS, you shouldn’t have many problems with VSO either.

There is one potential issue to take into account – if you use Team Foundation Build, you obviously need a licence for TFS. Namely you need a TFS server license and everybody “whose actions cause builds to to run” needs a TFS CAL. A great news is that the problem with CALs will disappear in a few months, because VSO licenses will also grant TFS CALs. Also don’t forget that MSDN subscription includes TFS licenses, therefore you may already have all licenses you need.

I’ll show how to set up a build server in a subsequent blog post.

VSO licensing changes: more for less

The latest news about licensing of Visual Studio Online and Team Foundation Server is truly exciting.

The first great news is that VSO licenses will be much cheaper, decreasing from $20/user/month (for the basic tier) to $8-$2, depending on the number of licenses. And you still have five users for free, free stakeholder licenses and MSDN subscribers are still covered by the subscription.

But the main thing is that VSO license will also grant client access license (CAL) for TFS. The dual licensing of VSO and TFS is currently a bit annoying, especially if you want to combine VSO and TFS, such as if you’re using VSO but you need a local TFS build server (which is, incidentally, the topic of my next blog post). Notice that you can use this license model even if you’re using on-premise TFS only – instead of buying individual CALs for $499, you can start paying only for the time when you need them and change the number of license exactly as you need. And it will be much cheaper.

If you consider using TFS but you have problems with the cost of licenses, I think this solves the problem. You don’t have to invest into CALs up front; you just have to find a few bucks every month.

The only bad news is that this doesn’t happen immediately. Some changes will take effect on 1 September, some a bit later.

If interested, you can find more details in Included CALs and Tiered Pricing on Brian Harry’s blog.

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.