Monday, January 31, 2011

Microsoft SQL Server DSN Configuration

As I wrapped up fellow MVP Victoria Yudin's book just a week aback and prepared to write a review, I was reminded of the importance of properly configuring your Microsoft Dynamics GP DSN connection -- the artifact that allows the client software to read and write data to your company databases and the system database -- something the Microsoft Dynamics GP installation gracefully setup automatically nowadays for you. So, if this is the case, why is it that so many people still have issues with DSN connections and the Microsoft Dynamics GP client not "seeing" the server, I wondered.

So, I figured in this article, I would go back to basics to walk through some of the common issues and demistify the Microsoft SQL Server DSN configuration options that the setup gracefully takes care of.

1. First up, if you are on Microsoft SQL Server 2005, Microsoft SQL Server 2008, or Microsoft SQL Server 2008 R2, you should be using the Microsoft SQL Server Native Client driver. At this point, if you have any legacy ODBC drivers for systems that have been upgraded from SQL Server 7.0 or SQL Server 2000, these should not be used to connect to SQL Server 2005 or SQL Server 2008, because a) you are not taking full advantage of the Native Client driver's performance, and b) simply because the old driver is not designed to be used with the new.

Getting started

Clearly name your connection so you can identify which version of GP or server you are targetting. If you have multiple physical environments, for example, test and production, clearly name the driver to distinguish which environment you are targeting. Also, note that you can setup a driver that points to an instance of SQL Server by using the MACHINE\INSTANCE_NAME convention. Starting with version 10.0, the ODBC name must be exactly the same across all workstations where Microsoft Dynamics GP is installed.

2. Microsoft Dynamics GP only supports SQL Server authentication. As much as you complain or rant about the system not supporting Integrated Windows Authentication, you cannot set the authentication method to anything other than SQL Server authentication, in which case you will need a login ID and password only to test your connection. You can certainly avoid going through the other steps of the setup if you choose to uncheck the Connect to SQL Server to obtain default settings for the additional configuration options checkmark.

Setting up the Authentication method

Also note the checkmark option's prompt. The settings are read from SQL Server to obtain the default ODBC connection settings. This might sound redundant, but you will understand what this means next.

Some frequently asked questions as well are:

a) Why can't I use my Microsoft Dynamics GP account to authenticate my ODBC connection?

Because Microsoft Dynamics GP encrypts passwords on SQL Server. Since the password is encrypted on Microsoft SQL Server you would have to enter the sequence of characters that are a part of the encrypted password to authenticate and this is simply not feasible as well, you don't know the encrypted password to begin with. For more information see Why does Microsoft Dynamics GP encrypt passwords over at Developing for Dynamics GP.

b) Does the password I enter here get stored with the Connection?

Categorically No! The user Id and password information entered here is only used for verification of the SQL Server default settings and testing of the connection itself. They are never stored with the setup.

3. With newer versions of Microsoft Dynamics GP, there's no need to set the default database to Microsoft Dynamics GP system database, DYNAMICS. However, since I am an old timer and still have my own quirks, I do it as a standard practice. The default database is the master database when no other database is specified in the connection.

Choosing default database
What is still standard though is to disable the Use ANSI quoted identifiers and Use ANSI nulls, paddings and warnings in your connection settings. Now, keep in mind from my previous observation, that these connection settings are defaulted from Microsoft SQL Server settings at first. Why I emphasize this? Because I always get asked, Why are these checkmarks on? When in doubt, ask your Microsoft SQL Server administrator to show you the SQL Server properties for Connections.

Microsoft SQL Server Properties window - Connections tab

Also, you will want to note that at this point you can define a Mirroring server if you are running a mirrored database environment for your Microsoft Dynamics GP databases. For more information on Mirrroring, see KB article 926490 - Description of the requirements to run replication, clustering, log shipping, and database mirroring together with Microsoft Dynamics GP (access to CustomerSource or PartnerSource is required to view this article).

These are some frequently asked questions:

