Problems with Modal GP AddIn Window

I recently developed a GP AddIn that provided an extra options window for the Vendor Maintenance GP window. For a given vendor ID, the window allowed the user to store a list of attributes for the vendor.

It worked fine and was pretty straightforward--until I tried to make it a modal window.

When I used .ShowDialog, the window would display, but it would not respond to mouse events (brief video - IE and Flash required).

Clicking on check boxes did nothing, Save and Cancel would not work, I could not move or reposition the window, and even the control box buttons in the upper right corner of the window would not respond to a mouse click. But I could still use the keyboard to tab through the window controls, and could press the ESC key to close the window.

After further testing, it turned out that the modal window worked properly the first time it was displayed. After that, the problem occurred and would not go away until GP was shut down and restarted.

After posting to Steve Gray's excellent Dynamics GP development forum on Visual Studio tools (and seeing that someone else had the exact same issue), he assured me that he uses Modal AddIn windows all of the time, and that it definitely should work. So I tried creating a new GP window form from scratch in my VS project, but had the same issue. The next test that Steve recommended was to create a new project from scratch with just a plain modal window and no additional code.

Unfortunately, before I could try that test, my virtual server bit the dust, and my last full backup of the VHD was from August (ya, I know, I know, lesson learned). So after restoring my August backup, I saw that I didn't even have the VS Tools SDK installed. So I installed it, along with SDK 10 SP3, and tried to create a new test project.

Well, the problem no longer existed--now my modal AddIn windows work fine. So unfortunately I don't have a specific resolution other than to let you know that you aren't going crazy if you run into this issue, and just to recommend installing the latest Visual Studio Tools SDK service pack and see if that does the trick. If it does not, then it would seem to be something related to the development machine that is causing the issue.

Checking eConnect Version Number Programmatically

Lately, it seems that every time I deploy an eConnect integration, I run into some type of version issue with eConnect. The client will have 9.02 installed and I developed with 9.03, or 10.00 vs. 10.03. I often forget to ask in advance, and even if I do, nobody knows or remembers what version is installed. And when the wrong version is installed, the errors that I've run into seem to be completely random and inexplicable.

Based on these experiences, I figured it might be interesting to add a routine to my integrations to verify that the proper version of eConnect is installed before it attempts to run.

So today I decided to trace the "eConnect Release Information" utility to see how it was checking the eConnect version.

Turns out that it is just calling a single stored procedure:

exec DYNAMICS..taeConnectVersionInfoDYNAMICS

This returns a result set with 3 fields, with a row for each company:

Database Name
Version
CompanyName

I have verified that this procedure is available with eConnect 9 and 10, and from what I have read, may go back to version 7 / 7.5.

One good thing about the stored procedure is that it returns a "friendly" version number, like 9.0.3.0, 10.0.2.0, etc.

The stored procedure is encrypted, and I haven't bothered to buy a decryption app, so I can't tell exactly what it is doing, but I do know that it is iterating through each company database and checking something in the database.

The reason I know this, and the unintended consequence of this design, is that if there are any company databases that are Offline, or otherwise inaccessible, the version check fails completely and does not return any results. I have a client with over a dozen company databases, and several off line, so I'm unable to use the Release Information to check their eConnect version. Not very graceful.

And since this is only a stored procedure and is returning a version per company database, I assume that it is not checking the versions of the eConnect .NET DLL files.

If you want to check the DLL versions, you have a few options, but none are terribly appealing for programmatic checking.

1) The Knowledge Base recommends using Control Panel -> Add/Remove -> Select eConnect -> Click on Support Information link.

2) If you are adventurous, that same Add/Remove information can be found in the registry. Though it appears the GUID is the same across eConnect 10 releases and service packs (3C90E5C5C8D49BD42A1B7F8FF2B481C4), I suppose it could change at some point.

3) You can write code to locate the eConnect DLL and use FileVersionInfo to get the version number via .NET. The challenge is searching for the file on multiple drives and directories on a server.


And there are probably a few other creative ways I don't know about, like somehow checking the version in the .NET GAC.

The annoying part of checking the file versions is that it returns the typical useless GP version numbers like 10.00.0836.00 and 10.00.1072.00. Though documentation of such numbers is available for GP service packs, I haven't found such a list for eConnect service pack version numbers. If you know which version you want, you can just check to see if it matches, but I would rather be able to have a simple dialog that says "eConnect 10 SP2 is installed, but SP3 is required".

For now I think I'll try the stored procedure approach.

Microsoft Dynamics Sure Step- Practice and Opinion

Last week I was in Atlanta co-training a new course called “Project Managing Microsoft Dynamics Implementations with Microsoft Dynamics Sure Step” (Course 80053A). It was partnered with a one day class developed by Kristi Honey and Scott Mertes called “Project Management Skills Essentials for Microsoft Dynamics”.

