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

AX7 VM - 500 Error

Few days ago, I have installed AX7 on my local box and it was working fine.

However, when I opened it today, I got Error 500 while opening home page (https://usnconeboxax1aos.cloud.onebox.dynamics.com/en/).

Looks like it is an issue with the date/time. Here are the steps I followed:

1. In my windows 10, I have noted date, time and timezone
2. Restarted and logged into AX7 by selecting AX7 boot option
3. opened date/time settings
4. changed time zone option to match with my win10 OS settings
5. Next sync time with internet time (shown below)
6. screenshot -->
7. After successful sync, you can now login to AX7 successfully

My system setup: I have three OS options at the time of booting. 1) Windows 10 2) AX 2012 R3 VM 3) AX7 VM

Reliance JIO Jiofi2 - Review

Today, I got a Jiofi with the help of my friend Uday. It is the new version of the device with OLED display.

My devices - Desktop, Windows 10 laptop, Meizu M2 Note, Samsung tab P3100

24-Sep-2016 update (Final):

• JIOFI speeds are pathetic even when it shows 4 bars in the JIOFI device
• Jio TV app very frequently fails to connect on my phone. Sometimes, I have to open app, connect to my JIOFI and then connect to my home wifi to see the channel. I guess JIOFI is not able to provide require bandwidth. But once connection is made, the app is not checking whether i am using JIO network or home wifi on a continuous basis.

I decided not to use it anymore, far from my initial thoughts of using it as my primary internet source. May be i will give it to one of my enthusiastic friends who wants to try the JIO experience.

19-Sep-2016: First impression:

• Device is sleek and good looking.
• Initially, speed on my phone is ~50 Kbps but improved after placing it another room
• Connection seems to be dropping but reconnecting quickly. Need to check while I am working on remote desktop as it will be easily confirmed if connection is erratic or not
• What caught my eye is "Designed in India" at the back. Usability could be better. To press POWER button to see the display is not convenient. Instead, the JIO logo could have been a capacitive button to light up the screen.
• I am getting 6 - 11 Mbps on my laptop and 2-3 Mbps on my phone (Tests done using speedtest.net, You have to note that the server used during testing was hosted by JIO. I am not sure whether it has any relevance to speedtest). Note: In my area (Aarambh township, Lingampally, Hyderabad, India - 500 019), network connectivity is very bad (I have Airtel which is very poor, despite reaching out to their open network initiative there is no response or action taken)
• http://www.speedtest.net/my-result/5644676280

Conclusion: 

• Low speeds on phone but decent on laptop. Will check phone settings and update later
• Not yet ready to replace my wired internet with this, need more testing