Business Process Improvement with Conditional Process Holds in Dynamics GP Sales Order Processing

I was presented with a business process problem by a client related to managing Credit Limit overrides in Dynamics GP Sales Transaction Entry.  This company does not supply Order Entry users with the Credit Limit override password.  It's the Credit Manager's responsibility to decide whether or not to allow that override.

This client has been working for some time to generally route information to the right person that needs to make the decision and eliminate waste in their business processes.  During the normal course of business when users are presented with the "Please enter the credit limit override password:" prompt the Credit Manager is called to the user's workstation to make the decision on the fly.

This is a very inefficient process.  What a great opportunity for improvement!

To resolve this issue and get the information to the person responsible for making the decision while maintaining the system controls required we got creative with Process Holds, eConnect, and VBA with ADO.  Here's what we did:

First, we removed the "Exceed Credit Limit" password from Receivables Management Setup:

Now, users would have been presented with the more friendly prompt that would allow them to proceed without a password.

I don't claim to be a SOX expert but I'm guessing that wouldn't pass a SOX audit.  So, we added some VBA Code to the BeforeModalDialog Event behind the Sales Transaction Entry Window.  Of course, to make this work you would need to add that window plus the DocumentNo and the SOPTypeDatabase fields to your project.

The code calls the taSopUpdateCreateProcessHold eConnect stored procedure and adds the "CREDIT" Process Hold to the Document when the credit limit prompt appears and closes the dialog box by answering Continue to the prompt automatically.

Now, instead of stopping the user in their tracks to make this decision on the fly the Credit Manager can manage this Process Hold separate from the Order Entry Process.  To do this, we used SmartList Builder to build a SmartList that displayed SOP Documents On Hold.  Then, we added a Favorite with a Reminder to the SmartList to show only SOP Documents with the "CREDIT" Process Hold applied.

Now, when the Credit Manager logs into GP and periodically throughout the day he can quickly and easily analyze the Sales Orders for Customers that had exceeded their Credit Limit and manage them accordingly.

Get the right information into the hands of the person who is responsible for making the decision and you too can improve your business processes and efficiency.  It doesn't take much work or creativity to increase the ROI on your GP implementation using techniques such as this.

If you like this let me know.  If enough people do, maybe we'll produce a new freebie for Dynamics GP!

Don't accept what you're given, make it into what you need it to be.

Visual Studio Tools Integration with GP Security

After reading many blog posts, TK articles, forum threads, and the like I pieced together from many sources how to build Visual Studio Toolkit forms that integrate with Dynamics GP security so you don't have to hard code a connection string or any environment specific settings in your application or .config file.


You'll need to get the GP Connection Library (GPConn.dll) from GP Support.  You have to license the GP Connection object and obtain the keys.  This will enable you to get around the VS Toolkit only exposing the unencrypted password.  Once you have that you can simply use the code snippet below in your project to dynamically connect to the Dynamics GP database using the GP user credentials.

Dim resp As Integer


Microsoft.Dexterity.GPConnection.Startup()


' Create the connection object
GPConnObj = New Microsoft.Dexterity.GPConnection()


' Initialize
resp = GPConnObj.Init("key1", "'key2")


CompanyDB.ConnectionString = "DATABASE= " & Dynamics.Globals.IntercompanyId.Value
GPConnObj.Connect(CompanyDB, Dynamics.Globals.SqlDataSourceName.Value, Dynamics.Globals.UserId.Value,
Dynamics.Globals.SqlPassword.Value)



This is mostly straight from the documentation that accompanies the GP Connection Library from GP Support.  From that, it was easy and convenient to build the connection string using Dynamics.Globals.IntercompanyID for the database and SqlDataSourceName for the SQL Server Instance in the connection string.  The UserID and SQL Password need no explanation.  Now, you can compile your .dll and deploy it to any GP installation without having to worry about any environment specific settings.


I'm sure there are more elegant ways of doing this and this may been clearly documented elsewhere but I didn't find it teed up for me this way.  That would have saved me a little time.  I hope this helps you!

