Microsoft Dynamics GP, the next generation of end-user customization tools?

September 9, 2011

My new article is out on my Community column blog, In my humble opinion with The Dynamics GP Blogster. This time, I take a swipe at some significant improvements that could be added to the customization and integration tools like Modifier, Report Writer, and Integration Manager, just by switching out the programming environment and scripting languages, Visuals Basic for Applications and VBScript for Visual Studio Tools for Applications and PowerShell, respectively.

Far fetched? Not quite, go on and read my reasoning behind this, over at the Community’s website.

Microsoft Dynamics GP, the next generation of end-user customization tools?

For more information on all the programming languages and environments, check the following links:

VBScript
   http://msdn.microsoft.com/en-us/library/cc175562(v=vs.90).aspx

Visual Basic for Applications
   http://support.microsoft.com/kb/163435

Visual Studio Tools for Applications 2.0
   http://msdn.microsoft.com/en-us/library/cc175562(v=vs.90).aspx

Scripting with Windows PowerShell
   http://technet.microsoft.com/en-us/scriptcenter/dd742419

Windows PowerShell Getting Started Guide
   http://msdn.microsoft.com/en-us/library/aa973757(v=vs.85).aspx

Modifier with VBA for Microsoft Dynamics GP 2010 Sample Applications
   http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=9304

Microsoft Dynamics GP 2010 Tools Documentation: Integration Manager
   http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=10955
Until next post!

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

Advertisements

Getting the Next Voucher Number for a Payables Transaction Integration

September 7, 2011

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/


Could not load file or assembly ‘Microsoft.ReportViewer.WinForms’ after upgrading to Integration Manager 2010

September 6, 2011

When trying to run an integration in GP2010 (after just upgrading from 9.0), you may receive the following error:

Log Report Failure
Could not load file or assembly ‘Microsoft.ReportViewer.WinForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. The system cannot find the file specified.

The latest versions of Integration Manager now incorporate the ReportViewer Control for displaying the different reports generated by the application.

If you receive the above error, install the ReportViewer Redistributable component from one of the following locations:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=21916 (Visual Studio 2005 components)

or

http://www.microsoft.com/download/en/details.aspx?id=6576 (Visual Studio 2008 components)
The latter will work just fine with Microsoft Dynamics GP 2010 or 2010 R2.

It is also recommended to install the 2007 Office System Driver Data Connectivity Components, which can be downloaded from:
http://www.microsoft.com/download/en/details.aspx?amp;displaylang=en&id=23734

or

Microsoft Access Database Engine 2010 Redistributable, which can be downloaded from:
http://www.microsoft.com/download/en/details.aspx?id=13255

Keep in mind that the above are not a substitute for Microsoft Office and are just intended to facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2010 (*.mdb and *.accdb) files and Microsoft Office Excel 2010 (*.xls, *.xlsx, and *.xlsb) files to other data sources such as Microsoft SQL Server. Connectivity to existing text files is also supported. ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats.
Once done, reboot the machine.

Until next post!

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


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

August 30, 2011

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/


Integration Manager for Microsoft Dynamics GP 2010 hangs when running an integration – Follow up

August 9, 2011

After some additional testing to find the route cause of Integration Manager 2010 hanging, it seems the issue has been narrowed down to a glitch in one of the slides cycled by the Connect gadget on the home page, and not an issue with the Microsoft .NET Framework 3.5 as originally thought.

For those of you who follow closely on the community news through the Connect gadget, you may have noticed the service being down since last Friday – around the same time Microsoft Support started receiving reports on the issue. Sources tell me that this was done as part of the standard testing protocol to discart new functionality causing the problem.

The slide causing the issue was removed this afternoon and the Connect service has been restored. If you were getting prepared to apply a new hotfix or service pack, you will be glad to know there may be no need to do so.

Stay tuned for further updates.

Until next post!

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


Integration Manager for Microsoft Dynamics GP 2010 hangs when running an integration

August 9, 2011

The forums are hot with users and partners reporting an issue with Integration Manager for Microsoft Dynamics GP 2010 hanging when running an integration. Everyone seems to agree the issue started sometimes this past Friday, August 5, 2011, with most users reporting a normal behavior and the ability to run trouble free integrations prior to that date.

This issue has been written up under the following hot topic article

Integration Manager for Microsoft Dynamics GP 2010 is Unresponsive at the Beginning or End of an Integration

According to a Microsoft representative with the Escalation Engineering team:

Our development team has identified the issue to be the result of a change in .Net Framework 3.5. At this point our development team is working on creating a new Integration Manager build which will then undergo testing to verify the build and that the issue has been resolved. As soon as we have a new IM build that resolves this issue, we will post the install on CustomerSource/PartnerSource so you can download the update and begin updating your IM installations to correct this issue.

Microsoft .NET Framework 3.5 was released in 2,007 and has since undergone a Service Pack 1 release in November of 2,008, and some security fixes just in July of this year.

Other releases of Integration Manager appear not to be affected by this as they use earlier versions of the .NET Framework.

Until next post!

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


IM – Cannot Open Database error when running eConnect adapter integration

June 29, 2011

Here comes one of those puzzling errors that can have you spinning your wheels for a bit until you resolve it. Just recently, I was helping a client building a Fixed Assets integration. The requirement called for importing a number of asset records and have a method that could be reused as new assets are added to the organization’s 15 different locations… that’s the easy part!

We were getting ready to run our first integration when the dreadful error came up:

ERROR: System.Data.SqlClient.SqlError: Cannot open database “XYZAB” requested by the login. The login failed”

Of course, here comes the troubleshooting aspect of the process. With the client running eConnect 10, there are a few places to look for issues that may trigger this error:

Component Services:

The most likely cause of this error message is a problem with the eConnect COM+ configuration. Open the Component Services by clicking Start > Run and type in dcomcnfg.

Expand Component Services > Computers > My Computer > COM+ Applications. Right click on “eConnect 10 for Microsoft Dynamics GP” and choose Start. If you receive an error, that means the COM+ component is not configured properly.

To configure, right-click and choose Properties. Click on the Identity tab and make sure that the domain user account configured here is setup in SQL server and is at least a member of the DYNGRP role for the company and dynamics databases.

If you have System Account selected, select This User instead and enter your Domain\User account and password.

SQL Sercurity

Open Microsoft SQL Server Management Studio (SSMS) and in Security verify that you have a SQL user that is the same Domain\User and the user is part of the DYNGRP role.

That should do!

Until next post!

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