Data Upgrade from Microsoft Dynamics AX2012 R2 / AX2012 R3 to Dynamics 365 for Finance and Operations

Upgrading Microsoft Dynamics AX2012 to Dynamics 365 Finance and Operations brings the digital transformation in most companies by giving them the ability to use significant new features and capabilities.

 

Recently, we have added another successfully completed data upgrade project for one of our clients in our portfolio. If you are looking for some help while upgrading from AX2012 R2 or Ax 2012 R3 to Dynamics 365 then you are at the right place.

 

This process contains several phases:

 

• Preparing the database in the AX2012 environment
• Installing a deployable package on the D365 development environment
• Migrating upgraded database from D365 one-box to sandbox environment

 

 

 

AX2012 Procedure

 

The process starts in the AX 2012 environment where you have to prepare the ax2012 database for upgrading to Dynamics 365. Microsoft has put together a pre-upgrade checklist that has to be installed in Dynamics AX 2012. The checklist ensures you have updated information in your database for the AX 2012 to D365 upgrade.

 

• If upgrading from AX 2012 R3, install KB 4035163
• If upgrading from AX 2012 R2, install KB 4048614

 

After the installation of the hotfix, run the sysCheckList classes to prepare the database. After executing the process in the AX2012 environment we must make a backup from this database and transfer it to the D365 development environment. For transferring databases between environments, we will use Microsoft Azure Storage.

 

D365 Procedure

 

Most of the errors must be resolved in this process while installing the data upgrade deployable package.

 

The process on D365 development environment is following:

 

1. Restore and rename the database.
2. Install deployable package.
3. Made another back up from this database and restore it to prepare it for Tier2 environment.
4. Export a bacpac file for transferring

 

 

Restore the Database

 

After restoring the database on D365 environment we need to rename the names of the databases. Rename the original AxDB to AxDB_Orig and then rename the newly restored database to AxDB.

 

Install a Deployable Package

 

Download data upgrade deployable package from Shared asset library from Software deployable package, find the data upgrade package that corresponds to your upgrade.

 

If you’re upgrading from AX 2012, the package name starts with AX2012DataUpgrade-x-x-x where x-x-x is the application release. Select the package that corresponds to the release you are upgrading to. After downloading extract it and install it.

 

Make a Back-up to Prepare the Database for Tier 2

 

After the installation of the deployable package completes, we need to make back up from this database and restore it again in this environment. We will do this because we will delete some objects from it to prepare for the sandbox environment.

 

Do the following steps in SQL management studio against the newly imported database:

 

• Update sysglobalconfiguration table

 

• Drop procedure XU_DisableEnableNonClusteredIndexes and drop following users: axdbadmin, axdeployuser, axmrruntimeuser, axretaildatasyncuser, axretailruntimeuser

 

• remove NT users as these are not supported in Azure SQL Database

 

• remove any AX 2012 RTM model store procedures that still exist

 

• If you receive a message that you cannot delete users because they own a schema, then check which schema the user owns. Either change the ownership to another user (for example to do) or drop the schema if it does not contain any objects.

 

• drop all views in the current database because some refresh the tempDB, which is not a supported action in Azure SQL Databases

 

• Drop MaintainShipCarrierRole procedure because it contains a tempDB reference that is not supported in Azure SQL Database

 

• Export a bacpac file for transferring 

 

After all this, the last step on the development environment is to export a bacpac file that should be imported in a sandbox environment. Go to the following location and execute the following command for exporting the file

 

A Process on the D365 Sandbox Environment

 

After transferring the database, we need to do the following steps:

 

• Import the database;
• Create users and prepare the database;
• Rename the database;
• Full database synchronization.

 

Import the Database

 

Import the database into the sandbox environment.

 

Create Users on the Newly Imported Database

 

Create users that were dropped in the preparation procedure. The passwords for the users must be copied from the LCS from the environment where we are importing the database. After users are created, alter the database with the required configuration.

 

Rename the Database

 

Full Database Synchronization  

 

Run full database synchronization and after it completes successfully you are ready to use the environment.

 

If you are interested in optimizing/improving your business processes, making the experience while using the application a whole lot easier in the long run, having the data secured in the cloud at the same time, and in need for someone to upgrade your systems from Microsoft Dynamics AX to Dynamics 365, we are open for future collaboration.

 