Quick Lookups with Modifier & VBA

It seems that the last few times I have implemented Contract Administration I've had to work around the inherent restrictions on the Contract Number field by using another field, in this case User Defined 1, to track an internal Customer Contract Number; in this example "Job Number". Maybe I'm missing something here but this is how I was able to work around this.

It's nice that you can move the User Defined 1 field onto the Contract Entry/Update window and add it to the Contract Lookup with Modifier in just seconds. This isn't as simple on some other windows in GP:




























The problem was that Contracts needed to be retrieved using this Job Number value. Depending on how you address that, it could have a much more significant impact on the project budget. In this case, the customer was just as happy entering the Job Number to retrieve the Contract Record as they would be if the field were added to the Find Function in the Contract Lookup window.

It's important to note in this example that Job Number uniqueness is forced across Contracts.

To do this in GP 10; first, create some Sub Procedures to make and close a connection to the database:

Option Explicit

'Declare variables for the ADO Connection
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

Public Sub Get_ADOConnection()

'Get_NewConnection
Set cn = UserInfoGet.CreateADOConnection
cn.DefaultDatabase = UserInfoGet.IntercompanyID
cmd.ActiveConnection = cn

End Sub

Public Sub Close_ADOConnection()

cmd.ActiveConnection.Close

End Sub

Then, add some code behind UserDefined1 on the Before User Changed event to force uniqueness of Job Number across Contracts:

Private Sub UserDefined1_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)

On Error GoTo Proc_Error:

If ContractNumber.Empty = False Then

'Get_NewConnection
Get_ADOConnection

'Make sure the job number is not already assigned to a contract

cmd.CommandText = "select top 1 CONTNBR from SVC00600 where USERDEF1 = '" & Me.UserDefined1 & "'"
Set rst = cmd.Execute

'If a contact exists for this job notify the user and force them to enter a unique job number
If rst.EOF = False Then
MsgBox ("This Job Number has already been assigned to contract " & RTrim(rst.Fields("CONTNBR")) & ".")
CancelLogic = True
KeepFocus = True
End If

Close_ADOConnection

End If

GoTo Procedure_Exit:

Proc_Error:

MsgBox Error$ & " " & Error, vbOKOnly, "UserDefined1_BeforeUserChanged"

Procedure_Exit:

End Sub

Finally, add some code behind User Defined 1 on the After User Changed Event to query the Contract Master Table and retrieve the Contract to which the Job Number has previously been assigned:

Private Sub UserDefined1_AfterUserChanged()

On Error GoTo Proc_Error:

If ContractNumber.Empty = True Then

'Get_NewConnection
Get_ADOConnection

'Lookup the Contract for this Job
cmd.CommandText = "select top 1 CONTNBR from SVC00600 where USERDEF1 = '" & Me.UserDefined1 & "'"
Set rst = cmd.Execute

If rst.EOF = False Then
Clear = 1
ContractNumber = rst.Fields("CONTNBR")
Else
MsgBox ("This Job Number has not been assigned to a contract.")
End If

Close_ADOConnection

End If

GoTo Procedure_Exit:

Proc_Error:

MsgBox Error$ & " " & Error, vbOKOnly, "UserDefined1_BeforeUserChanged"

Procedure_Exit:

End Sub

The end result of this not only can you easily assign the Job Number (User Defined 1) to the Contract and view the Job Number in the Contract Lookup Window but, most importantly, you can pull the Contract into the Contract Entry/Update Window by keying into the Job Number field.

Simple, quick, cost effective solution that could be applied to a variety of windows in GP.

ADO Connections through VBA in Dynamics GP v9

This has come up time and time again on message boards I frequent on the web. People are always asking how they can access SQL Tables and other objects through VBA to extend GP. It's pretty simple to add a field to a window with Modifier or create a user form in VBA but many seem to wonder how to access the database to really make things happen.

You can find all you need to know on Customer/PartnerSource. I'm only disclosing the same information here and a little of what I have learned to complement that information.