The first day of the class focuses on the soft skills that are essential in each phase of an implementation. As I am sure many of you can appreciate, there were a ton of fabulous conversations during class, on breaks, after class. I attended the first day, and really enjoyed the opportunity to hear a variety of perspectives on project management related to Dynamics implementations. I think that project managers are often a lonely bunch, and a class like this is exactly what many will need to gain the confidence and focus to take their skills and processes to the next level. Usually I am exhausted after a day in class, but I will admit that this class left me energized with new ideas that I want to share with our internal project manager. I actually sent my boss an email before lunch that day saying that our project manager MUST attend the class!
But what I really wanted to write about is the two-day class that focused on Microsoft Dynamics Sure Step. First, what is Sure Step? Sure Step is a toolkit that folllows a methodology that Microsoft developed for Dynamics implementations. It contains a variety of templates focused on CRM, AX, GP, SL, and NAV implementations. Currently, there is a bit more available for CRM and AX, but more templates are forthcoming and there are already a variety of generic templates that can be adapted for each product.

To download Sure Step, you must be on a partner service plan with access to PartnerSource:
https://mbs.microsoft.com/partnersource/partneressentials/serviceplans/surestep/

One of the things that I found most beneficial about the class is that it follows a case study model. So instead of focusing on learning Sure Step in a vacuum, it presents a very practical way to understand the methodology and see how it can be applied to your organization. In the past, I know that a common issue for many partners was exactly how to begin to use Sure Step. Where to begin? Because Sure Step is scalable to the largest of implementations, partners with smaller implementations can feel overwhelmed by the formality of the phases and abundance of documentation. But all of those options and approaches are just different suggestions of how you might approach an implementation. A great takeway from the class, which I think many of the students last week would agree with, is that Sure Step is a toolkit that can be adapted to a variety of implementation sizes by picking, choosing, and adapting it to your needs.

From the case study, students work with documents like a Fit/Gap Analysis and Solution Blueprint, Functional Requirements Document, and a Statement of Work. Students see how all of the documents tie together, and how you might take one or two or all of them and apply them practically to an implementation. We had many side discussions in class concerning how Sure Step might scale up or down: for a smaller implementation you might work with one simple document that evolves to represent the requirements while a larger implementation may call for a full cycle of deliverables with a formal approval process and tollgate reviews.

So, where to begin? Well, for those organizations lacking a formal project management approach- Sure Step can provide an understanding of the formal phases and their purposes, including the customer and partner roles who participate and the associated deliverables. For others who already have a formal methodology in place, Sure Step can serve to reinforce what you are doing and fill in any gaps. And for those that are somewhere in the middle, the templates available in Sure Step can be used standalone to begin (or further) the development of your own processes.

Sure Step provides project paths for Standard, Enterprise, Rapid, and Upgrade projects, giving you different approaches based on the scale of the project. Additionally, in the Diagnostic Phase, Sure Step provides a guidebook for a series of Decision Accelerators that can assist customers with the decision to purchase, and provide the desired due diligence for the customer to feel comfortable with the decision. Partners can pick and choose from these, using the ones that are appropriate for the case at hand and best address the customer’s concerns.

I am writing this on a plane to Portland, Oregon. With my project plan for an upgrade in my bag. A little documentation and formal process never hurts :) Which makes me think that a future blog topic might be “Why even have a methodology?” For those of you that know me personally, you know I could talk on that for days!

Please share any questions, comments, or perspectives on Sure Step. I would also love to hear of successes (or failures) deploying Sure Step in your organization.

SQL User Defined Functions

Have you ever written a SQL query only to come across a relatively small requirement that suddenly makes the query more complex than you want?

In the last few months, it's happened to me twice, but fortunately I was able to utilize a User Defined Function (UDF) to meet the requirement while simplifying my query.

The first project involved exporting data from GP. It had a requirement to split the GP SOP Order number into two fields--the alpha prefix in one field, and the numeric suffix in the other. Well, because there were different SOP order types involved, the alpha prefixes had different lengths, as did the numeric suffixes, and to top it off, some of the order numbers had a dash separating the prefix and suffix, while others did not. Examples:

ORD0000095
WAR-1025
SP001024
CONS-2933

So, how do you split a field value 'in half' when you don't know where one half ends and the other begins? Unfortunately, SQL 2005 still does not have native Regular Expression capabilities. There are some functions that provide some limited character searches (PATINDEX, CHARINDEX), but they don't allow you to search for a pattern or range like RegEx.

After doing some research, I found several references to using a .NET CLR assembly to make Regular Expressions available within SQL statements. While this approach was very appealing, it was more complicated than I wanted, as it would require a change to SQL Server security to allow assemblies to run, and would introduce CLR into the mix, something that is not typically used in GP environments.

Since I was responsible for assisting with deployment of the solution in a remote client environment, I needed something very something simple and self contained in standard SQL--the fewer pieces, the better. So I began to consider the possibility of using a cursor to loop through each character of the order number to determine where the prefix ended, and the suffix began. I'm very reluctant to use cursors in SQL unless absolutely necessary because they typically can't perform nearly as well as a set-based solution, but given my constraints, it seemed like my only option.

