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 created a "living" document that has embedded links to SQL views that an admin can run and tweak in their Dynamics GP environment to create custom SmartLists. The document says it is for Smartlist Designer but the reality is these SQL views can also be used in eOne's Smartlist Builder for those who have that 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
To get a view, double-click on an entry, which results in a pop-up to open the embedded .sql view. Don't worry if SQL Server Management Studio is not installed, it is possible to choose to open this type of file in Notepad and view/edit it 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 the columns to the ones needed vs. the entire table list of columns! Otherwise, the document is relatively self-explanatory if you are already familiar with creating views in SQL.
