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.

Get the Next NOTEINDX

This came up for me 2x this week alone on 2 different projects. Just about every time you have to insert a record into a GP Table you have to get the next note index. While it's a simple thing, it may not be obvious exactly how to do it.

Run this against your COMPANY database to increment and return the next note index from the DYNAMICS.dbo.SY01500 table:

--Declare some variables
DECLARE @I_sCompanyID smallint,
@O_mNoteIndex numeric(19,5),
@O_iErrorState int

--Get the CompanyID
select @I_sCompanyID = CMPANYID
from DYNAMICS..SY01500
where INTERID = DB_Name()

--Get and increment the next note index
exec DYNAMICS.[dbo].[smGetNextNoteIndex] @I_sCompanyID, 1, @O_mNoteIndex output, @O_iErrorState output

--Print the Next Note Index
print(@O_mNoteIndex)
--Print the Error State
print(@O_iErrorState)

Forming, Storming, Norming, Performing Group Development Theory

This is off topic but relevant for many, if not all of us, as we are integrating into or managing new teams. As a consultant, this is very common.

New teams generally form and evolve through a distinct set of stages; Forming, Storming, Norming, and Performing. The goal is to move to the Performing stage as quickly as possible. I'm currently tasked with managing a very diverse team that is not accustomed to working together. As a result, I'm spending a lot of time working to help them move along the path to high performance as quickly as possible.

Mind Tools has a good article posted that describes these stages with some tips on how to lead a team through them effectively to high performance. If you find yourself struggling to integrate with a new team or building a high performance team of your own you might find this useful.

GP Self Service - Locked Transactions

I neglected to mention another potential good reason to Customize GP; control your support costs, increase your IT resource capacity, and increase user satisfaction. There are routine problems that are easily resolved but often require the assistance of a system administrator. For example, records locked by other users, orphaned TempDB.dbo.DEX_LOCK records, can be a nuisance. This typically grows more common as the concurrent user count increases in Citrix environments. These records are necessary to prevent multiple users from accessing the same transactions at the same time. However, when they are not properly cleared they prevent users from accessing transactions even though another user may not be.

In this example, I added a Push Button to Sales Transaction Entry using Modifier.

Then, I created 2 stored procedures; one to return the userid that has locked the transaction so the user can attempt to resolve this problem on their own ...

... and the other to remove the locking record.


Next, add the Sales Transaction Entry Window, the Document No. field, and the Unlock Trx button to your VBA Project.

Open the VBA Editor to set a Reference in the Microsoft_Dynamics_GP Project to the Microsoft Active X Data Objects 2.x Library. This is required to access the database directly through VBA.

Finally, paste the following code behind the Sales Transaction Entry Window. If some pieces of this are foreign to you try to take advantage of Mariano's VBA workshop this week to learn more and check back as I take a deeper dive in the future into some of these concepts. My apologies for the formatting of this code. I'll work on that.

Private Sub UnlockTrx_AfterUserChanged()

'Declare the variables for the message boxes
Dim Msg, Style, Title, Help, Ctxt, Response, MyString, Default

'Declare the variables to make the connection to the database
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

On Error GoTo Proc_Error:

'Prompt the user for the Document Number they want to unlock
Msg = "Enter the Document Number you want unlocked." ' Set prompt.
Title = "Unlock SOP Trx" ' Set title.
MyString = InputBox(Msg, Title, Default)

'Set the variables required to make the connection
Set cn = UserInfoGet.CreateADOConnection
cn.DefaultDatabase = UserInfoGet.IntercompanyID
cmd.ActiveConnection = cn

'Query the database to get user that has the document locked
cmd.CommandText = "exec uSp_SELLockedDocUser '" & Microsoft_Dynamics_GP.SalesTransactionEntry.DocumentNo & _
"', '" & cn.DefaultDatabase & "', 'SOP10100'"
Set rst = cmd.Execute
If rst.EOF = False Then
'Inform the user and give them the option to unlock the document or cancel
Msg = "This document is locked by " & (RTrim(rst.Fields("userid"))) & ". Continue Unlocking?"
Style = vbOKCancel + vbCritical ' Define buttons.
Title = "Dynamics GP" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
'Unlock the document if requested by the user
If Response = vbOK Then
cmd.CommandText = "exec uSp_DELClearLockedDoc '" & Microsoft_Dynamics_GP.SalesTransactionEntry.DocumentNo & "', '" & _
cn.DefaultDatabase & "', 'SOP10100'"
Set rst = cmd.Execute
End If
Else
'Inform the user that the record is not locked
Msg = "This document is not currently locked by another user."
Style = vbOKOnly + vbInformation ' Define buttons.
Title = "Dynamics GP" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
End If

GoTo Procedure_Exit:

Proc_Error:

MsgBox Error$ & " " & Error, vbOKOnly, "UnlockTrx_AfterUserChanged"

Procedure_Exit:

End Sub

Simple Table Backups with T-SQL

I was working on a project recently with a very experienced and respected GP consultant. He has taught me many things but I was able to show him something very simple that made his life much easier. Simple table level backups with T-SQL.

The following will select all of the data in SOP10100 into a new SOP10100_Bkup_03012009 table:

select *
into SOP10100_Bkup_03012009
from SOP10100

Remember that inserts, updates, and deletes often fire off events that could alter data in other tables. This won't backup those dependent tables so be mindful of the potential that a simple update to one table could affect data on many others.