After checking with a few colleagues about my cursor approach, I received a recommendation to package up the prefix / suffix routine into a SQL User Defined Function. So here is where I admit that in the 10 years that I've been working with SQL Server, I have never seen the need to use a User Defined Function. Most business data that I've worked with has been pretty straightforward, with multiply and divide and a string function being as complex as things would get. I had heard of User Defined Functions, but didn't realize how simple they really were to create and use--which probably contributed to me never "seeing the need" to use them!

As SQL Server Books Online (BOL) states under "User-defined Function Basics":

The benefits of using user-defined functions in SQL Server are:

--They allow modular programming
--They allow faster execution
--They can reduce network traffic

It goes on to point out that UDFs can be written in T-SQL or a .NET language.

So after reading a little more about UDFs, I built my function. At first, I was going to have the function return the index number indicating where the numeric portion of the order number started. But after realizing how convenient the function would be, I decided to write two functions: one to return the prefix, and the other to return the suffix. (Code listed below)

Once I created the functions, using them was a snap:

SELECT DYNAMICS.dbo.GetOrderPrefix(RTRIM(ORIGNUMB)) FROM SOP30200

This approach made my already complex SQL query much simpler and cleaner than if I had tried to code it all in one query.

Like most technical solutions, there are often many approaches to solving the problem. You weigh the requirements against the platform, environment, tools, performance, constraints, your knowledge and skills, budgets, and time. The lesson I'm trying to convey is not the specific solution or function that I implemented, but rather the opportunity that User Defined Functions presents, offering the potential to reduce the complexity of your SQL queries, views, or stored procedures.

One final note. Although I chose not to use a CLR-based UDF (I would have liked to!), I would strongly recommend looking into them if it is an option in your environment. They offer the tremendous flexibility and power of the .NET CLR, and based on my research, they are extremely fast for complex computations or logic.



Here is the GetOrderPrefix prefix function:


USE [DYNAMICS]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetOrderPrefix]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
DROP FUNCTION [dbo].[GetOrderPrefix]
END
GO

CREATE FUNCTION [dbo].[GetOrderPrefix] (@SOPNumber varchar(40))
RETURNS varchar(20)
AS
BEGIN
DECLARE @NumStart int
SET @NumStart = 0
IF LEN(RTRIM(@SOPNumber)) > 0
BEGIN
DECLARE @Position int
SET @Position = 0
--If the doc number has a dash, locate the prefix relative to the dash
IF CHARINDEX('-', @SOPNumber) > 0
BEGIN
--Locate the dash in the document number
SELECT @Position = CHARINDEX('-', @SOPNumber)
END
ELSE
--If no dash was found, loop through the characters and find the first numeric character
BEGIN
--Loop through the initial characters of the doc number to identify the end of the alpha prefix
SET @Position = 2
WHILE @NumStart = 0
BEGIN
IF ISNUMERIC(SUBSTRING(@SOPNumber, @Position, 1)) = 1
BEGIN
SET @NumStart = @Position
END
ELSE
BEGIN
SET @Position = @Position+1
END
END
END
-- Return the alpha prefix
RETURN SUBSTRING(@SOPNumber, 1, @Position - 1)
END
--If an empty string was passed in, return one back
RETURN ''
END
GO


And GetOrderSuffix:


USE [DYNAMICS]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetOrderSuffix]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
DROP FUNCTION [dbo].[GetOrderSuffix]
END
GO


CREATE FUNCTION [dbo].[GetOrderSuffix] (@SOPNumber varchar(40))
RETURNS varchar(20)
AS
BEGIN
DECLARE @NumStart int
SET @NumStart = 0
IF LEN(RTRIM(@SOPNumber)) > 0
BEGIN

DECLARE @Position int
SET @Position = 0

--If the doc number has a dash, locate the prefix relative to the dash
IF CHARINDEX('-', @SOPNumber) > 0
BEGIN
--Locate the dash in the document number, add 1 for the starting point of the numeric suffix
SELECT @Position = CHARINDEX('-', @SOPNumber) + 1
END

ELSE

--If no dash was found, loop through the characters and find the first numeric character
BEGIN
--Loop through the initial characters of the doc number to identify the end of the alpha prefix
SET @Position = 2
WHILE @NumStart = 0
BEGIN
IF ISNUMERIC(SUBSTRING(@SOPNumber, @Position, 1)) = 1
BEGIN
SET @NumStart = @Position
END
ELSE
BEGIN
SET @Position = @Position+1
END
END
END

-- Return the numeric suffix
RETURN SUBSTRING(@SOPNumber, @Position, LEN(RTRIM(@SOPNumber))-@Position+1)
END
--If an empty string was passed in, return one back
RETURN ''
END

GO