April 20, 2009
Something I have been asked more than a few times is how to use a SQL view in SmartList Builder to create a new SmartList. I am going to walk through this step by step using Dynamics GP 10.0 SP 3 and SQL Server 2005 SP 3:
STEP 1 - create SQL view
To start we need a SQL view. I have published a number of views for getting GP data, so I will use one of my favorites – Payables Apply Information. You can see the list of other SQL views I have published on my GP Reports page.
To create the view in SQL:
-
Open SQL Server Management Studio and connect to your GP SQL Server
-
Click on the New Query button at the top left
-
Copy the SQL code into the new window that opens (on my blog, I put the code to copy between tildes: ~~~~~)
-
In the drop-down, select your GP company database:
-
Click Execute
-
You will get a message saying “Command(s) completed successfully.”
-
If you want to use your SmartList in multiple companies, repeat steps 4 and 5 for each company database
STEP 2 - set up SmartList security for SQL view
Before we can use a SQL view in SmartList Builder we need to grant SmartList Builder security to that view:
-
Log into GP as a user that has all the proper security access
-
Open the SQL Table Security window by going to Microsoft Dynamics GP > Tools > SmartList Builder > Security > SQL Table Security
-
Choose Views at the top
-
Click on your company database on the left
-
Find the view on the right and select it:
-
If you created the SQL view in multiple company databases, repeat steps 4 and 5 for each company database
STEP 3 – create SmartList
Now we are ready to create the SmartList:
-
Open SmartList Builder by going to Microsoft Dynamics GP > Tools > SmartList Builder > SmartList Builder
-
Type in a SmartList ID and Name (the Item Name will default to the SmartList Name, I recommend leaving these the same to avoid confusion)
-
Choose what Product and Series to put your SmartList in – this is a great new option in GP 10.0 and allows you to group SmartLists as you want. This is also something that can be changed later, so some people like to create all new SmartLists is a separate place until they are satisfied with them, then move them to their ultimate location. Up to you on this one.
-
Click on the + sign to the right of Tables and choose SQL Server Table:
-
Check the Use Company Database checkbox if you are creating a SmartList that should run against whatever company database the user is logged into (or if you are planning that for the future)
-
Click on the company database on the left
-
Select Views and click on your view on the right
-
Click on the + sign to the right of Key Fields and add Key Fields (these are used determine the current record selected when opening Go To’s) – at least one Key Field is required:
-
Click Save to return to the SmartList Builder window
-
Every column in the view will be brought in as a field for the SmartList, at this point you can decide what fields to show, change formatting, etc., for now I will simply add all the fields available to the SmartList:
-
Once you’re done configuring all the additional options for your SmartList, click Save
-
To finish creating the new SmartList, go to SmartList, and say Yes to the following message:
Next steps & additional resources
Below are some additional resources for creating SmartLists and dealing with SmartList security in GP:
-
This blog – Granting access to a new SmartList in GP 10.0
-
Mariano Gomez – Changing SQL Server views for SmartList Builder SmartLists
-
Mariano Gomez – SmartList Builder Security for SQL Server Views
-
David Musgrave – Creating SQL Views of Extender Data
-
This blog – Reporting on Extender data in Dynamics GP
-
David Musgrave – Creating SQL Views of DUOS Data (added 04.22.2009)
-
Interesting Findings & Knowledge Sharing – SmartList Builder Objects using SQL Tables and Views (added 04.22.2009)
-
Steve Chapman from Rose Business Solutions - video on how to Use SQL Views to Create SmartLists (added 04.14.2010)
If you know of any other resources to add to this list, please let me know.
Hi Victoria,
I’m learning more and have enjoyed you site. It is of great help.
I’m using Smartlist Builder and trying to link the GL00201 table with the GL11110 table to create an actual vs. budget GL Smartlist. The issue is if there is data only appears if data exists in both tables for a GL Account for a specific period.
Would this require a SQL view to do this, or is there anyway around this using Smartlist Builder.
Your help is much appreciated,
Gerry
Hi Gerry,
When you’re linking the tables, are you using left join on the ACTINDX? If so, and it’s still not working, then you will most likely need to create a view in SQL first, then use that in SmartList Builder.
-Victoria
I admire the work you did and posted in the website. You saved me several times.
Sorry if you have answered this before:
Where can I find the list of all smart builber report names with folder nams in the tables structure
Thanks,
Thanks Sameer,
SmartList Builder SmartLists are in table SLB10000.
-Victoria
Hi Victoria,
Do you have a list of all the common tables used on Extender; which one stores the Extender definition and which store the actual data?
I find that the Import/Export function for extender only covers limited type, e.g. Forms, Windows etc but not other type such as View, Import, Menu etc.
I had multiple databases created in a server and require to duplicte the Extender definition done from 1 master database to the rest of the databases. Since the import/export function does not covers all, I had resorted to using SQL integration services to copy files to the other databases. I would like to copy only the definition files and not the data files.
Appreciate your kind advise.
May
Thanks
Hi May,
I don’t have a list of Extender tables published. I would look at all the tables starting with EXT4 – those are the setup tables.
-Victoria
Thanks a lot for putting up this.
Victoria – GP9 Sp3
I have a smartlist that was created using some views. We have a number of test companies and it seems that one of the views was linked by using one of the test companies in view selection, instead of live. How can I change this to point to live as opposed to the test company?
As long as the view exists in live, is it as simple as exporting, replacing the database name in the xml file ( In the section ‘SQL Database Name’) and then re-importing?
Issues that occur because of this are:
If updating field names, have to replicate in test company as opposed to just live;
If adding fields, again, have to do in test as opposed to live;
If we removed the test company for any reason what repurcussions would there be on the smartlist.
Help is always appreciated.
Stu
Stu,
It’s a little difficult to advise on this without seeing the exact setup of this as there are so many components that all need to work together. In general what I typically recommend is that all SmartLists are created with the Use Company Database checkbox selected – that way they will work in all companies and you don’t have to go crazy when you restore a current copy of live database over the test database.
You could try making sure your view exists in the live company, granting security access to the live company view in SLB and then modifying your SmartList to have Use Company Database selected. This didn’t use to work very well in older versions of SLB, but I believe it should work in 9.0 SP3. If not, you could also try your method, but I am not sure that would work, I have not done that on the same server, only when ‘moving’ a SmartList from one server to another.
-Victoria
Hi Victoria
Take a look at the URL of my blog where I have posted an article on Smartlist Security.
http://cvakumar.com/msdynamics/2008/12/23/smartlist-builder-objects-using-sql-tables-and-views/
This was created after I had this discussion with Mariano on his blog article Smartlist Builder Security for SQL Server Views.
You can add this URL to your list as well.
Regards
Siva
Siva,
Thanks! Link is added.
-Victoria