We have seen a new issue with SmartList export in Microsoft Dynamics GP 2013 – it is truncating numbers to the thousands place. For example, on the two incidents we saw, they had a number in SmartList like 4,950.00. When exported to Excel, it exported as 4. Changing the format on the column does nothing to fix the problem.
This error seems to be related to a change Microsoft did with the formatting of SmartList data for Excel. With the introduction of the Web Client in Microsoft Dynamics GP 2013, the development team needed the ability improve the performance of SmartList exports over the browser. To gain a dramatic improvement in performance, during the export to Excel, the number format in a SmartList amount column is ignored and the amount column is populated with 5 decimals in Excel. Thus, if everything works correctly the SmartList number above of 4,950.00 should export as 4,950.00000.
Unfortunately a bug seems to have crept into a version of this ‘enhancement.’ We have not yet determined which specific builds are affected, but as mentioned some customers are only getting 4 exported to Excel in this example. We were able to find a fix. (However, this fix should not be used if the client is using Web Client.) The fix is an undocumented Dex.ini switch that provides limited formatting to SmartList, thus maintaining export performance:
SmartListEnhancedExcelExport=TRUE
If you are not going to deploy Web Client, then this should be fine as a workaround. However, this switch causes the Web Client to error out with the following message when attempting to export to Excel:
For additional tips and tricks regarding Microsoft Dynamics GP and other Dynamics products, check out other informative, useful blogs on our site by clicking here.
