Using a SQL view in SmartList Builder
Although this blog is dedicated to the features within Builder itself, I have to admit that about 1/3 of the Builder lists I do have at least one SQL view. Either I want to filter a table before adding it, group by certain fields first, or some other data manipulation before I use the data in Builder. In that spirit, here are links to other blogs and post about using SQL views:
Victoria Yudin – How to use a sql view in Smartlist Builder Pay special attention to the “Use Company” checkbox. This is VERY important, and generally can’t be changed after adding your view to your List.
Also note that all of her SQL views end with:
GRANT SELECT ON view_name TO DYNGRP
This is critical if you want the SmartList data to be loaded by any user other than sa.
Mariano Gomez – SmartList Builder Security for SQL Server Views Key on this is the two steps at the bottom, you will need add this Task and assign it to all your users so that they can use your new SmartList with a SQL view or table. (In GP9 or GP8, these rights are listed under SmartList Builder section of the Tools menu – User Security)
Mariano Gomez – Changing SQL Server views for SmartList Builder SmartLists A trick I use all the time, your new fields will appear at the end of the field list, but you can live with that!
Here are various blogs that will have sample SQL views that you can use:
Victoria Yudin - http://victoriayudin.com/category/dynamics-gp/gp-sql-scripts/
She also has a great section on GP Tables, see the menus at the top.
Real Life Dynamics User – http://rldu.wordpress.com/sql-scripts-views/
Let me know if you come across any other resources and I will add them.