A common question that we see with SmartList Builder is: Why am I not able to edit a SmartList in the SmartList UI? The issue is almost always that the SmartList was created by a third-party customization and cannot be modified or found in SmartList Builder tables.
If you’d rather watch a video explaining this, see below or click here.
A few times now, we have found that a customer has updated from GP 2010 or earlier up to current GP 18.x, and the customer affirms there was a SmartList Builder report that ran in SmartList. However, it doesn’t show up in SmartList Builder to edit further.
Looking in SmartList, there is such a report as the reported: SmartList Builder Smartlist was named – KB_TEST, and it is in the “Additional SmartLists” folder as expected.
If we look in SmartList Builder lookup, we don’t see it, which suggests that this was NOT created via SmartList Builder.
There are only a handful of SLB created SmartLists, and KB_TEST is not listed here.
Querying the SLB10000 table, however, does confirm that the SmartList exists, but also shows us the reason why it isn’t visible in SmartList Builder.
Looking in the table confirms that there is a Smartlist with the name KB_TEST – but just as important that shows us the SmartList_ID is ‘.KB_TEST’ – notice the period at the beginning of the ID.
In GP/SLB 2010 – you could give a SmartList any ID you wished – in this case .KB_TEST was entered.
Starting with SLB 2013 – we now have the option to “Modify Existing SmartLists” to modify the GP out-of-the-box SmartLists. When we do that, SLB uses the internal GP SmartList Number and then puts a period before it – in the screenshot we can see a SmartList_ID of ‘.12’ – the period means “modified with SLB” and then the 12 represents the “Account Transactions” report renamed here by the user with an asterisk at the end.
Because it has the leading period, the lookup window excludes that since it isn’t a “New Report” according to the ID so it won’t show. And if we would hand enter it – SmartList Builder gives the error:
SmartList ID cannot begin with a period.
Which makes sense given the above.
The report will work fine as noted, however, if we wish to edit it, then we’ll have to update the tables on the back end to remove the period. The following script against the system database (DYNAMICS by default) will do that for both SLB & SmartView tables- just change the SmartList_ID being updated. Before running any part of the script below, please be sure that you have a backup of your Dynamics GP system and company databases.
declare @smartlistid as varchar(20) = 'KB_TEST' update SLB10000 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB10100 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB10200 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB10300 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB10400 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB10500 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB10600 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB10700 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB10900 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB11000 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB11100 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB11200 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB11300 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB11400 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB11500 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB11600 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB11700 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB11800 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB11900 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SLB20000 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SV00100 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SV00200 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SV00300 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SV00301 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SV21000 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SV50003 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SV90000 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid update SV90100 set SmartList_ID = @smartlistid where SmartList_ID = '.' + @smartlistid
After running the script above, the SmartList in question is able to be edited normally.