a) Why can't I enable ANSI quoted identifiers and ANSI nulls, warnings, and pads?
The answer lays with Microsoft Dexterity. Dexterity (through the Runtime Engine) does not support quoted identifiers for character strings or hetorogeneous transactions -- the function of ANSI nulls, warnings, and pads is to maintain consistency of transactions and queries across distributed platforms. Since Microsoft Dynamics GP is a client/server based application and the system database, DYNAMICS was designed to live on the same SQL Server with the company databases, there was really no need to maintain this compatibility. After all, no heterogeneous query would ever be issued to begin with. 
4. Some more settings that are key to keep in mind reside with this wizard page. Among them Perform translation for character data, which receives most of the attention among Microsoft Dynamics GP consultants and database adminstrators alike. This setting was designed to do, as it name suggests, translations of characters between the client code pages and the server code page.

Defining connection settings

In earlier versions of MDAC, i.e., MDAC 2.1 or later version of the SQL Server ODBC driver (version 3.70.0623 or later) or the OLEDB provider (version 7.01.0623 or later), under some circumstances you could experience translation of character data from the client code page to the server code page, even when Autotranslation is disabled for the connection. Autotranslation is not the only mechanism that can result in code page conversion. The SQL Server 7.0 ODBC driver and OLEDB provider introduced a behavior when connecting to MSDE 1.0, SQL Server 7.0, or later versions of either. All SQL statements sent as a language event are converted to Unicode on the client before being sent to the server. The end effect of this is similar to an Autotranslation of all data flowing from the client to the server through a language event, regardless of the current Autotranslation setting for the connection. This will not introduce any difficulties except when trying to store non-translated character data from a code page other than SQL Server's code page.

So why do we talk about code pages? Because a) Microsoft Dynamics GP does not support Unicode characters. If you are a database developer it means, there is no such thing as NCHAR, NVARCHAR, or NTEXT table columns defined throughout the system or company databases, that would otherwise allow for Unicode character storage. Since this has been the case since version 1 of Microsoft Dynamics GP, it's easy to see how a different client code page (as in, a different language being ran at the Windows operating system level) could cause issues if this option were to be enabled). I have ran my own tests with a Russian locale (code page 1251) and 1250 code page on SQL Server and when this option is enabled, I get jumbled data in my GP tables when submitting Russian characters to be stored on the server. That's because, while there are subtle differences between code page 1251 and code page 1250, the latter only supports Eastern European languages that are based on the latin character set.

5. Once you have completed the settings, it's now time to review the summary for all the options you have chosen. 

Reviewing connection configuration

6. Finally, you can test and you should now be good to go.

Testing connection

I hope this review of ODBC settings for the SQL Server Native driver have served as a good first step in understanding the configuration. With so many settings it's easy to see why any subtle change would cause issues across the board..

Until next post!

Mariano Gomez, MVP
IntellPartners, LLC

Thursday, January 27, 2011

The always busy Mark Polino

Every once in a while, I run into someone who inevitably ask me "what do I need to do to become a Microsoft MVP?". At some point in the past, I tried to answer this question with at least some of the things that I do in the community, just as an example of the numerous ways you can contribute. Frankly, there's no one way to become a Microsoft MVP and furthermore this is the best response I can give to this question: You have to be an MVP without the title, before even receiving the Award.

So, what does this have to do with Mark Polino and why am I talking about MVPs you may be asking. Well, it turns out that Mark is probably one of the busiest MVPs I have ever known... the guy simply does not sleep! Even though, like me, he would argue that 4 hours constitute a good night rest.

Today in particular, I wanted to highlight yet another contribution from Mark to the community - among all the others that you will see in the next few months. As it turned out, Mark had been working on a top secret project -- ok, maybe I am watching way too much TV. The result of those efforts is now This new search engine is built on the Google search plarform and compiles information from all existing blogs in the community. You may ask, and how is that new or different? What's new and different is, MyGPSearch is also able to search for a query across the former newsgroups and the Community website. This allows you to find a topic even in discussions conducted between individuals in the community.

Mark makes it clear though that in adding any new resource he will carefully evaluate whether you are trying to advertise your company or simply sell something. If that's the case, your site may not make the cut.

