In the first part of this series, we discussed a scenario where a centralized accounting team could benefit from SmartLists that query data from multiple Dynamics GP company databases. We then covered the fundamentals of creating multi-company views as the foundation for these SmartLists.
Before continuing, let’s address the Bonus Quiz from Part 1. The missing step to enable adding a custom view to a new SmartList in SmartList Builder involves SQL Server database security settings. After creating a custom object in a Dynamics GP database, you must grant access to it for Dynamics GP users. Using a script is the most efficient method for granting these permissions.
In our case, we created a view named “csvwAllVendors” (following a naming convention where “cs” represents Custom and “vw” stands for View). It’s recommended to store this view in the Dynamics database for central access.
To grant access to all Dynamics GP users, execute the following script:
GRANT SELECT ON DYNAMICS..csvwAllVendors TO 'DYNGRP'
Now the view will appear in the SmartList Builder.
Returning to our main objective, let’s recap the requirements:
- Create a single SmartList accessible from any GP company that displays data from multiple GP databases.
- Display the source database name for each record.
- Automatically include data from newly created GP company databases.
- Allow exclusion of specific companies from these multi-company SmartLists.
We’ve already addressed displaying data from multiple databases and the company name for each record.
To dynamically include data from new company databases, we’ll employ a creative VB scripting approach. Instead of hardcoding database names, we’ll use placeholders in a generic query:
SELECT '^' AS Company, * FROM ~..PM00200
Here, “^” represents the company name, and “~” stands for the database name. The goal is to substitute these placeholders with actual company database names.
We can leverage Dynamics GP’s company database list:
SELECT INTERID, CMPNYNAM FROM DYNAMICS..SY001500
Then, using VBA, we can combine this list with our generic query, performing a search and replace to generate the multi-company view:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
| 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 utilizes the UserInfoGet object for GP 10. GP 9 requires the RetrieveGlobals9.dll.)
This code snippet can be incorporated into a button event to create the multi-company view.
However, this script needs a check to prevent errors when the view already exists. We can add a check for the view’s existence:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
'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
'\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
|
To exclude specific companies, we can utilize a User Defined field in the Company Setup window. By setting User Defined 1 to “MULTICOMPANY VIEWS,” we can adjust the company query to include only companies with this value:
SELECT INTERID, CMPNYNAM FROM DYNAMICS..SY01500 WHERE UDCOSTR1 = 'MULTICOMPANY VIEWS'
Combining these improvements results in the following script:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
| 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
|
While this approach works, it has a limitation. It’s not scalable for a large number of multi-company SmartLists. We wouldn’t want to clutter the company setup window with buttons or hardcode numerous queries.
Part 3 will explore how to adapt this design to accommodate an arbitrary number of custom multi-company views dynamically.