Today’s #TipTuesday has been on my list for a while and it’s a great resource that many people don’t know exists.

Microsoft has creating a “living” document that has embedded links to SQL views that you can run and tweak in your own Dynamics GP environment to create custom smartlists with. The document says it is for Smartlist Designer but the reality is if you create these SQL views, you can also use them in eOne’s Smartlist Builder if you have the product instead.

Where do I get this?

The resource is a Microsoft Word document and it’s on this blog post from Isaac Olson, Microsoft Support. That blog post has a list of the SQL views that are embedded in the document.

Here’s an example of some Canadian and US Payroll views:

How to get the views

Once you double click on an entry, you will get a pop-up to verify that you want to open the embedded .sql view. If you don’t have SQL Server Management Studio on your computer, you can also choose to open this type of file in Notepad and view/edit as plain text.

This view, for example, opens up as this:

Last thoughts

There are a few “gotchas” listed on the blog post, around security (running grant.sql) as well as some general reminders about limiting your columns to the ones you new vs. the entire table list of columns! Otherwise the document is relatively self-explanatory if you are already familiar with creating views in SQL.