As if this wasn't enough, MyGPSearch is also available for the Android platform (QR code below), so be sure to get the app if you really need to have a handy resource, literally, while out at a client.

Don't have an Android phone? Make sure to check out mobile site. With so many choices, please don't tell me you can't find what you need.

The next time you ask, "What do I need to become an MVP?", just look it up in I have added a permanent link to on the right frame of this blog.

Ok, now my head hurts!

Until next post!

Mariano Gomez, MVP
IntellPartners, LLC

Tuesday, January 25, 2011

Finding out the version of Office Web Components being used with your Microsoft Dynamics GP home page

Seriously, I wasn't planning on doing any more articles on the home page - at least not for now. But you have to admit this one is compelling. To find the version of the Microsoft Office Web Components running on your system,

1. Right-click on any metric graph currently being displayed on your home page.

Office Web Components options

Choose the About... option to continue.

2. You will now see the version number of the Office Web Components installed and being used with Microsoft Dynamics GP.

Office Web Components version
If you have any questions on a specific version, please see my article Microsoft Dynamics GP 2010 and Office Web Components for more information.

Until next post!

Mariano Gomez, MVP
IntellPartners, LLC

The Microsoft Dynamics Convergence Blog site

More on Microsoft Dynamics Convergence 2011 Atlanta... if you are excited about the possibilities at Convergence and you are looking for some good information on what to do before, during, and after the conference, then the Microsoft Dynamics Convergence Blog is for you. Headed up by Microsoft's Alexa Hoekstra, the Convergence blog is a very good resource with helpful tips and articles, whether you are a battle-tested Convergence goer or a newbie to the event.

Haven't registered yet? What are you waiting on?

Until next post!

Mariano Gomez, MVP
IntellPartners, LLC

Monday, January 24, 2011

Changing the metrics properties on the Microsoft Dynamics GP home page - Part 2

Part 2 of 2 - Changing the metrics properties on the Microsoft Dynamics GP home page.

In Part 1 of the series I showed a couple cool things that can be accomplished on the Microsoft Dynamics GP home page with metrics and changing graph properties and so on. Today, I will explore two more features that I think will make you fall in love -- ok, let's not get carried away -- with the Microsoft Office Web Components, and in particular the Chart Control: the Toolbar and Command and Options selections.


The Toolbar option enables a graphical toolbar above the metrics being displayed.

Office Web Components - Toolbar option

Enabled Toolbar option

Now, with the toolbar enabled, you have access to some of the elements discussed in the previous article and more. You can change the graph layout and some of the Data properties as shown in the previous article.

Command and Options

Of all the options presented, the Command and Options is one of my favorites as it allows you to visually enhance your graph and even change the type of graph, similar to the Data options. Take a look at the following visual enhancements produced by changing some of the Border/Fill properties.

Command and Options - Border/Fill option

Finally, if you have worked with Microsoft Excel producing graphs in the past, then you now probably have a lot of experience in creating and modifying graphs, so I won't delve into this. Nonetheless, know that they are a number of options that you can play with to bring some life to your charts and even copy/paste these from the home page onto some fancy report that you must deliver, so why not bring some life to those graphs?

Until next post!

Mariano Gomez, MVP
IntellPartners, LLC

Microsoft Dynamics GP 2010 Implementation book review

Just around the last week of Christmas I got myself a brand new copy of my friend, Victoria Yudin's Microsoft Dynamics GP Implementation book. No, I did not get a free copy and actually refused one in support of my fellow MVP's work. I spent the last few weeks reading through each chapter -- I still use a highlighter -- and thought I would provide some insight on the content and the author.

On the Content

As a seasoned implementation veteran, I have to admit there are many elements of this book that appeared pretty straight forward, but relevant if you are a new comer to the Microsoft Dynamics GP world. Victoria does a good job of walking the reader through the importance of project management, assembling a good team, executing the implementation, and delivering post implementation support as an integral part of the project. I have say, that I have gone through the PMP certification and indoctrinated myself with the Sure Step methodology and, unless you do project management for a living, this book rounds out very well all the considerations needed to deliver a solid project.

