SmartList Tips - printing & exporting

Today’s #TipTuesday is a continuation from last week, where I covered some of the basics around SmartLists: terminology, security, sorting & resizing columns, using the GoTo, and statuses.

In this post I plan to cover a few more things that I didn’t fit: more of the commands on the toolbar (Refresh, Stop, Print, Excel & Word) plus a bit more detail around exporting to Excel.

Refresh & Stop

Two of the more straight-forward buttons on the toolbar in SmartList are the Refresh button (between Search and Columns), and the Stop button (between Word and Go To).

When would you use those? The Stop button will stop the results from loading, when your list is in Exploring mode. I rarely use this and it’s usually on a list where I need to see some of the data to clarify the contents in a column to narrow down a search criteria.

• For example: I may be interesting in some of the Originating XXX fields on an Account Transactions list, and I don’t always remember the exact contents of each field (especially the source ones). So, I may run a smartlist with all of those columns added to see which column has the data I need to filter on, then stop it instead of waiting until it completes loading its default number of records. (PS the links in this paragraph are to previous articles where I have discussed some of the mentioned items!).

The Refresh button I’ll use about as often as I use Stop but where it is handy is if SmartList is open and you’re reviewing something that is changing (as people post transactions or make changes to a master record), refreshing will pull in the updated list of records. The most common situation I would use this in is if I’m reviewing transactions and I forgot to post a batch. I would then go and post the batch and refresh the results to see that data.

Print, Excel & Word

The next set of toolbar items are side by side: Print, Excel and Word.

Print button: Print Options window

Printing a SmartList is something I don’t think I’ve ever seen someone do. They are not pretty, I mean REALLY not pretty! Clicking on the Print button presents two options: Print to Screen/Printer or Export.

Print button: Print to Screen or Printer

The report has to be generic, because behind the scenes the engine driving this doesn’t know how many columns of data you have and what they are. The report layout isn’t even editable in Report Writer! This is an example of one simple 4 column SmartList printed to screen plus the message you get if you try to click on Modify. The message will read “The current report cannot be modified because it was not created by the Report Writer”. Suffice to say printing from SmartList will only be useful if you have a simple 2-3 column list, otherwise it wraps the results making it harder to read.

Print button: Export to file

The other option in the Print Options window is Export and the same options are available as are throughout other Report Options windows in Dynamics GP: Text file, Tab-delimited file, Comma-delimited file, HTML file or XML Data File.

The only one moderately useful might be the XML Data File, depending on what you need to export the data for. Chances are it’s still easier to do this from an Excel export.

Excel button

I would guess this is the singularly most useful part about SmartList - easily getting results out of Dynamics GP in a format that anyone can work with. For the purpose of this post, clicking on Excel will export the data to an Excel workbook. That’s really all there is to know as a starting point; however, there is more to it if I’m going to cover this properly.

In preparing to write this, I ran across a number of odd observations about Excel, exporting and formatting which is covered here as a separate post (it’s about some odd observations, not a tip post).

At the bottom of this post, I’ll expand on the Excel export to discuss a few more things. Before I do that, let’s continue with the toolbar navigation approach!

Word button

The Word export is something I’ve *never* used other than to test what it does. Like the Print option, it seems kind of useless to me since the formatting is horrible if you have more than 2-3 columns of data.

What it does do is export it to a table, which I can see could be useful for some scenarios where you may want to embed information into a letter or something (although there is Letter Writing Assistant which may do this better). I’d be curious what others have used this for!

Excel exports continued

The basic functionality of exporting a SmartList to Excel is great. It’s simple, it works, it’s relatively quick until you get into thousands of records.

At some point back when GP 10 was introduced, a dex.ini “switch” (as they are referred to) was added to speed up your exports from SmartLists. “SmartlistEnhancedExcelExport=TRUE” is the switch. This post by Mariano Gomez from several years ago covers this particular dex.ini switch and links to other articles that have more background on it.

Should this be turned on all the time on every workstation? No, and here’s why. One size does not fit all here! Depending on what you are exporting, the switch may be better set to TRUE or better set to FALSE. By default this switch isn’t even in the dex.ini file so the default behaviour of SmartList is the same as having it in there with a value of FALSE.

The enhanced part is stripping formatting to make the export faster. Why wouldn’t you want things to export faster? Well, what can occur if you have data with leading zeros in a master record field, the data could be misinterpreted as numbers and leading zeros stripped off. This can be problematic depending on where/how you want to use that data, as the ID no longer is what the value is in Dynamics GP.

Pro tip: never use leading zeros in master records if you can avoid it. Even just in exports like this, all sorts of challenges occur with it in importing, exporting and formatting data.

Here’s a very simple example: in Fabrikam, the Fixed Assets sample data has Asset IDs 00001, 00002, 00003 etc.

• Without the dex.ini switch > the Asset IDs are exported as text (like they are in Dynamics GP
• With the dex.ini switch, set to FALSE > same as above
• With the dex.ini switch, set to TRUE > the export sees a numeric value and strips the leading zeros so the Excel file shows 1, 2, 3 as Asset IDs.

Here’s another example where the dex switch set to TRUE works better for a client of mine: they have a payroll SmartList with a column that has both positive and negative values in it, the negatives are formatted with a dash. For some reason, when exporting without the dex.ini switch set to TRUE, those values are exported as text in that column instead of a negative number. With the dex.ini switch present and set to TRUE, it strips that off and it’s exported as a proper negative numeric value. What’s the big deal? Excel was ignoring string values in the column when using sum formulas so those values weren’t properly being picked up in the results. The same client has leading zeros on inventory part numbers so the users who export those types of SmartLists don’t have the dex switch as we don’t want those values stripped on export. So, one size does not fit all here! Different users have the switch set with either TRUE or FALSE depending on what they export most often.

Excel “drop down list”?

If there are any SmartList Export Solutions in place in your organization, you may see a toolbar that looks slightly different from what I show at the beginning of this post. I will cover Export Solutions in a future post, but the short version is they are ways to process and export to a specific workbook with a macro (i.e. do more than a simple export). There will be 2 changes if you are on a favorite with or without an Export Solution in place:

1. The toolbar buttons are in a different order (slightly)
2. The Excel button has a drop down list indicator

Here is what the difference is in an example .gif:

That’s it for this post, stay tuned for more SmartList tips in the coming weeks!