Today’s #TipTuesday post is the third in my series of tips on using SmartLists in Dynamics GP. The first two posts were around the “basics” as I call them and then printing and exporting. Today’s topic is working with columns.

Changing the Column Order & Names

In my first tip post, I showed that it’s easy to resize columns. It’s not “as” easy to re-arrange columns. While some elements of SmartLists are “Excel-like” (such as sorting and resizing a column), there is no “right click - delete column” action available. Changing the columns in any way other than resizing them is done via the Columns button on the toolbar.

Once you select a SmartList and click on Columns, you get the “Change Column Display window.

Ignoring the Add/Remove part for the moment, here are the things you can do in this window:

1. Column Renaming

Simply type over the Display Name of any column and type in what you want the display name to be. It’s that easy! Here’s what I would do if it were mine, as an example.

The Original Name is non-editable and will always show you the proper name if you need to reference it. Here’s a .gif showing how to quickly reset the columns back to that list’s default settings (both column names and what columns are there in the first place):

2. Column re-ordering

The second thing you can do in this window (before I get into Add or Remove) is re-ordering the columns. Unfortunately, there is no drag and drop option, nor is there multi-select. To move columns, you must select one by clicking on it and using the arrows on the side to move either to the front or back of the list (far left, far right) or one position at a time up or down the list (which is left or right of course!).

Adding & Removing Columns

The Change Column Display window is also where you can add or remove columns to your SmartList. Many times the data you want to see is there, it’s just not a “default” column and all you need to do it add it. Here is how you do that (or at least check to see if the column you want is there to be added!).

First, click on Columns (if you’re not already in the Change Column Display window), then click on Add at the bottom. The window that opens is simply called Columns and by default it will be showing you what’s defined as “available” columns.

Where I’ve indicated with the arrow is where you will also find a drop down list letting you change to All Columns.

Now, before you get excited, depending on the Smartlist, you may not see any difference in the listing here. What you see depends on what the author who built it created for you. If you use SmartList Builder, for example, the author has control over what columns will display and be available to choose from.

In many SmartLists, the column names will be friendly enough but at times, it may require the use of some trial and error to see if a particular field contains the data you want to see (i.e. add the column then run it and remove the column if it’s not what you’re expecting).

Adding columns

Here are a few (somewhat random) tips to help you find what you want:

• Sort by the Type column.
  • If you know the field you want to add is a list type of field (drop down list, lookup or multi-select), it’s often listed here as a Drop Down List.
  • If you’re looking for a particular date field, look for Date etc.
  • Note that not all types make sense though! “Currency” is the type of field identified on things like the Note Index which is a numeric field.
• On the default SmartLists that ship with Dynamics GP, fields that have Account Index in the name are typically the actual G/L account number (even though the Type is Long Integer). The table field is an “Index” number but the default SmartLists have been linked to show the G/L Account instead, since that makes more sense.
  • Don’t see that on yours? Check if someone built this particular SmartList Object for you, i.e. if it is custom. The long way to show the Account Number is to link any Account Index field to the GL Account Master table but if you’re using SmarList Builder from eOne Solutions, they have a simple tick box to enable this for you - no work required! I wrote about this a number of years ago in this blog post.
• Use CTRL to multi-select columns to add instead of adding one at a time. Clicking on columns while holding down the CTRL key will keep the previously selected items.
• If you are used to looking at the tables for data, try sorting the Columns window by Sequence. Typically this sorts the data in table column order, from main table onwards. Often the fields you want will then appear in the same order you may be used to seeing them in the tables.

Removing columns

Boy, I wish I could tell you that to remove columns, you could multi-select and click Remove. Alas, you cannot do that, and removing columns is a one-at-a-time endeavour. Click the field in the Columns window, then click the Remove button.

To do this slightly more quickly, I will use the ALT key combination and mouse to move faster. What I mean by that is you can see the “v” in Remove is underlined meaning it is the ALT key combination to remove a field. Click on the field, press ALT+“V” on your keyboard. Repeat.

A note to “builders” of SmartLists

If you are creating SmartLists for end users, not favorites but net-new SmartList Objects, please watch for duplicate fields and help the end user by differentiating them or removing the duplicate data. For example, if I build a SmartList of Sales Transactions for instance, I may want to link to the Customer Master table or other tables. The Customer ID is in multiple tables for linking purposes. The field has the same name in those tables. If I simply added all fields from both tables, I would see Customer ID twice and perhaps not know which one to select. In this example, I would unmark the Customer ID from one of the source tables as I don’t need it twice and it won’t have different data (because it’s a key field).

Another (better) example would be a field like Salesperson or Territory. It’s possible to have a Salesperson or Territory on a customer by default, and just as possible to have overridden it on a particular sale. In my hypothetical example of a SmartList for sales transactions, if I include both fields without differentiating them, the results the user gets could vary significantly by selecting the wrong field. One returns a list of transactions based on who the typical salesperson is, one returns the list of transactions based on who the actual salesperson was on that. If you want both fields because the intent is to compare when they are not the same value, that’s perfectly valid but rename the field (one or both of them) - i.e. Salesperson from Customer, Salesperson from Trx etc.

Most users would see more than one of a given field, not know which to pick or what the difference is, and select one randomly. The results they get may matter, so help them by being clear on where the field comes from.

Well, that’s it for this tip post. Next up I will be covering favorites!