Now, if you are a hands-on person on the project, Victoria also makes sure you have some insight into the system requirements, infrastructure, installing SQL Server, and setting up the system.

I would have liked to see more of the "in the trenches" experiences that Victoria has been through in the book, but I can see how the book would have quickly become anecdotal and perhaps hard to follow given the topic, since all implementations are different in nature. Nonetheless, items such as, the importance of the collaboration and interaction between the configuration team and the customer's IT team seem not have made it to the book. Also, some of the systems configuration requirements seem to have come from the standard Microsoft recommendations and don't necessarily reflect the complexities of some environments and other considerations needed in such environments. Now, on the good side, at least the standard system recommendations are in the book since I have seen many installation teams that have not a clue these requirements even exist.

If you are a seasoned Microsoft Dynamics GP professional, there are still a few things you can learn - for example, are you still insisting in parallel testing? Hear what Victoria has to say. If you are new comer to the Microsoft Dynamics GP implementation world and/or have delivered a couple implementations so far, then this book is for you.

If you are an individual or an organization evaluating Microsoft Dynamics GP and want to get a pretty good idea on the elements that go into making your implementation successful along with considerations on environment and team, this book is for you too! The books language is simple and easy to digest, yet solid in its delivery of each topic covered.

On the Author

Having known Victoria for the past 3 years or so, it is easy to see why she would have been inclined to write a Microsoft Dynamics GP implementation book. Victoria brings over 20 years of experience delivering high quality Microsoft Dynamics GP implementations and she has the distinguished honor of being a Microsoft Dynamics GP MVP for the past 8 years. If you are truly looking for a Microsoft Dynamics GP implementation book from a well rounded individual you can trust, this book is it! Keep in mind that outside Microsoft's manuals and material, there is little available on the subject at your local library.

Victoria has fielded and answered more than 5,000 questions from the community over the past 8 years, runs a blog that constantly keeps you updated on her reporting prowesses, and manages to still keep a cool head in tough situations. No easy job considering the nature of enterprise resource planning implementations.

Go and get your Microsoft Dynamics GP 2010 Implementation book now.

Until next post!

Mariano Gomez, MVP
IntellPartners, LLC

Sunday, January 23, 2011

Changing the metrics properties on the Microsoft Dynamics GP home page - Part 1

Part 1 of 2 - Changing the metrics properties on the Microsoft Dynamics GP home page.

In this series I wanted to surface a little known secret about the standard Microsoft Dynamics GP Home Page metrics: you can change the graph coding required! In fact, there are many graph properties that can be changed.

Gross Profit for the Past 12 Months sample metric

Microsoft Dynamics GP makes use of the Office Chart Control, a component of the Microsoft Office Web Components to display the metrics that you have become accustomed to see on the home page -- see my article Microsoft Dynamics GP 2010 and Office Web Components for more information on the different versions supported.

When you right-click on the metric of your choice a menu becomes available that will allow you to choose from a number of options, as shown below.

Today we will be looking at the Data properties.

Command and Options window - Data Source tab

The Data option presents the Commands and Options window with 3 tabs: Data Source, Data Sheet, Type... you have guessed correctly! You can make changes to what's being displayed on the metric by creating a new data sheet or reading data from a query. However, this information will be lost as soon as you refresh the home page.

Perhaps, the option that's most important at this point is the Data Sheet option. The Data Sheet option shows the numbers crunched by GP for each category and series of the metric being displayed.

Command and Options window - Data Sheet tab
 The good thing about the Data Sheet tab is, you can actually use the numbers displayed to reconcile against your subsidiary module, since that's the source of the data for all metrics.

Finally, the Type tab allows you to change the type of graph being displayed. Most metrics are set to display bar graphs by default, but you can make a subtle change to the type of graph to enhance your reading of the data being presented.

Commands and Options window - Type tab

The series continues tomorrow exploring some of the other properties available to you as a user.
Until next post!

Mariano Gomez, MVP
IntellPartners, LLC

Thursday, January 20, 2011

