I am in the early stages of a set of tips around the use of SmartList in Dynamics GP. Today I was writing one for this coming Tuesday and in doing the prep work to get screenshots etc. ready, I was doing some exports. One thing led to another, and I realized I should include a screenshot of what the toolbar looks like when Export Solutions are present, one of the future topics I plan on covering. I created a super simple Export Solution to format an Account Transactions list and create a Net Amount column. It was pretty basic and off I went to test it and start putting the process together in screenshots. I tested it, and it didn't work! What the heck?

Well, this detour led me to a discovery that I hadn't yet stumbled upon, and then another when returning to the original blog I was sitting here writing in the first place. (Welcome to blogging, it's one rabbit hole after another!)

Did the toolbar change?

First, an easy one and I'm covering this in this upcoming #TipTuesday. The blog link is here. The only thing I wanted to show was how the Excel button on the toolbar changes to a drop-down list when there is an Export Solution in place. Minor right? I had never noticed the toolbar was in a different order with and without the Export Solution. Weird. (It doesn't affect anything, it's just a strange observation!)

SmartList toolbar with and without export solutions
Comparison of toolbars with and without Export Solutions in place, showing a different order of icons on the toolbar.

Exports to Excel and formatting differences

The biggest thing I noticed while writing the tip post was the export behaves differently with and without an Export Solution in place. That is something I never expected nor noticed before.

Without going into the full detail of my planned Export Solutions post, my typical process with those is something like this:

  • Export the "raw" SmartList that the Export Solution will be based on
  • Prepare a macro in Excel
  • Save the macro and workbook
  • Set up an Export Solution in Dynamics GP SmartList
  • Test

Seems straightforward enough, right? Famous last words! Here's my raw SmartList before export: note that my company settings are showing the currency with a prefix to identify CAD vs USD dollars. Many companies have something like this with a letter other than the symbol.

SmartList with formatted currency values
SmartList window showing a C$ as the currency symbol.

I didn't even notice it anymore, so I exported it without even thinking about it, nor did I notice that the export dropped the C$. I built my macro etc. per my steps above and went to test the Export Solution. Here is what exported originally, straight-up Excel export (with the dex.ini enhanced export switch set to FALSE, the equivalent of not having it in the file in the first place): it exports dollars as numbers, drops the C$ and I didn't even bat an eye. This is what I wanted to see. I built my macro based on this data format.

Excel version of the above data exported.

When I exported via my Export Solution, here is what the export came out as (and I've removed the macro "steps" so it *has* a macro but there is no code behind it): this contains the C$ and in my macro, I had a formula to create a Net Amount column which then failed since the Excel values are considered to be Text not Numeric. I expected it to be like the above, and I verified that at the time, the enhanced export switch was not even in my dex.ini file at all (let alone set to FALSE).

The same data is exported via a favorite with an Export Solution on it.

Using the dex.ini switch

So, what happens if I put the switch in there? The same thing then happens on the "plain" Excel export: the C$ exports with the dollars.

What I'm referring to when I talk about the dex.ini switch is this: "SmartlistEnhancedExcelExport=TRUE"

This speeds up the export but, there are caveats. In some situations, you may not want to use this. More info is in Mariano's blogs here and here.

What does that mean?

That means, in effect, using an Export Solution is the equivalent of using the dex.ini switch. What that also means is if users are creating export solutions where they have leading zeros on things like master records, the resulting export will be stripped of that too.

It's not a bug, it's just an observation but one that is good to know the next time users go to use the Export Solutions feature. In my case, to fully flesh out my example, I will need to incorporate a "remove C$" step in my macro code for this to work, if I intend it to work on a sample like my currency-prefixed data!

That's it for this post… enjoy!