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?

Multi-Company SmartLists in GP - Part 1

1 Overview

This series of posts will show you how to use SmartList Builder and some custom scripts to view data from multiple companies in a single SmartList. It will cover multi-database views, custom SmartLists, SmartList security, and even automatically updating the multi-company views when a new company database is added.

This solution is probably most valuable to corporations that have many GP company databases, but have a centralized accounting department that enters transactions for all companies.

Ultimately, this is a technical solution that will be fairly straightforward for individuals that are comfortable writing moderately complex SQL queries and are comfortable working with Visual Basic Script. I’ve tried to explain each step so that you don’t have to be a SQL guru or a full programmer to implement this solution, but it does discuss several concepts that will be more familiar to a person with a more technical skill set.


2 Scenario

Suppose Consolidated Property Corporation, a rapidly growing property management firm, has 12 different property management subsidiaries across the US. Each subsidiary has as a separate company database in GP, but all accounting is processed at their corporate headquarters in Denver, Colorado. Consolidated Property expects to add 10 new subsidiaries over the next year, and will therefore be adding 10 more GP company databases. 2 of the current 12 companies are leasing firms, providing different services than the other 10 companies.

Consolidated Property Corporation has national contracts with some vendors for building maintenance services, such as commercial roofing, air conditioning repair, parking lot maintenance, window washing, and office cleaning. They also have customers that hire Consolidated to manage multiple properties in multiple states, such as national commercial property firms.

Consolidated wants to be able to view data from their 10 main property management subsidiaries in a single SmartList. They do not want to include data from their 2 leasing firms in the SmartList.

Consolidated offers a story to explain the solution they are looking for:

Susan, the AP clerk at the corporate office receives a call from Crystal Window Washers inquiring about an outstanding invoice. Because all invoices are mailed to the corporate offices, the Crystal Window Washers collections agent does not know which Consolidated property the invoice was for—he only knows the invoice number and amount. Since Susan does not know which GP company to log into to look for the invoice, she would normally have to login to each GP company to try and find the vendor and then the invoice.

Using a multi-company Vendor SmartList, Susan would be able to search for Crystal Window Washers and see that they are setup as a vendor in only 2 different GP databases—San Diego and Los Angeles. She could then use the multi-company AP Invoice SmartList to search by invoice number see that the invoice was entered into the San Diego company. She could then log in to the San Diego company and research the transaction.


3 Requirements

In the scenario I’ve outlined, the custom SmartLists need to provide the following:

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


4 Solution

This solution will require several different elements to meet the requirements. I’ll discuss the first element of the solution in this post, and will discuss the other elements in subsequent posts in this series.


4.1 Multi-company queries

The core requirement is to display data from multiple GP databases in a single SmartList. With SQL Server and Dynamics GP, this is fairly easy to do. It all rests on the "UNION" statement.

Let's start with vendors, since that is an easy query. If you were to query vendors in GP, you would launch SQL Server Management Studio, connect to the TWO database, and write a query like:

SELECT * FROM PM00200

This would list all vendors in the TWO database. But knowing that we need data from multiple companies, let’s be more explicit with our query syntax:

SELECT * FROM TWO.dbo.PM00200

Since I don’t like to type any more than I have to, this query can also be written as:

SELECT * FROM TWO..PM00200

So this gives us a list of all vendors in TWO. If we wanted all vendors from the Los Angeles (California database #4) database, we would write:

SELECT * FROM CA001..PM00200

And San Diego and Denver:

SELECT * FROM CA004..PM00200
SELECT * FROM CO001..PM00200

So now we have separate lists of vendors from each company:

SELECT * FROM CA001..RM00101
SELECT * FROM CA004..PM00200
SELECT * FROM CO001..PM00200

But, we need to get them to display together in a single query, so naturally, we use the UNION statement, which neatly combines all of the results from our 3 different queries into a single result set:

SELECT * FROM CA001..PM00200
UNION
SELECT * FROM CA004..PM00200
UNION
SELECT * FROM CO001..PM00200

Great! But what’s wrong with this new query that combines the list of vendors from all 3 companies? How can you tell which company any given vendor is from? To solve this, let’s add a company field:

SELECT 'Los Angeles' AS Company, * FROM CA001..PM00200
UNION
SELECT 'San Diego' AS Company, * FROM CA004..PM00200
UNION
SELECT 'Denver' AS Company, * FROM CO001..PM00200

Much better! Now we can see which database every vendor record came from.

I think you can see where this is going—just create similar queries for the 7 other GP companies, and you now have a single list of vendors for all 10 of the property management companies.

Pretty simple, right? So now that you have this query, how do you turn it into a SmartList? The easiest way that I’ve found is to turn the query into a custom “view” in SQL Server. This is quite simple:

CREATE VIEW csvwAllVendors AS
SELECT 'Los Angeles' AS Company, * FROM CA001..PM00200
UNION
SELECT 'San Diego' AS Company, * FROM CA004..PM00200
UNION
SELECT 'Denver' AS Company, * FROM CO001..PM00200
GO

Now, instead of having a large, complex looking query, we can simply query:

SELECT * FROM csvwAllVendors

Now that you have created a view, you can use SmartList Builder to create a new SmartList that points to your custom view, just as if it were a database table.

Bonus Quiz: For you technical database folks out there, what step have I omitted near the end? Hint: It will prevent you from adding the custom view to a new SmartList in SmartList Builder.

I’ll share the answer in Part 2!