Reach out to us here, and if you are interested in potential career opportunities, check out our careers page.

‘View Details’ Button Does Not Work on EDT Fields Used in Forms Displaying Records from Customized Tables

In previous versions of Microsoft Dynamics AX, table relationships could be defined in the Application object tree under the Relations node of either an EDT node or a specific table node.

 

Starting with Microsoft Dynamics AX 2012, the use of relations on EDT is obsolete and should be deleted in its future versions.

 

All table relations defined under an EDT should be migrated since they should be defined on tables only. If this is not done correctly, the ‘View details’ option can be unavailable for EDT fields used in forms displaying records from customized tables. This problem might occur even though the ReferenceTable property on the EDT is set to some table and this EDT matches the type of the primary index from the corresponding reference table.

 

Our team came to a different possible solution to this problem, which is explained in the text below:

 

Firstly, you need to create a self-relation in the table whose primary index matches the EDT.

 

 

Another option is to create a hidden menu item button, which opens the main form of the data from the table whose primary index matches the given EDT.

 

Next, on the grid displaying the records from this table set this button as a default action. ‘View button’ option will appear on right-clicking the first field in the grid.

 

It is important to note that this is not the best solution since this works only on forms containing a grid with the corresponding table set as the main data source.

 

If you want to use this field from the same table in other forms, the ‘View details’ button won’t be shown again, unless the previously mentioned conditions are fulfilled. This approach is used in the Sales agreements list page.

 

 

Creating View Out of an AOT Query Object

The purpose of today’s featured article is to show how to create automatically view out of a query object in AOT, based on the selected query. This is a very helpful enhancement that provides easy testing with queries.

 

Iteration through AOT elements is done by using the TreeNode class which has very useful methods for finding a specific node, creating and compiling subnodes, retrieving their children and so on.

 

Creating a view and setting some properties through X++ code is showed below. The view is named as the query and by adding the ‘View’ suffix.

 

 

But there is one problem when it comes to a query that has many data-sources and even some of them have nested data-sources. In order to solve this ‘inception’ problem, we need to iterate through all data-sources and add every field to the view.

 

The following code example shows how to iterate recursively through the data-sources and the addFields function adds the fields to the view.

 

 

 

At the end, the newly created view should be compiled and a data dictionary synchronization is recommended:

 

treeNodeView.AOTcompile();
SqlDataDictionary::synchronize();

How to Resolve Conflicts After Installing a Model in AX 2012?

As promised in our previous post, in this blog post we will describe the process of resolving conflicts occurred after installing a model.

 

Conflicts may appear after importing a model, if some of the elements included in the model are already in another model on the same layer. In that case, you can use the push option when importing the model:

 

(Windows PowerShell) –Conflict Push
(AxUtill) /conflict:push

 

The conflicting elements will be put into a new conflict model that is located in the patch layer for the current layer in the AOS. This new model will have a name that indicates it contains resources that have caused a conflict.

 

You can find the conflicting elements in this model and use the Compare tool to see the differences between the element in the path layer and the element in the main layer. Using that information and available actions from the Comparison form you can merge changes from the conflict model into an existing layer.

 

After you have resolved all of the conflicts, consider removing the conflict model from the patch layer. That way, somebody looking at the system will not assume that there are model conflicts that have to be resolved. If you are leaving a resource in the patch layer, move the resource out of the conflict model into another model in the patch layer. Then delete the conflict model.

 

Another scenario, where code conflicts may occur, is installing Cumulative updates or hotfixes (patch models) if the changes of affected elements may be over layered by customizations in higher layers.

 

Let’s assume that you are currently working on CUS layer. You have to install patch provided by ISV and this patch model will be installed in the ISP layer. You may potentially have changes on elements in ISP that are over layered/modified in CUS layer and code conflicts will be occurred between these layers.

 

For resolving these conflicts, Detect code upgrade conflicts tool can be used from Tools – > Code upgrade

 

 

NOTE: Although no baseline database is required to run code pattern detection, analysis of layer conflicts requires that you configure a baseline database before you run this task.

 

