Rejecting duplicate checks during Bank Transactions import with Integration Manager

One of the interesting things about checkbooks setup in Microsoft Dynamics GP is that you have the ability to prevent duplicate checks from being issued in the Payables Management module, yet those same controls do not apply if you are entering checks in the Bank Transaction Entry window.

Checkbook Maintenance window - Payables Options

This can certainly be an issue if those check numbers happen to be integrated from a line of business application as a bank transaction in the Financial module.

Bank Transaction Entry window

This was certainly the case for a partner who was integrating a number of transactions from a line of business application into Microsoft Dynamics GP and required to implement a control to prevent duplicate check numbers from being integrated.

Integration Manager scripting capabilities proved to be very helpful here. By adding some VBScript to the Before Document event script, we can check to see if the check number being integrated exists in the CM Transactions table (dbo.CM20100) prior to allow the integration to commit the record in Microsoft Dynamics GP.

Before Document script

'
' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons 
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.

Const adUseClient     = 3
Const adCmdStoredProc = 4
Const adCmdText       = 1

Dim oCn, oCmd, oRs

Set oCn = CreateObject("ADODB.Connection")
With oCn
  .ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
  .CursorLocation = adUseClient
End With

GPConnection.Open(oCn)

' Prepare the SQL statement and retrieve the next voucher number

Set oCmd = CreateObject("ADODB.Command")
With oCmd 
 .ActiveConnection = oCn
 .CommandType = adCmdText
 .CommandText = "SELECT * FROM CM20100 WHERE CMTRXNUM = '" & _ 
    CStr(SourceFields("sourceQry.CheckNumber")) & _ 
    "' AND CHEKBKID = 'FIRST BANK';"
 Set oRs = .Execute
End With

If Not oRs.EOF Then
' This is a duplicate check
   CancelDocument "Duplicate Check Number for checkbook FIRST BANK: " & _ 
     CStr(SourceFields("sourceQry.CheckNumber"))
End If
oRs.Close
oCn.Close

Set oCmd = Nothing
Set oCn = Nothing

A few things to note:

The CancelDocument function is used to reject the record if it's found in the database. We can also add a simple message to be written to the integration log file describing the check number found to be a duplicate.

You can optimize this code by opening a connection to the database and storing the connection in a global variable in the Before Integration event script, rather than having to open and close the connection several times as I do here. Likewise, you can close the connection in the After Integration script.

The bottom line, nonetheless, is to show a simple technique for record control and rules implementation that help the partner and the customer overcome the issue they were having.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Getting the Next Voucher Number for a Payables Transaction Integration

At the beginning of the year, I wrote a 2-part series on retrieving document numbers assigned by Microsoft Dynamics GP when a field rule is set to Use Default in Integration Manager, see:

IM - Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager - Part 1

IM - Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager - Part 2

Back then, I was addressing an issue faced by many working with integrations that require you to pass back some value to a source system and the complexities involved in retrieving those values, especially when the field rule is set to Use Default, this is, Microsoft Dynamics GP is assigning the field value, not the integration (via source file or otherwise).

Today, I am looking at a slightly different issue, nonetheless, related.

In this occasion, the client wanted to retrieve the next voucher number before hand for a payables transaction integration and assign it to the voucher number field, but did not want to have to write their own script. In essence, they wanted to leverage whatever mechanism built already in Microsoft Dynamics GP's business logic to get the next voucher number.


Payables Setup Options - Next Voucher Number field

 Indeed, writing their own code would involve retrieving the field value and incrementing the numeric part of the string. This sometimes can be a nightmare, especially when having to deal with record collisions and users accessing the system while the integration was running.

In doing some additional reading, I realized that eConnect already included this method, so all I had to do was find the SQL Server stored procedure to get the next voucher number. That stored procedure is conveniently named taGetPMNextVoucherNumber. One down, 2 more to go!

The second challenge with eConnect stored procedures is to determine the parameters that need to be passed in, but all eConnect stored procedures are created with encryption, so editing them was not an option. However, I remembered that in SQL Server Management Studio, you have the ability to execute a stored procedure from the Management Studio UI and that this would in effect display a window with the parameters, furthermore detailing data types and whether they are input or output type parameters.

Execute Stored Procedure option (Right-click)

Execute Procedure window

The good thing about this window is you can enter values for your input parameters and click on OK, and SQL Server will automatically generate a template for executing the stored procedure, with variable declarations, types, and all. The construct looks something like this:

USE [TWO]
GO

DECLARE @return_value int,
  @O_vCNTRLNUM varchar(21),
  @O_iErrorState int

