Dynamics 365 for operations - DB sync failed, table FinancialReports (deleting duplicate rows)

DB sync failed - table financialreports

DB sync failed due to error creating a unique index

CREATE UNIQUE INDEX I_4361FOLDERREPORTNAME ON DBO.FINANCIALREPORTS(PARTITION,DATAAREAID,FOLDER,REPORTNAME)

The duplicate key value is (5637144576, 0987, Default Report Definitions, 12 Month Rolling Single Column Income Statement - Default).

When applying application packages, DB sync failures typically occur during creation of unique index:

• New column (unique values) - if table has existing data, sync will try to create blank values in existing rows which will throw error

In my case, I got error in table finacialreports. However, data is correct. data is unique on columns "PARTITION,DATAAREAID,FOLDER,REPORTNAME, designID". But for some reason in our package, the clusteredindex doesn't have designname and hence, it is throwing errors for rows with different design name but same values for "PARTITION,DATAAREAID,FOLDER,REPORTNAME"

To resolve issue temporarily, I used below query to delete duplicate rows.

delete FROM financialreports

WHERE  recID NOT IN (SELECT MAX(recID) as recid

                  FROM   financialreports

                  GROUP  BY REPORTNAME,DATAAREAID,FOLDER,PARTITION

                  /*Even if ID is not null-able SQL Server treats MAX(ID) as potentially

                    nullable. Because of semantics of NOT IN (NULL) including the clause

                    below can simplify the plan*/

HAVING   COUNT(REPORTNAME) > 1

                 -- HAVING MAX(recID) IS NOT NULL

 ) 

Note: Before doing this, make sure you backup data for that table so that it can be used later.

Dynamics 365 for Operations: Use test VM for development VM

We have lot of environments in our project but due to some issues, we needed  to use one of our test VM for development purpose as it has the correct data.

We tried to export and import data to one of the dev boxes but the code base Test and DEV machine are different (which is another story). We were having lot of sync issues especially on views. In AX2012, we can just go and install VS tools. But in AX7, i dont know 

So, here is the approach we followed:

1. Installed VS2015 update 3. VS 2017 is not supported
2. Download binary update from LCS and extract it to a folder. In my case, I used update 4 from MS. In that folder, go to DevToolsService\Scripts, find Microsoft.Dynamics.Framework.Tools.Installer.vsix, double-click it to start the installation and walk through the installation wizard.
3. If you want to create projects, copy MSBuild target files (which are referenced by projects) to the the below location. You can do it using below Powershell script (run as admin and go to DevToolsService\Scripts to run below commands).
1. $dir = New-Item "C:\Program Files (x86)\MSBuild\Microsoft\Dynamics\AX" -Type Directory
2. cp *.targets $dir

After doing this, your VS will have correct settings w.r.t, AX7 database etc.

Now, you can connect to TFS and get latest code. You should also map your workspace properly and start using.

Note: If you want to browse thru the code of AX7 without having the full AX7 setup, please follow this wonderful link: https://community.dynamics.com/ax/b/goshoom/archive/2017/02/03/ax7-development-tools-without-vm

This post is based on the above URL. I thank Martin for his invaluable contribution to AX7 community

AX 2012 SysLookupMultiSelectCtrl issues

While working with SysLookupMultiSelectCtrl, we came across 3 issues:

Scenario: We had 3 controls in our form:

1. Dimension name - displays all financial dimensions (Existing and custom types)
2. Dimension Value - displays values of the dimension selected in the above control
3. ItemId - Multiselect lookup. Shows all released products having above dimension value

Issue 1: ItemId lookup is not opening for the first time. But When i try to open it again(2nd time), it shows data. This issue occurs only when opening for the first time

Resolution: Not sure how this works. But moving below code from ItemId.lookup() to DimensionValue.modified() resolved the issueItemIdCtrl   = SysLookupMultiSelectCtrl::constructWithQueryRun(element, msCtrlItemId, ItemIdQueryRun);

Issue 2: ItemIds are displayed in the multiselect control first time. But when we change the selection in either Dimension name OR value and open the ItemId lookup, data is not refreshed (in fact, lookup method is not firing)

Resolution: Declare ItemIdQueryRun as global variable instead of local variable. After Dimension value selection is changed, get query and querybuildsource from the queryRun and clear ranges from the query. Add ranges again and call refreshQueryRun()

Issue 3:After selecting multiple ItemIds, and then try to get selected products into a container using getSelectedFieldValues() - It returns truncated ItemIds (e.g. ItemId "00050000" is returned as "50000")

Resolution: This is an issue with the getSelectedFieldValues(). getSelectedFieldValues() calls str2con(). str2con() takes an optional parameter to specify whether to convert contents into Int64 or not. It's default value is true. For some reason, multiselectlookup is not passing this parameter to getSelectedFieldValues() and hence values are always converted to Int64 while populating container. We created a new method in SysLookupMultiSelectCtrl and called str2con() with false as last parameter and it worked fine.

References: https://community.dynamics.com/ax/f/33/t/178799