Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday: Error when attempting to export SmartList data to Excel


I have run into issues in support where people are attempting to use the “Export to Excel” feature in SmartList, and receiving an error that says something like, “We found a problem with come content in “My File Name.XLSX”.  Do you want us to try and recover as much as we can?  If you trust the source of this workbook, click Yes.”   The message will look like this. 

The message occurs exclusively with reports that are built in SmartList Builder. 

You will often see strange characters in the SmartList before you export it for specific rows, these special characters are what is causing the export to fail. 

What causes these records are NULL results from the query.  You normally will never see this with default GP SmartLists because Microsoft Dynamics GP does not allow nulls in any table, each field always has a default value. 

The typical reason you see this only in SmartLists built with SmartList Builder is that when you are creating custom joins, depending on the join types you use, you can end up with certain fields returning a NULL.  Not every record will display the special characters, and often times it’s a different record every time you refresh the SmartList, however, the root cause of it is the field returning a NULL value, and SmartList not knowing exactly how to display it, since in GP, a NULL is impossible. 

The easiest way to resolve this is to use a calculated field in SmartList Builder.  In my case, in the above screenshot, you can see that the field “Favorite Color” is sometimes displaying special characters which will cause my export to Excel to fail. 

First, you want to open the report in report writer and then click on the “Calculations” button at the top of the window. 

You need to give the field a name, and also define a field type.  In my case, I know the field is always going to be a string.  I have named my field “CALC_COLOR” and defined it as a string. 

The calculated fields in SmartList Builder use SQL script.  We want to convert any NULLS in this field to blanks.  The Syntax in SQL for this would be:  ISNULL (value, replacement value)

So in SmartList Builder you want to type in INSULL( and then double-click on the field you are using the in the calculated field. 


Resulting in this:

Then you complete the rest of the syntax, which in this case would be ISNULL({CUSTOMERDATA : Favorite Color},”)   The last character there is 2 single quotes and not a double quote.  The 2 single quotes next to each other are blank in SQL.  The window should look like this when you are finished. 

Click on the Validate button and make sure you get a message box saying the script is valid.  Then hit the save button. 

Then back in your SmartList you are going to want to UNMARK the “Display” and “Default” checkboxes for the original field, and then mark them on the newly created calculated field. 

Now the field will not display the special characters in the SmartList, and the report will also be able to be exported to Excel without error.

Leave a Comment





RECENT POSTS


Tech Tuesday: Popdock - Joining the tale of two States
Meet eOne Solutions at Directions EMEA 2023!
Connect with eOne Solutions at SuiteWorld 2023!
What is a Data Lake/Data Lake Management?
The Challenges & Benefits of Ad Hoc Reporting

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release Salesforce Salesforce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.