Changing SQL Server views for SmartList Builder smartlists

One of the valuable tools that Microsoft has released is SmartList Builder for Dynamics GP. The concept is very simple: if the standard out-of-the-box Smartlist does not cut it for you, well, you build your own! Furthermore, SmartList Builder does allow for some nifty stuff, such as working with Microsoft SQL Server views and (custom) tables. Custom tables are not the focus of this article.

If you can't get your smartlist done with all the SmartList Builder query building capabilities, then create your own views of the data in Microsoft SQL Server, grant access to them in SQL Server, then, grant security to them in SLB, and use them.

But what if after deploying your smartlists, users request more columns of information that you did not include in your original SQL Server views, and now you need to make those changes and affect your previous work of art?

Solution

The solution may be easier than you think, but please do not delete your existing SmartList Builder and recreate all that work again! It's not necessary! These simple steps will illustrate how to change your SQL Server views and make these changes affect your existing SmartList Builder objects.

1) First, create your SQL Server view (If you already have a view skip to step 5), i.e:

/* Mariano Gomez, MVP
   Sample code to illustrate changes in SQL Server view-based smartlist
*/
IF OBJECT_ID('dbo.querySomething') IS NOT NULL
   DROP VIEW dbo.querySomething
GO

CREATE VIEW dbo.querySomething AS
SELECT JRNENTRY, TRXDATE, ACTINDX, DEBITAMT from GL20000
GO

GRANT SELECT ON dbo.querySomething TO DYNGRP
GO

NOTE: This is a trivial example to illustrate the solution. Views should and must be used when you have exhausted all possibilities in SmartList Builder.

2) Grant security to the new object in SmartList Builder in GP. Go to Microsoft Dynamics GP > Tools > SmartList Builder > Security > SQL Table Security


















3) Create a smartlist with the new view, marked all columns in the Default list

4) Build the smartlist by going to Microsoft Dynamics GP > SmartList. Click on Yes to build SmartList.

5) Now, go back to SQL Server Management Studio and add a new column to the view, as follows:

/* Mariano Gomez, MVP
   Sample code to illustrate changes in SQL Server view-based smartlist
*/
ALTER view [dbo].[sampleGLforSLB] AS
SELECT JRNENTRY, TRXDATE, ACTINDX, DEBITAMT, CRDTAMNT from GL20000
GO

NOTE: In this case I added the CRDTAMNT (credit amount) column.

6) Go back to SmartList Builder, open the smartlist and click on the Edit Selected Table button to open the Add SQL Table window.




















All that's needed is to click on the Save button, nothing else! Surprisingly, this is not documented anywhere in the manuals. Now the CRDTAMNT column shows up in the fields list on the right pane. Then click on the Default check mark to add to the default list of columns to be displayed, or manually add the column in your smartlist after rebuilding with the changes.

7) Now, click on the Save button in the SmartList Builder window.

8) Open SmartList to build the modified smartlist with the new changes.

Hope these simple steps help you with modifying your views and getting your SmartList Builders to work smoothly with the changes.

Until next post!

MG.-
Mariano Gomez, MIS, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/