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!