Ingredients for a successful Convergence 2011 experience

"Convergence is more than just the sum of its keynotes and sessions"

My new article is out over at MSDynamicsWorld with some pointers for a successful Convergence here in Atlanta. Wheather you are a local or coming from another city or country there are a few things to take into account even before you arrive at Hartsfield-Jackson. You can read about these ingredients here.

All comments welcome, so please let me know what you think.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Wednesday, January 19, 2011

Microsoft Dynamics GP Technical Conference 2011 sessions now out!

Likewise, I wanted to bring attention to the Microsoft Dynamics GP Technical Conference 2011 sessions that David Musgrave and I will be co-presenting:

Development Troubleshooting with the Support Debugging Tool
Presenters: David Musgrave and Mariano Gomez, MVP

Following the success of this session at Convergence Atlanta 2010, David Musgrave and Mariano Gomez return to the Microsoft Dynamics GP Technical Conference with a number of advanced features included in the now popular Support Debugging Tool. Learn how to debug and trace your customizations, build temporary fixes, and troubleshoot development issues and problems with dictionaries. Discover some of the tricks used by the Microsoft Developer Support team. Presenters will demonstrate real life scenarios and work through their resolutions.


- Manual Logging Mode
- Advanced Debugger Mode
- Dictionary Control
- Scripting and Triggers
- SQL Execute and Runtime Execute features
- Exporting and Importing fixes

Introduction to Microsoft Dynamics GP Customization Tools

Presenters: David Musgrave and Mariano Gomez, MVP

New to Microsoft Dynamics GP? Welcome to this jam packed session where Microsoft’s David Musgrave and MVP Mariano Gomez will walk you through the different customization tools available to address your next project. If you have plans for a Microsoft Dynamics GP customization, but you don’t know where to start and are skeptical about future upgrade issues, then this session is for you. Gain some introductory knowledge of Modifier and Report Writer with Visual Basic for Applications, Dexterity and Visual Studio Tools with two of the leading Community experts, who will contrast the tools and help you sort through the complicated terminology, to bring you a clear picture of the choices available for your next project.


- Where to start?
- Modifier and Report Writer with Visual Basic for Applications
- Dexterity
- Visual Studio Tools
- Resources

Just a note on this last session... in previous years we focused our attention on the experienced to advanced developer. This time around, if you are just getting acquainted with Microsoft Dynamics GP development, we want you to start out on the right foot without the frustrations, so please attend our session and save yourself the headache of figuring this thing out on your own.

Register for the Microsoft Dynamics GP Technical Conference 2011, in the always beautiful Fargo, North Dakota, here.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Microsoft Dynamics Convergence 2011 sessions are now out!

If you are attending Microsoft Dynamics Convergence this year, it's a good time to begin planning the sessions you want to attend. The session codes, titles, and speakers are now available for you.

On that same note, David Musgrave and I are presenting the following sessions:

CSGP14 Administering Microsoft Dynamics GP Like a Pro with the Support Debugging Tool

Track: Microsoft Dynamics GP
Speaker(s): David Musgrave, Mariano Gomez
Audience: IT Professional, Technical Decision Maker
Skill Level: 300 - Experienced

Session Details

Following the success of this session at Convergence Atlanta 2010, David Musgrave and Mariano Gomez return with a number of advanced features included in the now popular Support Debugging Tool. Learn how to maintain a clean DEX.INI in a Terminal Server environment, execute complex profiling of transactions and data, and resolve day to day security issues and problems with dictionaries. Discover tricks used by the Microsoft Support organization and its partners.

DDGP03 Tools, Tools, Tools: Microsoft Dynamics GP Customization & Integration Tools Review

Track: Microsoft Dynamics GP
Speaker(s): David Musgrave, Mariano Gomez
Audience: IT Professional, Technical Decision Maker
Skill Level: 200 - Intermediate

Session Details