When Microsoft Dynamics GP 9.0 was released, a change was made in the password policy. This change required changes in how Microsoft Dynamics GP Dexterity works with passwords and in how the RetrieveGlobals.dll file works with passwords. The RetrieveGlobals.dll file was available for all earlier versions but does not work with Microsoft Dynamics GP 9.0. Therefore, you must replace the RetrieveGlobals.dll file with the new RetrieveGlobals9.dll file. The new RetrieveGlobals9.dll file is an ActiveX file. It returns the following information:

•The current user ID
•The current company to which you are logged in
•The current SQL data source
•The current user date in Microsoft Dynamics GP 9.0

The RetrieveGlobals9.dll file also returns an ActiveX Data Objects (ADO) connection object that lets you connect to Microsoft Dynamics GP data. The RetrieveGlobals9.dll file works only with version 9.0 of Microsoft Dynamics GP. Additionally, this file works only if you have one session of Microsoft Dynamics GP running and if you are logged into this session. The RetrieveGlobals9.dll file is for use only in Modifier with VBA or in Integration Manager. Modifier with VBA and Integration Manager both require that Microsoft Dynamics GP is open and running. To download the RetrieveGlobals9.dll file together with its documentation, visit one of the Microsoft Web sites.

After upgrading all of our GP v7 VBA code to Dynamics GP v9 I found that I might have something valuable to share on this topic that you cannot readily find on CustomerSource. This is how I have organized my code to get best results from the RetrieveGlobals9.dll.

1. Create a Get_NewConnection SubRoutine behind each window for which ADO connections will be required:

Public Sub Get_NewConnection()
'This code clears all variables used and closes the connections.

If precordset.State = adStateOpen Then precordset.Close
If pconnection.State = adStateOpen Then pconnection.Close

'Initialize the connection string variables
Set userinfo = Nothing
Set userinfo = CreateObject("RetrieveGlobals9.retrieveuserinfo")
luserid = userinfo.retrieve_user()
lintercompanyid = userinfo.intercompany_id()
lsqldatasourcename = userinfo.sql_datasourcename()

'Use the connection property to get a connection object.
Set pconnection = userinfo.Connection

'Create an ADO command object.
Set cmd = CreateObject("ADODB.Command")

'set the database to the currently logged in db.
pconnection.DefaultDatabase = lintercompanyid

cmd.ActiveConnection = pconnection

'adCmdText.
cmd.CommandType = 1

End Sub

2. Create a CloseConnection SubRoutine behind each window you create a Get_NewConnection:

Public Sub CloseConnection()

'This code clears all variables used and closes the connections.
If precordset.State = adStateOpen Then precordset.Close
If pconnection.State = adStateOpen Then pconnection.Close
Set cmd = Nothing
Set pconnection = Nothing

End Sub

3. Call the Get_NewConnection to create new connections and CloseConnection to close them. I found that on some windows you can put these calls on the After Open and After Close events. On others you will have to reinitialize the connections and close them for every new call you make to the database.

4. Here's an example of how we populate a checkbox placed on EFT Customer Maintenance with Modifier based on whether or not the customer's pre-note has been confirmed which is stored in a table called "Dealer" outside of Dynamics GP.

Private Sub CustomerID_Changed()

On Error GoTo Proc_Error:

Get_NewConnection

cmd.CommandText = "SELECT PreNoteStatus FROM " & lintercompanyid & ".dbo.Dealer where AccountNumber = '" & CustomerID & "'"

Set precordset = cmd.Execute

If precordset.EOF = False Then
If RTrim(precordset.Fields("PreNoteStatus")) = 1 Then
PreNoteConfirmed = 1
Changed = False
Else
PreNoteConfirmed = 0
Changed = False
End If
Else
PreNoteConfirmed = 0
Changed = False
End If

GoTo Procedure_Exit:

Proc_Error:

MsgBox Error$ & " " & Error, vbOKOnly, "CustomerID_Changed"

Procedure_Exit:

CloseConnection

End Sub