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

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

  • Comments 1

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

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.

However, we have received several cases asking if this is a bug in Microsoft Dynamics GP 2013, because it did not work this way in prior versions.  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.

 

I hope you will find this tip useful in the future and save you time from having to format in Excel for each export, when using the same Smartlist.  (This information is also found in KB 2881746.)

 

Thank you,

 Cheryl Waswick | Sr. Technical Support Engineer | Microsoft Dynamics GP Support

  • *sigh*  It _is_ a bug.  The reason you've received multiple support requests is because it was a poor design decision that should be corrected.

    The work-arounds you describe require either the user to reformat every exported SmartList or an administrator to set up an export solution for every SmartList.  This is a lot of work.

    Third party addons have no problem exporting to Excel with proper formatting and good speed.