EXEC @return_value = [dbo].[taGetPMNextVoucherNumber]
  @O_vCNTRLNUM = @O_vCNTRLNUM OUTPUT,
  @I_sCNTRLTYP = 0,
  @O_iErrorState = @O_iErrorState OUTPUT

SELECT @O_vCNTRLNUM as N'@O_vCNTRLNUM',
  @O_iErrorState as N'@O_iErrorState'

SELECT 'Return Value' = @return_value
GO

This was fantastic, because now I did not have to struggle with understanding what needed to be passed in. It so happens that the control type parameter, @I_sCNTRLTYP, requires a zero to retrieve the next voucher number. In essence, I played with the parameter value and compared to what I was seeing in the GP interface (above), so here are the parameter values accepted for control type:

0 - Next Voucher Number
1 - Next Payment Number
2 - Next Alignment Number

Two down, 1 more to go.

Finally, the rest is putting the scripts together in Integration Manager to call the stored procedure.

As a best practice,  I tend to make the connections to the database persistent throughout the integration. This assures me that connections are only opened once, and closed at the end of the integration, improving the overall performance of the integration and reducing the points of failure. So, as you can imagine, a before document or a field script aren't the places to open and close connections, as these events occur over and over, based on the number of records being integrated.

I typically open the connection in the Before Integration event script, so this is what this script looks like:

' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons 
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.
' 
' Persisting ADO connection

Const adUseClient = 3
Dim oCn

Set oCn = CreateObject("ADODB.Connection")
With oCn
  .ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
  .CursorLocation = adUseClient
End With

GPConnection.Open(oCn)
SetVariable "gblConn", oCn

Note that the connection object is stored in a global variable, gblConn, using the SetVariable statement in Integration Manager.

Once we have the connection piece sorted out, now we can focus on adding code to the Voucher Number field script to call the eConnect stored procedure, as follows:

'
' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons 
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.

' Prepare the SQL statement and retrieve the next voucher number
Const adCmdStoredProc = 4
Const adVarchar       = 200
Const adInteger       = 3
Const adParamInput    = 1
Const adParamOutput   = 2
Const PMVoucher       = 0

Set oCmd = CreateObject("ADODB.Command")
With oCmd 
 .ActiveConnection = GetVariable("gblConn")
 .CommandType = adCmdStoredProc
 .CommandText = "taGetPMNextVoucherNumber" 'the eConnect stored proc
 
 .Parameters.Append .CreateParameter ("@O_vCNTRLNUM", adVarchar, adParamOutput, 25)
 .Parameters.Append .CreateParameter ("@I_sCNTRLTYP", adInteger, adParamInput)
 .Parameters.Append .CreateParameter ("@O_iErrorState", adInteger, adParamOutput, 4)
 
 oCmd.Parameters("@I_sCNTRLTYP").Value = PMVoucher 
 .Execute 
 NextVoucher = oCmd.Parameters("@O_vCNTRLNUM").Value
 CurrentField.Value = NextVoucher
 'MsgBox NextVoucher
End With

Set oCmd = Nothing

Note how in this occasion, we are using the GetVariable function to retrieve a pointer to the connection object stored in the global variable. We then access the Parameters object to add the different parameters and set the value for the input parameter to the stored procedure.

When this script is executed within the context of the integration, it effectively returns the next voucher number for the transaction being integrated, from which you can proceed to update this information in your source system, if needed.

Note that by using standard Microsoft Dynamics GP business logic, your integration can now be supported if you need to open a support incident. Indeed another method for retrieving a document number for your transaction.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

"Object Reference Not Set" error when running Integration Manager with eConnect Adapter

I have seen a number of forum posts around this subject and have even received a few calls for help in troubleshooting the issue. In the occassions I have assisted someone, I have noticed that most of the time the developer or consultant was using an event or field script of some kind, which almost always attempts to get some information from Microsoft Dynamics GP.

So, in an attempt to reproduce the problem, I have recreated the following VBScript based on a recent case:

' Created by Mariano Gomez, MVP

'  This code is licensed under the Creative Commons 

'  Attribution-NonCommercial-ShareAlike 2.5 Generic license.



Dim objConn, objRec, cmd, sJE

 



set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionString = "database=" & GPConnection.GPConnInterCompanyID

GPConnection.Open(objConn) 

 



Set cmd = CreateObject("ADODB.Command")

cmd.ActiveConnection = objConn

 

cmdString = "SELECT NJRNLENT FROM GL40000;" 

Set objRec = objConn.Execute(cmdString)



