There is a little known feature now available in SmartList Builder 2015.  This is the option to create a SQL View from your SmartList Builder setup.  This feature was released in the 2015 R2 build 14.00.0223 and 2015 build 14.00.0023. 

There may be a time where you want to create a view from your SmartList Builder setup to use the same query in another way.  You can now do this with SmartList Builder. 

To Create a new View in SmartList Builder, follow these steps.

    1. Create the setup you want in SmartList Builder with the tables, restrictions, calculations, etc.
    2. Once the setup is complete, go to Options – Create View in SmartList Builder.
      Create View Selection
    3. The View Name field will be prepopulated with the same name as the list.  You can change this if you would like something different.

Create View

  1. The columns that are marked as default will be marked in the view, but you would like others in the view, mark them as needed.
  2. If you want the view to be created in all of your Microsoft Dynamics GP databases, mark the “Create in all company databases” checkbox.
  3. Select Create to create the view in SQL.
  4. You are now able to use the view where you would like.View script


Notes: 

  • When the View is created, there are no permissions granted to it.  You need to grant the permissions based on where you want to use the view.
  • The GP user creating the view will need to have the sysadmin server role or be in the db_owner database role for the companies the view is being created in.  By default, the only GP user with these permissions is ‘sa’.
  • You can use the Create View option in SmartList Builder again for a view if you make changes to it in SmartList Builder.  Just be aware when you do this, the view is dropped and recreated, so you will want to make sure you haven’t modified it in SQL after creating it using SmartList Builder. Those changes would be lost.  You would also need to set up the security to the view again.