What makes excellent training?

I have spent the last couple of weeks training. First, training a Dynamics GP Distribution Partner Academy then a custom Forecaster training for a group from California. These were two very different classes, not only in content but also in structure. And next week I will be teaching a Dynamics GP Financials Partner Academy followed by co-training on Sure Step. So it has all gotten me thinking about what makes good (well, not just good...but excellent) training? What makes training valuable? This is the interesting thing about blogging, I get to just put my thoughts out there for all to share. So here are my thoughts, please feel free to share your own...



1. Training content must meet the needs of the students

Obvious, right? Sometimes this is out of your control, especially with non-custom classes like the partner academy. Sometimes students do not review the syllabus or enroll based on an assumption of what the class will cover. But the instructor also bears a burden to deliver the content in a relevant way, including real-life examples so that students can see how the content applies to their situations. Students can also contribute to this process, through their own experiences and ideas....which leads us in to my next point...

2. Students must learn from the instructor, but also from each other

I am only half joking on the first day of class when I emphasize the importance of students asking questions. Talking all day long can get pretty boring for me, and for the students. Only when students start to ask questions, and share experiences, does the class really begin to take off. Of course, a knowledgeable instructor is key to providing the forum and managing the exchanges; but I think the light bulbs really start to go off when students start explaining and relating concepts to each other. Here I go again, leading in to the next point...


3. Practical application of the knowledge is key (in the classroom, in discussions)

We can talk all day about what a particular field means, or how a particular process works in Dynamics GP. But it is the knowledge of how to apply those facts to real life situations that is critical. The ability to apply knowledge increases the value and life of the training to beyond the classroom. How can instructors contribute to this application? Maybe by sharing their own practical experiences, by bringing more than just the factual knowledge to the classroom, and by encouraging students to share their own practical experiences. I encourage this even beyond the classroom, asking students (particularly those new to GP) to continue to share their experiences with me after the class ends. I love getting the emails from students who have successfully completed implementations, or tackled a tough business process and found a creative solution.

4. Students and instructor must be engaged

Hmmm. Where to begin with this one. As an instructor, I have witnessed a variety of types of disengagement. The "I have too much work at my real job" disengagement, the "I was forced to come to training" disengagement, or even the "I am in denial" disengagement. I can completely identify with each type, and try to be proactive in the early hours of class to engage those individuals through gentle coercion to bring them in to the discussion. On the flip side, as a student, I have seen instructors who seem to want to be somewhere else. Or who are not engaged to the degree that they are unwilling to stray far from the printed word. As you probably suspect from my earlier points, I think that the most beneficial classes are those where all parties jump in feet first and contribute fully. This means that instructors are willing to entertain new ideas and approaches, and students are focused on the content being presented. Students and instructor both think critically about the task at hand, bringing their full mind to the classroom.

As a side note, I think a subset of the last point is that the instructor must be knowledgeable about the subject at hand. A bit basic, right? But, I think that is an aspect of being engaged. A fully engaged instructor is well-prepared for the class being taught and has the practical knowledge to provide a well-rounded learning experience.

So...what do you all think? What makes excellent training? What makes an excellent student? How about an excellent instructor? Share your thoughts!

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!

Project Accounting Ramblings

Okay, so I will admit that it is sometimes a lonely life as a trainer for Microsoft Dynamics GP Project Accounting as there does not seem to be many of us, and I would be hard-pressed to name two clients who use the module in the same fashion. In that fact lies the contradiction of GP Project Accounting, that it is indeed a focused module but it serves a variety of goals. In my time using, implementing, and training on the module, I have learned that thorough discovery is absolutely essential to a successful implementation. The discovery process, in my humble opinion, should include as many pre-purchase "reality" discussions as possible to ensure that the module is indeed a sufficient fit and proper expectations are set.





I do not want to come across as negative about the module, as it meets many requirements and can provide significant productivity gains. However, I think it is important to know the parameters you are working with so that they can be planned for and addressed during the design and development stages of an implementation as opposed to coming to light during training or in a setup session. Some key limitations I have found that can impact the satisfaction with the module:


1. Reporting capabilities: Project comes with a variety of standard reports, however I have found that many companies require some degree of customized reporting. I think this is related to the fact that people approach project accounting differently, and analyze data differently. Also, consider that the project accounting hierarchy of Customer/Contract/Project/Cost Category must, in turn, support the reporting that is required.


2. Milestone billing: Although this is not standard functionality, milestone billing can be accomplished by scheduling the fee amounts for billing. This can be a process adjustment for users, but can work nicely in many situations.


3. General Ledger reporting: In some cases, users want GL reporting by project. Although project does have a trial balance report, be careful about assuming other reports can be created easily that combine GL activity with project information. The links between GL and PA are a bit complicated, so you just need to plan for any GL reporting carefully. If users want FRx style reporting (and the associated flexibility) for projects, it often leads to a discussion regarding adding a segment in the GL for projects (the easiest, albeit not always the most desirable, solution).


4. Cost Category Transaction Usage: Cost categories in project accounting can only be used with one transaction type. This can cause issues when budgeting projects, as you might end up with one cost category TRAVEL-EE (for travel expenses on employee expense reports) and TRAVEL-PM (for travel expenses from outside vendors recording using the purchasing module). So in that case, the budget for travel would have to be divided between the two cost categories. Not an ideal situation for users who plan on using the cost budgeting functionality, but it is something that users do get used to over time. If PS Time and Expense (the Business Portal based time and expense entry tool for employees) is not being used, the issue can be easily addressed by recording both employee expenses and outside vendor purchases using the purchasing module rather than using project's employee expense entry window. However, if you are using PS Time and Expense, the expenses will automatically integrate with employee expense entry not the purchasing module.


5. Budget input and updates: Currently, this is a manual process per project. There is not an import tool, which is a common request. This is particularly true when the users want to interface project accounting with other project management systems. If it is a critical need, you want to accomodate customization/development of a tool in your implementation.




When these items come up in discovery, it is important that plans be made to address them. In some cases this may mean additional costs to the clients in terms of report development and customization, or it may mean a change in business process to better align with the software. In either situation, planning ahead can spare everyone frustration.






Some popular non-traditional project accounting uses include:


1. For law firms, accumulating costs on cases

2. Tracking costs and budgets for internal projects and activities (development, marketing, etc)

3. Tracking internal construction costs for building stores, etc.



Please share your thoughts and experiences with the project accounting, I would love to hear them!