SmartList Export: Excel content error
Last year, I wrote a post about a relatively common SmartList Export error, the “Excel cannot open the file” error. Today, I was working on a client upgrade and cleaned up a ton of minor issues with some Canadian Payroll smartlists they use.
In writing this post, I have also developed a quick hatred for SmartList Designer, as you will read later in the post.
The Error
Today’s error is also fairly common but slightly different, in that you can usually at least take a peek at the Excel file to figure out what is wrong. The message text is: “We found a problem with some content in ‘YYYYMDD-######.xlsx’. Do you want us to try to recover as much as we can?”.
Most of the time, the user will click Yes and this next window will appear. The message text on this next screenshot is: “Excel was able to open the file by repairing or removing the unreadable content.”, and there’s an XML link to a log listing the repairs (supposedly, although I rarely see data that’s useful in there).
One important thing to note: the user(s) who get these errors often have the “SmartlistEnhancedExcelExport = FALSE” flag in their dex.ini file (or they don’t have that line in the file at all). The post linked above in the opening paragraph talks about this switch in more detail.
An example of the issue
Here is a screenshot of one of the SmartList exports I was working with. It’s obvious to me which columns have an issue as the exported data stands out between what values it knows how to deal with and those it does not. When the values are negative, they did not export to Excel properly. The format of the column in this case was Integer, and the default formatting (inside GP) was showing negatives in brackets, which then Excel interprets as a string/char field instead of a number.
How to resolve (with SmartList Builder)
In this case, the client has eOne Solutions’ Smartlist Builder product so the resolution was simple: modify each Smartlist to force the formatting of the field to a different type via a calculated field. Instead of re-inventing the wheel here, I will reference a Tech Tuesday post they wrote a number of years ago explaining another reason for this issue and how they resolved it. That post is here.
How I resolved the issue was essentially the same except I didn’t need to use the ISNULL in my case. For the issue above, this is what I did:
- Create a calculated field for, say, Units as shown in the above picture. The calculated field was simply selecting the field that was displayed above, and setting the Field Type to Currency. That’s it, I just needed a formula to tell SmartList (& Excel) that it’s a “currency” field.
- In the calculated fields section, I went into “Set Field Options” (the blue arrow on the field list) and unchecked “Show Currency Symbol” since the field is numeric, not a dollar field.
- I hid the original Units field, then checked the Columns button to move the new calculated field into the same position the old one was.
- Save and close, then launch SmartList (if it’s not already open) to propagate the changes. Run the same test SmartList and export to prove that the error now is gone.
The eOne post above is doing the same thing on fields where NULL values might accidentally show garbage characters and is a great example of another situation where this error can occur.
How to resolve (with SmartList Designer)
** SIDE RANT ** I was going to walk through an example of how to do the same thing with SmartList Designer, the built-in product with Dynamics GP to make new SmartLists. How hard could this be? Honest to god, I have NEVER used it. EVERY client I have ever worked with has SmartList Builder from eOne. Five minutes into attempting to mimic my client’s example on my own sample Fabrikam data using SmartList Designer and I’m ready to throw my computer out the window. I’m sorry Microsoft, but the UI/UX is awful. ** END OF RANT **
In theory, the concept is the same as what I describe above if you do not have SmartList Builder (** please invest in your staff and buy SmartList Builder to save them oodles of time and headaches **). HOWEVER, in my now-limited experience attempting to create a simple example, OH MY GOD, you’re in for a bit of work. If the SmartList is already built in SmartList Designer, perhaps the path is slightly easier for you – modify and add a calculated field. Here’s what I ran into:
A) If you are using default SmartLists (i.e. SmartLists that come with Dynamics GP), you cannot modify a default SmartList. In SmartList Builder, you can modify them to make further changes. In SmartList Designer, you would have to create a new SmartList. Not that big a deal but if you have several objects to fix, this is going to be tiresome in a hurry.
B) The SmartList designer window cannot be maximized, or not on my install. I hope I’m wrong, but I’ve got this GIANT MONITOR and SmartList Designer opens in a very tiny window where the panes of information don’t fit the information well. Please allow users to maximize this window!
C) Calculated fields are possible but damn awkward. Allow me to explain. Admittedly, it’s worse in my example because I chose to use Canadian Payroll tables as the base. Ugh.
Step 1 – click on the “FX” button in the Selected Fields pane.
Step 2 – click on Add to add a new calculated field.
Step 3 – browse the Table Fields to find your field (P_Units in my example).
< and this part of the fun is because I’m using Canadian Payroll. >
Step 4 – in the Expression pane, click in the end of the field you just added so the insertion point for the next part is AFTER the first part. I deleted my expression 3 times before I caught on to what I was doing wrong.
Step 5 – click on Functions and Arithmetic and the Divide symbol. Seriously, why can I not just type this in the Expression box?
Step 6 – repeat step 4. YES, you still have to tell it that the NEXT part of your expression should go at the end not the beginning.
Step 7 – click on Constants and type in 100 and insert it.
Step 8 – click Save. Click OK to add the field to the SmartList.
Step 9 – Click OK to save your SmartList assuming you don’t have any other changes.
I won’t even get into security, that’s too much for what should have been an easy demo here…
Sidebar: weird Canadian Payroll quirk
In Canadian Payroll, this is a very common problem because units & hours are stored in the tables as integers which have to be divided by 100 to display the proper value the user expects to see. The table might show 850 where in SmartList or in a CanPay window, this might show as 8.5. Once you create a calculated field for this, it reverts to showing what the table value is, so in the examples above, my formula looked like “{table:field} / 100” not just “{table:field}”.
Summary
Well, now that I have pissed off the amazing-except-for-this-feature people at Microsoft, I have a new-found appreciation for the folks at eOne Solutions with how ridiculously simple SmartList Builder has been to use all these years. Clearly, I’ve been very spoiled. Honestly, this is not a sponsored post, I’m not even a partner (because I don’t sell software).
This error, the original point of my post, has been an irritant for many for a long time for various reasons. Sometimes it’s bad data, sometimes it’s not (which wasn’t the case in my example at all). I hope this helps someone and if you are in charge of purchasing decisions for your Dynamics GP environment and love SmartLists, seriously, go and buy SmartList Builder, you won’t regret it.
*This post is locked for comments