Welcome to this jam-packed session where Microsoft’s David Musgrave and MVP Mariano Gomez will walk you through the different customization and integration tools available for Microsoft Dynamics GP. If you have plans for a customization or application integration, but don’t know where to start, this session is for you. Gain introductory knowledge of Dexterity, Visual Basic for Applications, Visual Studio Tools, Extender, Integration Manager, eConnect, and Web Services with two of the leading community experts.

If you haven't registered yet, please visit the Microsoft Dynamics Convergence Atlanta 2011 site and do so now.
Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Tuesday, January 18, 2011

Changing and Copying a Home Page Role - Part 2

Part 2 of 2 - Changing and Copying a Home Page Role.

In Part 1 of this series, I described how you could change a user's home page by reassigning him or her to a different role using the standard Microsoft Dynamics GP interface, but I also explained that my client wanted to be able to copy all the standard settings they had worked on over months to a staff member who was simply switching departments.

I also mentioned the use of a script, and while there's one, I want to also clarify that the client was not interested in having SQL scripts ran in their environment, so I had to come up with an alternate solution. The answer was to install the Support Debugging Tool and use it's XML Import and Export tool.

XML Table Export window

We created a profile with for the tables containing the settings the customer wanted to replicate for their user, which we then exported into an XML file. Since all that was needed was to use one of the existing profiles for an Accounts Receivable user as a template, we chose one user as a template. We then edited the XML file with Notepad, removing information from all other users, then replacing the template user ID for that of the new Accounts Receivable staff.

We then use the XML Table Import feature to reimport the now changed XML data file with the new user's profile settings.

XML Table Import window
The Support Debugging Tool provided a safe mechanism to replicate data the client needed, because in their eyes it meant not having to run queries against their system. In addition, they could test the import several times if needed, because of the data overwrite capabilities offered by the tool.

The next time you are considering duplicating data from one user to another, don't overlook the Support Debugging Tool.

For more information on the Support Debugging Tool, visit the Support Debugging Tool Portal page over at Developing for Dynamics GP. You can find excellent content on the tool as well in MVP Mark Polino's new book Microsoft Dynamics GP 2010 Cookbook.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Sunday, January 16, 2011

Changing and Copying a Home Page Role - Part 1

Part 1 of 2 - Changing and Copying a Home Page Role.

I was working with a client a few days aback and they have been on Microsoft Dynamics GP 2010 for quite a few weeks now. One of their users was moved from the accounts payable department to the accounts receivable department.

The IT staff wanted to change the configuration of her home page to look just like that of an existing user in this department, so of course, came the question..."How do we change her home page, and have her get the same links we have setup for all the other users in the group?".

Out of the box, Microsoft Dynamics GP allows you to change a user's role by clicking the Change Role... button under the Customize Home Page window.

Customize Home Page

Once you click on the Change Role button, you will receive a warning on effecting the changes, since all customizations and previous layout changes would be lost in the process of changing the user's role.

Change Role Warning

Since our goal was to move the user from role, this was the first task at hand. The company enjoyed standard links and settings for their users overall, so worring about loosing the previous links and settings was not an issue. Once we clicked the Ok button, we were at the Select Home Page window where we could move the user to the Accounts Payable role.

Select Home Page

Good so far! Now, how could we make a copy of all the settings established by the IT department for all users to this user profile? Tomorrow, I will show you a script that we wrote to achieve this.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Wednesday, January 12, 2011

IM - Integration Manager Series Summary

I know you are still unreeling from the New Year's celebrations and that you probably missed out on a couple of the articles I published in the past days. The following are the links to the articles:

I also wanted to take the opportunity to highlight Steve Endow's recent article on Integration Manager, over at Dynamics GP Land which highlights an odd error a customer of his was getting on a workstation.

This completes a good roundup of the topics covered.
Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Monday, January 10, 2011

GPUG to offer Payroll Tax Update seminar with Microsoft for US and Canadian partners and customers

Important 2011 US and Canadian Payroll Year-End and Tax Update with Microsoft (Jan 2011)

Microsoft payroll tax update gurus will give an overview of 2011 U.S. and Canadian Payroll Year-end and Tax Update changes and how it impacts Microsoft Dynamics GP customers for Microsoft Dynamics GP 9.0, 10.0, 2010 and Microsoft Business Solutions Small Business Financials 9.0. This is a GPUG open event, all members are invited to attend.

