Multi-Company SmartLists in GP - Part 2

In Part 1 of this series, I shared a scenario where a centralized accounting department would benefit from having SmartLists that allow users to query data from multiple Dynamics GP company databases. I then explained the basics of creating multi-company views, which serve as the basis for multi-company SmartLists.

Before we proceed, let me provide the answer to the Bonus Quiz from Part 1. So what step did I omit that would prevent you from adding a custom view to a new SmartList in SmartList Builder? Well, in SQL Server databases, there are security settings that determine which database objects users can access, such as tables, views, and stored procedures. After you create any custom object in a Dynamics GP database, you need to make sure to grant access to the object so that Dynamics GP users can access it. Fortunately this is relatively simple. For convenience, I highly recommend using a script to grant permissions, as it is simpler and much faster than trying to use SQL Server Management Studio to change permissions on a single object. It can certainly be done with Management Studio, but after waiting for several minutes as the windows refresh, you'll understand why I prefer scripts.

In this case, we created a new view called "csvwAllVendors".

(Aside #1: I borrowed this naming convention from Lorren Zemke and his colleagues at WennSoft. It means "cs" = Custom, "vw" = View. cstb = custom table, cssp = custom stored procedure. The convenience of using this naming convention consistently is that you always know that you can find your custom objects in Dynamics databases by searching for "cs".)

(Aside #2: Because this view queries data from multiple databases, in which database should it be created? My recommendation would be the Dynamics database, since all GP users have access to that database, and because it can serve as a central repository for all multi-company views.)

To grant access to this new view to all Dynamics GP users, you can run the following script:

GRANT SELECT ON DYNAMICS..csvwAllVendors TO 'DYNGRP'

That's it! The view should now show up in the SmartList Builder.

Okay, so now that the quiz is settled, let's get back to our original requirements:

1. Display data from multiple GP databases in a single SmartList accessible from any GP company
2. Display the name of the database where the data is stored
3. When new GP company databases are created, automatically include data from the new databases
4. Allow some companies to be excluded from the multi-company SmartLists


In Part 1, I discussed how to fundamentally display data from multiple GP databases, and I also explained how to display the name of the company for each record.

But how can we automatically include data from a new GP company database in our query? Since the multi-company view is hard-coded with the name of each database, how can we "automatically" have new companies included in the query?

This requires a little bit of creative VB scripting. (There are certainly other approaches, but this is what came to my mind first and seemed easiest.) Instead of thinking of the view as a hard-coded query of specific company databases, let's rewrite it as a generic query for any company database:

SELECT '^' AS Company, * FROM ~..PM00200

In this case, the caret '^' serves as our placeholder for the company name, and the tilde '~' is the placeholder for our physical database name. Now imagine if we could have some way of substituting these two characters with the names of every company database.

First we would have to have a list of every company database. Of course, Dynamics GP has such a list:

SELECT INTERID, CMPNYNAM FROM DYNAMICS..SY001500

Great, so we now have a list of the company databases and company names. But we still need to combine that list and our generic query so that we have a multi-company view. With a few lines of VBA, we have such a solution that will perform a search and replace on our query template:


Dim GPCommand As New ADODB.Command
Dim rsCompanies As New ADODB.Recordset

Dim strQuery As String
Dim intCompany As Integer

Set GPConn = UserInfoGet.CreateADOConnection
GPConn.CursorLocation = adUseClient
'Set DYNAMICS as the default DB
GPConn.DefaultDatabase = "DYNAMICS"
GPCommand.ActiveConnection = GPConn

'Get a list of companies
strSQL = "SELECT INTERID, CMPNYNAM FROM DYNAMICS..SY01500"
GPCommand.CommandText = strSQL
Set rsCompanies = GPCommand.Execute

intCompany = 0
strView = "CREATE VIEW csvwAllVendors AS "

'Loop through each company record and build the SQL for the view
While Not rsCompanies.EOF
intCompany = intCompany + 1
'Replace the ^ with the full company name
'Replace the ~ with the database name
strQuery = Replace(Replace("SELECT '^' AS Company, * FROM ~..PM00200", "^", Trim(rsCompanies.Fields("CMPNYNAM").Value)), "~", Trim(rsCompanies.Fields("INTERID").Value))
If intCompany = 1 Then
strView = strView & vbCrLf & strQuery
Else
strView = strView & vbCrLf & "UNION" & vbCrLf & strQuery
End If
rsCompanies.MoveNext
Wend

'Execute the CREATE VIEW statement
GPCommand.CommandText = strView
GPCommand.Execute

'Grant permissions to DYNGRP
strSQL = "GRANT SELECT ON csvwAllVendors TO DYNGRP"
GPCommand.CommandText = strSQL
GPCommand.Execute

MsgBox "csvwAllVendors was created!"


(NOTE: This code sample is for GP 10, as it uses the UserInfoGet object to connect to the GP database, which means that it must be run from within GP 10 as VBA. For GP 9, you will need to use the RetrieveGlobals9.dll, which gives you the freedom to run the script from GP, or as a VBS file outside of GP--although a user must be logged in to GP on the workstation when the VBS file is run.)

If you just use Modifier to create a new button on a window, like on the Company Setup window, you can paste this code into a button event, and it will create the multi-company view.

But what is missing from the script? It works the first time it is run, but what problem will you have on the second attempt? Well, on the first run, it is creating the view. The second time it is run, the view will already exist, and the CREATE VIEW statement will fail with an error. So we have to enhance it to detect whether custom view already exists or not. Before our code begins to build the view SQL statement, we need to add something like the following:


'*********************************************************
'Check to see if the view already exits
'SQL Server 2000 - Use older SQL 2000 sysobjects table
'GPCommand.CommandText = "SELECT COUNT(*) AS Records FROM sysobjects WHERE type = 'V' AND name = 'csvwAllVendors'"
'SQL Server 2005 - Use SQL 2005 sys.objects system view
GPCommand.CommandText = "SELECT COUNT(*) AS Records FROM sys.objects WHERE type = 'V' AND name = 'csvwAllVendors'"
Set rsViewExists = GPCommand.Execute

If rsViewExists.Fields("Records").Value = 0 Then
strView = "CREATE VIEW "
blnGrant = True
Else
strView = "ALTER VIEW "
blnGrant = False
End If

rsViewExists.Close
'*********************************************************


So we now our script can create the view or alter the view if it already exists!

Okay, so in concept we now have the ability to "automatically" update the view when a new company database is added. But what about excluding certain companies from the multi-company SmartLists? How about we use one of the User Defined fields on the Company Setup window to indicate whether to include the company in custom view? If we set User Defined 1 on the Company Setup window to have a value of "MULTICOMPANY VIEWS", we can adjust our company query to be:

SELECT INTERID, CMPNYNAM FROM DYNAMICS..SY01500 WHERE UDCOSTR1 = 'MULTICOMPANY VIEWS'

Any value in this field would work, but something obvious and explanatory helps avoid confusion. Using a UDF field makes it easy for a GP user to specify which companies are included in the views. So if we combine all of this together, we get a script like:


Option Explicit
Private Sub RefreshViews_AfterUserChanged()

Dim GPConn As New ADODB.Connection
Dim GPCommand As New ADODB.Command
Dim rsCompanies As New ADODB.Recordset
Dim rsViewExists As New ADODB.Recordset

Dim strQuery As String
Dim intCompany As Integer
Dim strView As String
Dim blnGrant As Boolean

Set GPConn = UserInfoGet.CreateADOConnection
GPConn.CursorLocation = adUseClient
'Set DYNAMICS as the default DB
GPConn.DefaultDatabase = "DYNAMICS"
GPCommand.ActiveConnection = GPConn

'Get a list of companies
strQuery = "SELECT INTERID, CMPNYNAM FROM DYNAMICS..SY01500 WHERE UDCOSTR1 = 'MULTICOMPANY VIEWS'"
GPCommand.CommandText = strQuery
Set rsCompanies = GPCommand.Execute

intCompany = 0

'*********************************************************
'Check to see if the view already exits
'SQL Server 2000 - Use older SQL 2000 sysobjects table
'GPCommand.CommandText = "SELECT COUNT(*) AS Records FROM sysobjects WHERE type = 'V' AND name = 'csvwAllVendors'"
'SQL Server 2005 - Use SQL 2005 sys.objects system view
GPCommand.CommandText = "SELECT COUNT(*) AS Records FROM sys.objects WHERE type = 'V' AND name = 'csvwAllVendors'"
Set rsViewExists = GPCommand.Execute

If rsViewExists.Fields("Records").Value = 0 Then
strView = "CREATE VIEW"
blnGrant = True
Else
strView = "ALTER VIEW"
blnGrant = False
End If

rsViewExists.Close
'*********************************************************

strView = strView & " csvwAllVendors AS "

'Loop through each company record and build the SQL for the view
While Not rsCompanies.EOF
intCompany = intCompany + 1
'Replace the ^ with the full company name
'Replace the ~ with the database name
strQuery = Replace(Replace("SELECT '^' AS Company, * FROM ~..PM00200", "^", Trim(rsCompanies.Fields("CMPNYNAM").Value)), "~", Trim(rsCompanies.Fields("INTERID").Value))
If intCompany = 1 Then
strView = strView & vbCrLf & strQuery
Else
strView = strView & vbCrLf & "UNION" & vbCrLf & strQuery
End If
rsCompanies.MoveNext
Wend

'Execute the CREATE VIEW statement
GPCommand.CommandText = strView
GPCommand.Execute

If blnGrant Then
'Grant permissions to DYNGRP
strQuery = "GRANT SELECT ON csvwAllVendors TO DYNGRP"
GPCommand.CommandText = strQuery
GPCommand.Execute
MsgBox "csvwAllVendors was created!"
Else
MsgBox "csvwAllVendors was updated!"
End If

End Sub


So we've made alot of progress! But...thinking about the bigger picture, what's wrong with this approach? It works for our custom view for vendors, but what if the company wants to have 20 different multi-company SmartLists? We certainly don't want to put 20 buttons on the company setup window, and we probably don't want to hard code all 20 queries into our code.

How could we use the design we have developed so far to accommodate an arbitrary number of custom multi-company views?

We'll discuss that in Part 3!

Any questions about what has been covered so far?