Home > General - Builders > Using a SQL view in SmartList Builder

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.

Happy Building,
Devon

  1. May 18, 2010 at 3:27 am

    Hi Devon,

    Thanks for the shout out!

    -Victoria

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s