The recent legislative changes presents many modifications to U.S. Payroll in Microsoft Dynamics GP and Small Business Financials. This session will provide you with an overview of the updates in the product to comply with the recent legislative changes. Canadian Payroll also has several new regulatory changes as well which is new functionality for our customers that we will review. Microsoft has major payroll releases in the next weeks / month that will impact your payroll, attend this session to be educated on the latest changes so your business will continue to be able to run payroll without issues.

GPUG's presenters work hard at providing valuable education to you, our members. For this session, if you have questions, comments or suggestions prior to the event and would like us to include that information, please email "Presenter Request".

This is an open GPUG event, all members are invited to attend. Register here.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Sunday, January 9, 2011

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

' Make sure the connection is valid
If (oCn.Status = 1) Then
   ' Setup global connection variables
   SetVariable "gblConn", oCn   
   SetVariable "gblInterID", GPConnection.GPConnIntercompanyID
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
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!

Mariano Gomez, MVP
Maximum Global Business, LLC

Thursday, January 6, 2011

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

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

One of the things often frustrating for Integration Manager users and developers alike is knowing what document number will be assigned by GP to an integrating transaction when the document number property in the mapping is set to Use Default. Typically, the developer will need this key piece of data, because the integration will perform some write back to a source system to mark a specific record as processed, but also passing back the document number that was assigned in GP.

The UseDefault rule is shown for a Sales Transaction mapping
But, how to know the journal number, or the order number, invoice number, or voucher number that GP has assigned to a submitted document after the record has been integrated?

Let's start by saying that I have seen techniques that read the next document number from the Microsoft Dynamics GP setup tables, then assign that document number to the key field in Integration Manager for the record being integrated. The problem with this approach is that it can cause the system to lock up and crash if users are processing transactions too. Why? Because most developers do not use transactional methods to retrieve and update the next sequence number in the Microsoft Dynamics GP tables. Imagine updating the next SOP number while a user is creating a SOP transaction at the same time. Without proper transactional methods in place, this can wreak havoc in the system.

I have also seen techniques that immediately query the transaction tables looking for the highest DEX_ROW_ID and the transaction associated with it. This technique is also not reliable, especially if they are users processing transactions at the same time the integration is being executed. In summary, you can end up retrieving the wrong document number for your source integrating transaction.

So what's the solution?

Over the years I have developed a technique to overcome this hurdle. The technique assumes that the source transaction records have a unique identifiable key (and in fact, they should). In the case of journals, orders, or invoices, this key is assigned by some source system where the transactions are being integrated from. You can easily spot this key as it typically allows header records to be linked to the detail records to create the relationships between these. For example, if you are integrating orders from your source system into invoices in Microsoft Dynamics GP, it is assumed that the Order Number from your source system is the key.

The idea is to pass this source key to a Microsoft Dynamics GP user defined field (via mapping) or other data field where it can easily be queried after the document has been integrated.

Tomorrow, I will describe the scripting elements that make it possible to retrieve a document number assigned by GP once a source document is integrated and the field rule for the key field is set to Use Default.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

Wednesday, January 5, 2011

Look what Santa brought for me!

There is a little box for me...

The elves at Amazon brought me a book!

Gotta love this one!

Great material for the winter!!

I know Christmas is gone and whether you believe in Santa or not, that's a whole other story, but just before I left for vacation to New York City, I had ordered myself -- yes, I am my own Santa! -- a nice little reading material for the cold Atlanta winter. I figured, since summer was well spent with my Cookbooks, winter should be just as productive, especially the days I really don't feel like leaving my house. So, what did the elves at Amazon brought for me? Nothing more, nothing less than MVP Victoria Yudin's brand new Microsoft Dynamics GP 2010 Implementation book.

Now do me a favor... be your own Santa and go and order Victoria's book.

Be this the time to also congratulate Victoria on her 7th year as a Microsoft Dynamics GP MVP... the woman is rolling!
Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC