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 straightforward buttons on the toolbar in SmartList are the Refresh button (between Search and Columns), and the Stop button (between Word and Go To).
What are they for? The Stop button will stop the results from loading when a list is in Exploring mode. For example, I may be interested 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 I'm 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! 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 it will need to print. The report layout isn't even editable in Report Writer! This is an example of one simple 4-column SmartList printed to the screen plus the message the user would get if they clicked on Modify. The message will read "The current report cannot be modified because it was not created by the Report Writer". Suffice it to say printing from SmartList will only be useful if a user has a simple 2-3 column list, otherwise, it wraps the results making it harder to read.
Print button: Export to a 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 the user needs 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 this post, clicking on Excel will export the data to an Excel workbook. That's 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 several odd observations about Excel, exporting and formatting which are 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 there are 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 it is useful to embed information into a letter or something (although there is a 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, and it's relatively quick unless there are 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 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 a user is 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. What can occur if data has leading zeros in a master record field, for example, the data could be misinterpreted and leading zeros stripped off. This can be problematic depending on where/how users want to use that data, as the ID no longer is what the value is in Dynamics GP.
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 the environment, a user 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 a user was on a favorite with or without an Export Solution in place:
- The toolbar buttons are in a different order (slightly)
- The Excel button has a drop-down list indicator:
That's it for this post, stay tuned for more SmartList tips in the coming weeks!