if Not objRec.Bof and Not objRec.Eof then

  objRec.MoveFirst

  CurrentField = objrec.fields(0).value

end if

  

'Close recordset when finished

Call objRec.Close

  

'Close connection when finished

Call objConn.Close

 

Set cmd = Nothing

Set objConn = Nothing

NOTE: This script purposefully contains errors and does not follow best practices. It was recreated to illustrate the issue on the subject.

In summary, the above script was added by the consultant to retrieve the next journal number for a GL Transaction integration with the eConnect Adapter. The consultant reported the script working on and off on the server and not working on the workstations. However, in each case the error reported by Integration Manager is as follows:

Opening source query...

Establishing source record count...

Beginning integration...



DOC 1 ERROR: Error Executing Script 'GLTransaction.Journal Entry#' Line 9: - 

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Integration Failed

Integration Results

    1 documents were read from the source query.

    1 documents were attempted:

        0 integrated without warnings.

        0 integrated with warnings.

        1 failed to integrate.

The error indicates the is a problem with the data source name not being found, which leads to an object reference problem when the connection is attempted. But why would this code work on the server at times and not work on the client? Then it hit me!

The GPConnection object retrieves the connection and login information for the user currently signed on to Microsoft Dynamics GP... and therein lies the issue! The GPConnection object actually requires the Microsoft Dynamics GP user interface to be active for the object to retrieve the connection information, which is typically not the case for eConnect Adapter-based integrations.

As a side note, the times the integration did work, the user interface HAD to be active, but this was not apparent to the consultant.

So, how can we adjust this integration to follow best practices and work without the Microsoft Dynamics GP user interface having to be active?

The answer is relatively simple. The above code will need to switch out the way it obtains the connection string for an actual (as in hardcoded) connection string.

'

objConn.ConnectionString = "Provider=SQLNCLI10;Server=yourSQLServerName;_

Database=YourCompanyDB; Trusted_Connection=yes;" 

Because the script uses a trusted connection to the database (a best practice), it is advisable that proper permissions be granted to the user's domain account on SQL Server in order for the integration to be successful. The domain account will also need to be added to the DYNGRP role. What many customers have done is created specific domain accounts to execute eConnect integrations under a trusted connection. This further limits the exposure to security breaches.

For a final look at a technique to implement the above script, see the following article on this site:

Integration Manager: Integrating journal entries with Analytical Accounting Information

Hope you found this post useful.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

IM - Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager - Part 2

Part 2 of 2 - Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager.

---

In the previous installment, I explained the technique that I otherwise use to relate source systems records with imported records in Microsoft Dynamics GP for which a Use Default field rule has been set for the key field. This article demonstrates the key event scripts needed to obtain the record.

Before Integration event script

'
' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons 
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.

' Secure a connection against the company database we will be running
' the integration against.

Const adUseClient = 3

set oCn = CreateObject("ADODB.Connection")
oCn.ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
oCn.CursorLocation = adUseClient

GPConnection.Open(oCn)

' Make sure the connection is valid
If (oCn.Status = 1) Then
   ' Setup global connection variables
   SetVariable "gblConn", oCn   
   SetVariable "gblInterID", GPConnection.GPConnIntercompanyID
Else
 CancelIntegration
End If

The Before Integration event script will allow us to secure a connection against the company database we will be running our integration against. By opening the connection in this event script, we will avoid having to open a connection for each transaction being integrated, further on, creating any loss of performance in the process. It is important to highlight that we need to save the successful connection to a global variable, to be able to use it in other event scripts. In this case, we will assign the oCn connection variable to a global variable, gblConn, using the SetVariable statement.

Once Integration Manager has integrated the document, we will use the After Document event script to retrieve the record integrated. At this stage, we are assuming that the mapping of source fields to destination fields provisions a user-defined field or description field for the source key field. In the past, I have also used note fields to store these key fields when it has not been feasible to use a standard Microsoft Dynamics GP field.

Note: the After Document event script will only execute upon success of the document being integrated. If Integration Manager is unsuccessful integrating the document, the Document Error script will execute instead. This event can be used to report failure to the source system, which may facilitate new attempts to integrate from the source system by reporting different event statuses.

After Document event script

'
' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons 
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.

' Prepare the SQL statement and retrieve the assigned Sales Transaction number
Set oCmd = CreateObject("ADODB.Command")
With oCmd
 .ActiveConnection = GetVariable("gblConn")
 .CommandType = adCmdText

 .CommandText = "SELECT SOPNUMBE FROM SOP10106 WHERE USERDEF05 = '" & SourceFields("mySourceQry.KeyField") & "'"
  Set oRst = .Execute

 If Not oRst.EOF Then
                SopNumber = oRst!SOPNUMBE
 End If
 oRst.Close
