Smartlist and Navigation Lists export currency values with 5 decimals in Excel using Microsoft Dynamics GP

Se aplica a: Dynamics GP 2013Excel 2010Microsoft Dynamics GP 2015

Symptoms


Smartlist, SmartList Designer, Excel Builder, and Navigation Lists export currency values with 5 decimals in Excel using Microsoft Dynamics GP 2013 and greater versions. 

Cause


In Microsoft Dynamics GP 2013, the way data is exported from Smartlist and Navigation Lists to Excel has been changed.  You will notice that amount columns are populated with 5 decimals in Excel and the formatting shown in the Smartlist appears to have been lost during the export.  This is by design in Microsoft Dynamics GP 2013.  This change was made to increase the performance of exporting data to Excel from Smartlist, and also to allow for exports through Smartlist from the Web Client.  The code was changed from COM to OpenXML SDK, and to export the full 5 decimals directly from the SQL tables to ensure there is no data loss.  This approach is safer than truncating the data to two decimals, and allows the user to format the field how they want in Excel. This change is by design to improve performance and prevent data loss.

Resolution


In Microsoft Dynamics GP 2013 and going forward, you will have to use one of the methods below to format the data:   

METHOD 1: Format in Excel each time.

Format the currency field directly in Excel to two decimals.  To do this:   

1.  Open Smartlist and export the data to Excel.

2.  In the excel spreadsheet, right-click on column heading for the column you wish to format, and select Format Cells...

3.  In the format cells window, select Currency and set the Decimal places to 2.

4.  Choose if you want a Symbol displayed or not, and how you want negative numbers to appear.  Click OK.

5.  Save the spreadsheet.

 

METHOD 2: Use Export Solutions in Smartlist.

Create a formatted spreadsheet in Excel and use Export Solutions in Smartlist to reuse the same spreadsheet as a template to export to. To do this:   

1.  Open Smartlist and export the data to Excel.

2.  In the excel spreadsheet, right-click on column heading for the column you wish to format, and select Format Cells...

3.  In the format cells window, select Currency and set the Decimal places to 2.

4.  Choose if you want a Symbol displayed or not, and how you want negative numbers to appear.  Click OK.

5.  Enter a file name and location to save it to. Click Save.

6. Open the saved file again in Excel. (It should be listed under File | Recent).  The line numbers are listed in the left-most column.  Right above the ‘1’ for line 1, you will see a blank cell.  Right-click on this blank cell and choose Clear Contents.  This should clear all the data from the spreadsheet.  Close the spreadsheet and save your changes to create a blank template or worksheet.  

7. Now in Microsoft Dynamics GP, set up the export to be able to use this formatted spreadsheet going forward.  To do this:

a. Click Microsoft Dynamics GP and click Smartlist.

b. In the top menu-bar, click on Smartlist and select Export Solutions.

c. Key in a Name in the Name filed in the upper right side of the window.

d. In the Document field, browse out to the formatted spreadsheet you created.

e. Change the Application field to Excel.

f. In the Works for Favorites section, expand what Smartlist and mark the ‘checkbox’ to the left of that Smartlist(s) you want to use with the saved spreadsheet. You can mark as many of favorites that you want to use this formatted spreadsheet.  

g. Click Save.  Exit out of the Export Solutions window. 

h. Refresh the Smartlist window.

I. Select the Smartlist and now you should now see a drop-down arrow under the EXCEL button, where you can select to use the formatted spreadsheet you made, (or the ‘Quick export’ will be the old way showing all 5 decimals.) 

j. Select the name of the formatted spreadsheet you created, and the data will export to Excel, where the currency column will now be formatted to two decimals.

k. Click FILE | SAVE AS and save the spreadsheet under a new name, to leave your template empty. 

 

Note:  Create as many formatted spreadsheets as you need and use Export Solutions to link them to the appropriate Smartlist.

More Information


The above information above can also be found in the Microsoft Dynamics GP blog (Publish date 8/27/2013):

https://community.dynamics.com/controlpanel/blogs/posteditor.aspx?SelectedNavItem=Posts&WeblogID=536&WeblogPostID=201539



More information on why this change was made is also discussed in this blog for Microsoft GP Developers (Publish date 9/23/2013):

http://blogs.msdn.com/b/developingfordynamicsgp/archive/2013/09/23/microsoft-dynamics-gp-2013-smartlist-export-to-excel-shows-currency-values-with-5-decimal-places.aspx