SmartList Export Oddities

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 you use Export Solutions, 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 really sitting here writing in the first place. (Welcome to blogging, it’s one rabbit hole after another!)

Did you know the toolbar changes?

First, an easy one and I’m actually covering this in this upcoming #TipTuesday. The blog link is here but the link won’t be “active” until Oct 1st. 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 is in a different order with and without the Export Solution. Weird. (It doesn’t affect anything, it’s just a strange observation!)

Exports to Excel and formatting differences

The biggest thing I noticed while writing the tip post was the export actually 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 a the Export Solution in Dynamics GP SmartList
• Test

Seems straight-forward 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.

I don’t even notice it anymore, so I exported it without even thinking about it, nor did I actually 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.

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

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 as you’ll read in my October 1, 2019 #TipTuesday coming up, 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 then means is if you are creating export solutions where you 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 you 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!