End With  

' From here on you can open a connection to your source system and update the 
' some status flag and the column provisioned to track the GP document number

I hope you find this technique useful. Of course, this is a technique I have been using over the years. I would like to find out from you what methods you have used to accomplish the same.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

Integration Manager: Integrating journal entries with Analytical Accounting Information

My good friend, David Musgrave, somehow manages to get me involved in interesting topics circulating in his inbox. Just recently, he came across a fairly long thread among his peers, needing to work out some Integration Manager issues for journal entries with Analytical Accounting information. David was kind enough to involve me, as I had posted an answer on the newsgroups a long time ago on this same issue.

If you are one of the fervourous Integration Manager fans out there and have had to work on integrating journal entries with Analytical Accounting information, you may know this is only possible with the eConnect Adapter, not the Standard Adapter.

The eConnect Adapter was introduced with Integration Manager version 10, and replaces the old SQL Optimized Adapter available in prior versions of Integration Manager. The eConnect Adapter in turn, leverages eConnect components to deliver a robust transactional environment for high volume integrations using ADO.NET to access Microsoft Dynamics GP company databases.

eConnect Adapter - Journal Entry# field with Use Default rule value

However, the eConnect Adapter, though, while it provides a Use Default rule value for the Journal Entry# field, this setting causes the integration to fail, as eConnect (the component) requires a value to be supplied, this is, the actual journal number.

Of course the question now is, how do you retrieve the next journal number from your company database to supply this value to the Journal Entry# field to allow the integration to be successful and thereby, preventing you from having to manually reserve ? The answer is, scripting, of the VBScript type.

You can add VBScript code to the Before Document event script in Integration Manager to retrieve the next journal number from your company database, as follows:

' Created by: Mariano Gomez, MVP
' This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.5 Generic license.
Option Explicit

Const adCmdStoredProc = 4
Const adParamInput = 1
Const adParamOutput = 2
Const adParamInputOutput = 3
Const adInteger = 3
Const adVarchar = 200
Const adBoolean = 11
Const adChar = 129
Const adDate = 7
Const adNumeric = 131

Dim SqlStmt
Dim objConnection, objCommand, NextJournal



Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")

objConnection.Open _
    "Provider=SQLNCLI10;Server=MGB001\GP11;Database=TWO; Trusted_Connection=yes;" 

With objCommand
 .ActiveConnection = objConnection
 .CommandType = adCmdStoredProc
 .CommandText = "glGetNextJEWrapper" 'our wrapper stored proc
 
 .Parameters.Append .CreateParameter ("@IO_iOUTJournalEntry", adInteger, adParamOutput, 4)
 .Parameters.Append .CreateParameter ("@O_iErrorState", adInteger, adParamOutput, 4)
 
 .Execute 
 NextJournal = objCommand.Parameters("@IO_iOUTJournalEntry").Value
End With

SetVariable "gblJournal", NextJournal

Set objCommand = Nothing
Set objConnection = Nothing

The above code calls the stored procedure dbo.glGetNextNumberWrapper, which leverages the existing Microsoft Dynamics GP's dbo.glGetNextJournalEntry stored procedure to retrieve the next journal number, stored in the dbo.GL40100 (General Ledger Setup) table. As this is a call to a standard Microsoft Dynamics GP stored procedure, we are avoiding the use of custom code to retrieve the journal number and increment the value at the same time.

It is also necessary to note that the above code uses a Trusted Connection to connect to the company database. You can change the connection string as you see fit, just keep in mind that if you are going to use a SQL login, it cannot be a Microsoft Dynamics GP user login as the password for these logins are encrypted on SQL Server.

The following is the code for the dbo.glGetNextNumberWrapper stored procedure called by the Before Document script:

IF OBJECT_ID('dbo.glGetNextJEWrapper') IS NOT NULL
 DROP PROCEDURE glGetNextJEWrapper;
GO
CREATE PROCEDURE glGetNextJEWrapper 
 @IO_iOUTJournalEntry int OUTPUT,
 @O_iErrorState int OUTPUT
AS
DECLARE @l_tINCheckWORKFiles tinyint = 1, @I_iSQLSessionID int = USER_SID(), @O_tOUTOK tinyint;

IF @IO_iOUTJournalEntry IS NULL 
 SET @IO_iOUTJournalEntry = 0

