Today’s article is a simple tip, but can save you a lot of time troubleshooting SmartLists. I would estimate that I get close to 5 calls a day that start off with “My default GP SmartLists are broken or returning funny results and Microsoft said to call you”. Normally what has happened is that people have modified the default SmartList and replaced it with a SmartList Builder SmartList. I would estimate that 99% of the time this is done inadvertently.

Beginning with Microsoft Dynamics GP 2013, there was a new button add to SmartList labeled “Modify”. This was added by Microsoft when they added their “SmartList Designer” product to GP. If you do not have SmartList Builder installed, the button will open SmartList Designer. If you have SmartList Builder installed, it opens SmartList Builder.

One of the new features of SmartList Builder beginning with 2013 was the ability to “modify” existing SmartLists. It doesn’t actually modify the existing list, what it does is create a copy of it, and then hides the default list. The copy has the same exact name and security settings, however once you have copied it, you can add additional tables. We added the feature because a common request we got was “The GP SmartList gives me everything I need, except for 1 field from another table.” Until now the only option was to recreate the entire list from scratch, and then add your additional table to it. Some of the default SmartLists are very complex, so you could spend a week recreating the entire SmartList from scratch, just to be able to add 1 new field to it.

So the modify option allows you to do just that, you start off with the existing SmartList, and then you can link new tables to it, saving you the trouble of recreating the entire SmartList from scratch.

However what we run into a lot is people thinking the “Modify” button is used to create favorites. So you have a user trying to create a favorite, they hit the modify button and start adding a restriction to the Smartlist, or unmarking the columns they don’t want to see. About ½ way though adding the restriction they realize that the windows don’t seem familiar, so they exit out of SmartList Builder. When the exit SmartList Builder, it will ask them if they want to save or delete the changes. Of course they hit save because they don’t want to “delete” anything.

What then happens is that the default SmartList is replaced with a modified copy of the SmartList. Typically the first complain you hear is “My favorites are missing”, or “My favorites are not returning the right data”, or “My SmartList doesn’t return any data”. There are a few different items that are combining to create all these issues.

  1. The user that accidentally modified the SmartList started to create a restriction, so they have restricted the list to just one customer. Another user has a favorite saved that shows only a different customer. When they click on the default list, the only see 1 customer because the default list has been modified to show only 1 customer. When they click on their favorite, they see no data because the customer they are looking for is no longer in the main list because of the SmartList Builder restriction.
  2. The columns that were used in their favorites were removed. Once again, the person that accidentally modified the SmartList started removing columns that they didn’t want to see. They removed a column that another user was using in a favorite. When the other user uses that favorite, it’s either going to display no data, or garbage data since it’s trying to restrict on a column that is no longer there.

I would say that 99% of the time the modification is done inadvertently by someone, and here is how you can tell. Open up SmartList Builder (Tools – SmartList Builder – SmartList Builder). Change the SmartList Type to existing, and hit the dropdown arrow next to the SmartList ID. Look in the list there and see if you have any SmartLists that have an asterisk after the name. Normally it will be the one you have multiple users complaining about not working anymore. Once you find up, pull it up in the SmartList Builder and take a look at it. Here is a screenshot of my Account Transactions SmartList. In it you can see the ID has an asterisk after it, so we know it has been modified. However if we look at the tables in the SmartList, the ONLY table in the SmartList is the “Account Transactions” table. This means that no additional tables were added to the SmartList. So it’s not really modified, someone just made a restriction or started removing columns. Making restrictions or removing columns should ALWAYS be done by making a favorite and not by modifying the default list. If you modify the default list, you will end up breaking other users’ favorites because any restrictions you apply, or fields you remove are being done globally for everyone.

If you want to find out when and who modified it, you can run the following query in SQL server management studio against your DYNAMICS database. Replace the SmartList name with the name of the SmartList that was modified. In my case the SmartList is Account Transactions, so my select statement would look like this:

select MODIFDT,MDFUSRID,* from SLB10000 where SmartList_Name=’Account Transactions’

In the screenshot below you can see that the user “john” modified my SmartList on 10/6/2016, which is coincidentally the same day that all my users starting calling me telling me that their favorites were broken.

 

If there are no additional tables added to the list, it means that nothing has been changed that cannot be done with a SmartList Favorite. The solution is to hit the delete button on the modified list in SmartList Builder. This will delete the modified one, and the next time you launch SmartList the default one will return. Typically at that point, all the existing favorites will start working again.

Now the question comes up, “How can we keep people from modifying the default lists?” Starting with GP 2015 R2, we added a new security option to SmartList builder that you can unmark to prevent people from modifying existing SmartLists, but allow them to create new ones. If you have a security task setup for SmartList Builder, you normally would edit the task you already have created. Our SmartList Builder expert Nicole Albertson wrote an excellent blog article on how to use this new security feature. Her article can be found here: Tech Thursday: Security for the Modify button in SmartList

So while the ability to modify default lists can be extremely powerful, use it with caution because you may inadvertently be breaking existing favorites for all your other users. You need to determine what is more cost effective when deciding whether to modify the list or create a new one.

If you need to add 1 additional field to a SmartList that not very many people use, then modifying the default is usually a good idea. Any favorites created against the default list might have to be recreated, depending on what you change, but if only a few people use the list, and they only have a few favorites each, modifying the default list is going to save you time.

If you need to add 1 additional field to a SmartList that EVERYONE in the organization uses, and there are already 1000 user favorites on that SmartList, it’s better to create a new list from scratch. You will spend more time creating the SmartList, however then you are not risking breaking existing favorites by modifying the existing list. Depending on what columns you are changing or adding, you might not break any favorites, or you might break all 1000. In cases where it’s a list that lots of people use, and lots of favorites have already been created, it’s usually safest to create a new list from scratch.