How to use a SQL view in SmartList Builder

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:

  1. Open SQL Server Management Studio and connect to your GP SQL Server
  2. Click on the New Query button at the top left
  3. Copy the SQL code into the new window that opens (on my blog, I put the code to copy between tildes: ~~~~~)
  4. In the drop-down, select your GP company database:change-db
  5. Click Execute
  6. You will get a message saying “Command(s) completed successfully.”
  7. 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:

  1. Log into GP as a user that has all the proper security access
  2. Open the SQL Table Security window by going to Microsoft Dynamics GP > Tools > SmartList Builder > Security > SQL Table Security
  3. Choose Views at the top
  4. Click on your company database on the left
  5. Find the view on the right and select it:sql-table-security
  6. 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:

  1. Open SmartList Builder by going to Microsoft Dynamics GP > Tools > SmartList Builder > SmartList Builder
  2. 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)
  3. 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.
  4. Click on the + sign to the right of Tables and choose SQL Server Table:slb01
  5. 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)
  6. Click on the company database on the left
  7. Select Views and click on your view on the right
  8. 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:slb02
  9. Click Save to return to the SmartList Builder window
  10. 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:slb03
  11. Once you’re done configuring all the additional options for your SmartList, click Save
  12. To finish creating the new SmartList, go to SmartList, and say Yes to the following message:slb04

About nijamkhan

I am simple Hardworking,and i would like improve the Knowledge time by time
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s