EXECUTE glGetNextJournalEntry 
   @l_tINCheckWORKFiles
  ,@I_iSQLSessionID
  ,@IO_iOUTJournalEntry OUTPUT
  ,@O_tOUTOK OUTPUT
  ,@O_iErrorState OUTPUT
GO
GRANT EXECUTE ON glGetNextJEWrapper TO DYNGRP;

For more information on connection strings, visit http://www.connectionstrings.com/. Also, check the following article over at Developing for Dynamics GP on why does Microsoft Dynamics GP encrypts passwords.

Once the Before Document event script is implemented, you can then add a small field script to the Journal Number field to retrieve the value stored in the gblJournal Integration Manager global variable, as follows:

' Created by: Mariano Gomez, MVP
' This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.5 Generic license.
CurrentField.Value = GetVariable("gblJournal")

Integration Manager has great import capabilities when combined with the power of scripting and when you have a clear understanding of the underlaying technologies that support it.

Please enter your comments on this article or any methods you have used in the past to overcome similar issues.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Edits:
05/25/2011 - fixed IM global variable name as it was not matching between the Before Document script and the Journal Number Field Script, causing nothing to be assigned to the field and erroring out the integration.

Read More

IM - How to group Integration Manager transactions based on transaction date

Just recently, I fielded a question where the user wanted to group a number of customer invoice transactions in a file based on the invoice date. The file happened to contain invoices downloaded from the billing system in a date range, for example 02/01/2010 - 02/05/2010. They wanted the resulting transaction batches in GP to be something like,

AR02012010
AR02022010
AR02032010
AR02042010
AR02052010

This would facilitate analysis by day and to make sure control totals matched those created on a daily basis.

---

The solution involves adding a field script to the batch ID field which in turn reads the transaction date field from the source query and forms the new batch ID with that information:




Very simple, but useful script. Keep in mind that you must enable the option to Add Missing batches for the integration as shown below:



When the integration is executed, it will create as many AR batches in Microsoft Dynamics GP as unique transaction dates there are in the file.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

IM - How to filter source query records dynamically

Ever wanted to give users the ability to limit source records dynamically? How about being able to filter records from in a source query by a date range? Well, I encoutered this situation working on my current project.

Out of the box, Integration Manager offers the ability to retrieve records and set static filters to source records. However, in many cases users may want to dynamically (at runtime) establish a date range or any other range parameter for the records, then have these ranges applied to the source query.

This is possible by using some old fashioned VBScript with Integration Manager. Consider the following records:

DOCUMENT_DATE JOURNAL_NUMBER ACCOUNT_NUMBER DEBIT_AMOUNT CREDIT_AMOUNT
12/01/2009 100  000-1200-00 100.00  0.00
12/01/2009 100  000-6620-00 0.00  100.00
12/15/2009 200  000-1201-00 22.50  0.00
12/15/2009 200  000-6630-00 0.00  22.50
01/06/2010 300  000-1200-00 120.00  0.00
01/06/2010 300  000-6620-00 0.00  120.00
01/20/2010 400  000-1201-00 52.50  0.00
01/20/2010 400  000-6630-00 0.00  52.50

In order to make our integration interactive, we must first prompt the user to enter the date restriction in the format required to filter the data:

Before Integration

Dim startDate, endDate

Do
  startDate = InputBox("Enter the start date for your transactions (mm/dd/yyyy).")
  If Not IsDate(startDate) Then
     MsgBox "Invalid date format, please try again."
  End If
Loop Until IsDate(startDate)

Do
  endDate = InputBox("Enter the end date for your transactions (mm/dd/yyyy).")
  If Not IsDate(endDate) Then
     MsgBox "Invalid date format, please try again."
  End If
Loop Until IsDate(endDate)

If CDate(startDate) > CDate(endDate) Then
 MsgBox("The start date must be greater than the end date. Integration will end now.")
 CancelIntegration
End If

' Store the user input in global variables that can be retrieved later on
SetVariable "gblStartDate", startDate
SetVariable "gblEndDate", endDate

Now we can apply the user's input as restrictions to our source query data by invoking the AdditionalCriteria property of the Query object.

Before Query

Query.AdditionalCriteria = "DOCUMENT_DATE >= '" & CDate(GetVariable("gblStartDate")) & "' AND DOCUMENT_DATE <= '" & CDate(GetVariable("gblEndDate")) & "'" 

Note that the Query.AdditionalCriteria will only work on source queries that use a Text or Simple ODBC DSN. The AdditionalCriteria property will not work on Advanced ODBC queries.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More