Therefore, you need to create a baseline database from the database you’re using before installing the model, then you need to configure the baseline database on the AX server. Follow these steps to configure the baseline database:

 

1. Close the Microsoft Dynamics AX client.
2. Open the Microsoft Dynamics AX 2012 Server Configuration utility (Start> Administrative Tools > Microsoft Dynamics AX 2012 Server Configuration).
3. If there is no editable configuration, create a new one.
4. On theDatabase Connection tab, set the Baseline database name value so that it is the same as the Database name
5. Restart AOS.
6. Reopen the Microsoft Dynamics AX client, and continue with the upgrade.

 

Now, you can run the task for detecting code upgrade conflicts. After finishing, 3 projects may be included:

 

– Private project AxUpgradeLayerConflicts_<layer>
– Private project AxUpgradeRecIdConflicts_<layer>
– Private project AxUpgradeRuleConflicts_<layer>

 

In the project AxUpgradeLayerConflicts_<layer> you will find all elements that contain code conflicts and you can resolve them by using Compare tool

 

 

 

It is important to analyze the changes on both layers and conclude which lines need to be merged or removed on the higher layer.

 

After resolving all conflicts for an element, save the changes and mark the element in the project as resolved.

 

Purchase Price Variance In Accounting Distribution – Service Invoice

Posting a purchase order related invoice for a non-stocked product in AX resulted in a price variance posting. Normally this shouldn’t happen. Investigating the problem we noticed that it happens in case of a partial invoice posting related to single receipt. This problem happens when the invoice is the last one for the line. The AX version is AX 2012 R2.

 

To make it simple, we have one line with quantity 100 and unit price 1. We invoice 25 on the first invoice. The posting goes well. On the second invoice, we invoice the rest e.g. 75.

 

The expectation is that the no price variance will be calculated. However, if we post the invoice or view the accounting distribution we can see price variance calculated and the extended price calculated for the full amount of the line.

 

In this case, the accounting distribution looks like this:

 

Net amount: 75.00
Extended price: 100.00
Price variance: 25.00

 

The accounting distributions are calculated in the Source Document Framework. Looking into the code we noticed that when the line needs to be closed, there is a different calculation for the remaining amount.

 

The calculation starts in VendorInvoiceLineSourceDocLineItem class in the calculateSourceDocumentAmountMap method. On the line 79, it switches to the calcRemainingAmount in the same class. The third parameter marked with the red box is part of the fix. We will come back later with this.

 

 

The calcRemainingAmount method uses the SourceDocumentBalanceProvider class to calculate the balance. Here we propagate the same parameter to the parmDoRestrictByAccountingEvent. This is a new parm method created.

 

 

SourceDocumentBalanceProvider calculates the amounts. We are interested in the calculateRelievedAmount method since this is the amount used in the price calculation. Again, we propagate the information in the SourceDocumentLineProvider::getDistributedAmountInCurrency method.

 

 

Here is the origin of the problem. The query that calculates the amount uses the Accounting Distributions as a source of data. But, the accounting distributions that belong to the previous invoices are not filtered out!

 

 

The parameter that we propagated through out the classes is now used to implement the filter. This is a classical AX 2012 R2 problem. In R3 version it is fixed and in this post, the same fix is applied in R2.

 

This post is written since the problem was noticed (and it was a huge problem for the accounting department at the client). We hope that this could help someone and save some investigating time.

How To Add Custom Description To Allocation Ledger Transaction?

Adding description to a transaction happens to be a common problem in Microsoft Dynamics AX 2012. In this blog post we are going to give you a short insight about describing the steps of adding custom description to allocation ledger transaction.

 

 

Adding Custom Description to Allocation Ledger Transaction

 

First you need to extend the LedgerAllocationItem class with the parm method for description. This class holds the information related to an account allocation.

 

The LedgerAllocationController class allocates an allocation account to the physical ledger accounts that it represents. The LedgerAllocationController class has a method that performs the actual allocation of the amount based on percentage and creates the ledger – allocateAmounts method.

 

In this method you have an instance of LedgerAllocationItem class and you can fill data for description calling parm method that you have created.

 

 

