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.

The Error

Today's error is also fairly common but slightly different, in that I 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?".

Error message in Microsoft Excel. The message text is described in the paragraph above this image.
Error message "We found a problem with some content".

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).

Further detail about the repaired records in Microsoft Excel. The full error message is noted in the paragraph above this screenshot.
Error message: Excel was able to open the file by repairing or removing the unreadable content.

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 were 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 Excel interprets as a string/char field instead of a number.

Snipped from Excel of the issue. The Units column shows some values right justified, which Excel recognized as numeric, while other values that contained negatives were exported as string values.
A sample of the content that was exported with highlighted cells indicating the items that did not export properly to Excel.

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 reinventing the wheel here, I will reference a Tech Tuesday post they wrote many 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:

  1. 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.
  2. 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.
  3. 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.
  4. 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)

😡
** 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 since every client I have ever worked with has SmartList Builder from eOne Solutions. Five minutes into attempting to mimic my client's example on my own sample Fabrikam data using SmartList Designer I'm ready to throw my computer out the window. I'm sorry Microsoft, but the UI/UX is awful.

In theory, the concept is the same as what I described above if SmartList Builder ("SLB" for short) isn't licensed in the environment. In my limited experience attempting to create a simple example, OH MY GOD, it's not as easy as it should be. If the SmartList was already created in SmartList Designer ("SLD" for short) perhaps the path is slightly easier: modify and add a calculated field. Here's what I ran into:

  1. Default SmartLists (i.e. SmartLists that come with Dynamics GP) cannot be modified in SLD. In SLB, they can be modified. In SLD, a new SmartList would need to be created. That is not that big a deal but if there are several objects to fix, this is going to be tiresome in a hurry.
  2. The SLD designer window cannot be maximized, or at least I wasn't able to. I hope I'm wrong, but I've got a large primary 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!
  3. Calculated fields are possible but very awkward. Allow me to explain. Admittedly, it's worse in my example because I chose to use Canadian Payroll tables as the base. Ugh.
    1. Click on the "FX" button in the Selected Fields pane.
    2. Click on Add to add a new calculated field.
    3. Browse the Table Fields to find the right field (P_Units in my example).
    4. (Now for the fun part because I'm using Canadian Payroll) In the Expression pane, click at the end of the field 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.
    5. Click on Functions and Arithmetic and the Divide symbol. Seriously, why can I not just type this in the Expression box?
    6. Repeat (d). YES, I still had to tell it that the NEXT part of my expression should go at the end not the beginning.
    7. Click on Constants, type in 100 and insert it.
    8. Click Save. Click OK to add the field to the SmartList.
    9. Click OK to save the SmartList assuming there are not any other changes that need to be made.

I won't even get into security, that's too much for what should have been an easy demo here…

💡
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 would show 850 whereas in SmartList or a CanPay window, this would show as 8.5. Once the calculated field is created, 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. 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 your users love SmartLists, go and buy SmartList Builder, you won't regret it.