Then in LedgerVoucherTransObject class (represents a single transaction in an individual voucher) in newLedgerAllocationItem method (initializes a new instance of the LedgerVoucherTransObject class from a ledger allocation item) you can fill transaction by calling the parmTransTxT with the description that you put on ledgerAllocationItem instance.

 

This should happen no matter where the transaction is posted from. It could be a general journal, a free text invoice, an invoice journal, etc.

Deploying and Managing Models in AX 2012

Microsoft had introduced the Model concept in Dynamics AX 2012, in order to help customers more easily to install and maintain multiple solutions side by side in same layer. It is recommended to use models and the model store to deploy customizations or entire solutions.

 

Model management should be very well known topic for build managers, but sometimes developers need to do the same task for any purpose.

 

For that purpose, our team made instructions using two command-line tools:

 

• Windows PowerShell
• AXUtill

 

How to Create an Empty Model that Has Default Manifest Properties

 

(AXUtil)

On the Start menu, click Command prompt.

 

Navigate to the directory for the management utilities. Typically, the location of this directory is %ProgramFiles%\Microsoft Dynamics AX\60\ManagementUtilities. At the command prompt, type the following command, and then press ENTER.

 

axutil create /model:<ModelName>/layer:<LayerName>

 

(Windows PowerShell)

 

On the Start menu, click All Programs -> Administrative Tools -> Microsoft Dynamics AX Management Shell. At the Windows PowerShell command prompt, PS C:\>, type the following command, and then press ENTER.

 

New-AXModel –Model <ModelName> -Layer <LayerName>

 

How to Export a Model

 

(AXUtil)

 

On the Start menu, click Command prompt.

 

Navigate to the directory for the management utilities. Typically, the location of this directory is %ProgramFiles%\Microsoft Dynamics AX\60\ManagementUtilities. At the command prompt, type the following command, and then press ENTER.

 

axutil export /model:<modelname> /file:<filename> /verbose

 

(Windows PowerShell)

 

On the Start menu, click All Programs -> Administrative Tools -> Microsoft Dynamics AX Management Shell. At the Windows PowerShell command prompt, PS C:\>, type the following command, and then press ENTER.

 

Export-AXModel -Model <ModelName> -File <FileName.axmodel>

 

These commands export the model to an .axmodel file

 

 

How to Uninstall a Model

 

 

(AXUtil)

 

On the Start menu, click Command prompt. Navigate to the directory for the management utilities. Typically, the location of this directory is %ProgramFiles%\Microsoft Dynamics AX\60\ManagementUtilities.

 

At the command prompt, type the following command, and then press ENTER.

 

axutil delete /model:<name>

 

(Windows PowerShell)

 

On the Start menu, click All Programs -> Administrative Tools -> Microsoft Dynamics AX Management Shell. At the Windows PowerShell command prompt, PS C:\>, type the following command, and then press ENTER.

 

Uninstall-AXModel –Model <ModelName>

 

It is recommended to perform this action only if the intention is the model to be permanently removed. But if the goal is to upgrade a model or replace a model with newer version, it is better to import the model over the existing model, because element IDs and element handles are assigned at installation.

 

If you delete a model, and then import the same model or a model with newer version, element IDs and element handles are randomized, and that affects data integrity.

 

How to Import a Model

 

(AXUtil)

 

On the Start menu, click Command prompt. Navigate to the directory for the management utilities.

 

Typically, the location of this directory is %ProgramFiles%\Microsoft Dynamics AX\60\ManagementUtilities. At the command prompt, type the following command, and then press ENTER.

 

axutil import /file:<filename> /verbose

 

(Windows PowerShell)

 

On the Start menu, click All Programs -> Administrative Tools -> Microsoft Dynamics AX Management Shell. At the Windows PowerShell command prompt, PS C:\>, type the following command, and then press ENTER.

 

Install-AXModel –File <FileName.axmodel> -Details

 

These commands install the specified .axmodel file in the same layer that it was exported from.

 

When you import a model, elements in the model that you are importing may conflict with another model in the same layer or may conflict with overlaying version for these elements in upper layer. These conflicts must be resolved. There are several ways to resolve the conflicts occurred during the model import process, and we will write about it too.

 

When you install or uninstall a model, it is recommended, before taking the action, to create a backup of the database and after the performed action you can run a full compile, synchronize the database and generate Full CIL.