A Tragedy In Integration Land: How to Spot a Bad Dynamics GP Integration

Trivia Question:  What is one of the fastest ways to spot a bad Dynamics GP .NET integration? (answer at the bottom)

I've seen some strange things in my days working with Dynamics GP and developing integrations.  I've seen several crazy GP macro generators that are used to "enter" data rather than using an integration tool.  I've seen .NET projects that are wrappers for the entire eConnect assembly, essentially re-writing all of the eConnect interfaces, for no apparent reason.  I've seen half-baked integrations that have transaction distributions hard-coded, with DR and CR going to the same account!  And I've seen the 1980s vintage integrations that used "technology" (and alot of duct tape) that was so old I didn't even know it existed.  All of these fall into the category of "What were they thinking???".

So when I was told that a client needed help with an existing VB-based integration, I thought "Excellent!", finally a modern .NET Dynamics GP integration.  Surely it will be well designed and use current integration tools.  And then I saw it.

Oh. My. Gosh.  How could I be more wrong?

So apparently back in 2006, the client's former "Gold Certified" Dynamics GP partner "developed" this ".NET" integration between the client's operational system and Dynamics GP.  It's really quite simple:  Import AR invoices and GL journal entries.  Piece of cake, right?

Fortunately, the client has a copy of the source code from their prior partner.  But when I start looking through the Visual Studio files, I don't see any code.  No classes and no modules--just a single VB form.  Hmmmm.

So I grab a copy of the entire solution and open it on my development server.  Sure enough, essentially no .NET code.  But what I do see are a bunch of SQL files that have been included in the project.

20 SQL files,  to be exact, each containing a stored procedure.  Some of these stored procedures are pages long, and horribly complex looking.  Dozens of parameters.  Inserts, updates, deletes.  And oh yes, a very good sign that the developer was out of his league:  Lots, and lots of SQL cursors. 

After staring at pages of SQL stored procedure code in a Visual Studio project, I finally realize what this supposed "developer" at the supposed "Gold Certified" Dynamics GP partner had done.

He had written, from scratch, a 100% SQL based direct-to-table integration to Dynamics GP using 20 stored procedures.

Read that last sentence one more time...

In case that didn't quite sink in, let me state that another way.  Instead of using one of several of the standard, supported, documented, tested, efficient Microsoft or ISV Dynamics GP integration tools or technologies that already exist, this developer apparently decided that the only way to get AR invoices and GL journal entries into Dynamics GP was to write custom stored procedures to shove the data into the SQL tables.  Using lots of cursors no less!

When it finally dawned on me the carnage of code that I was looking at, I was speechless.

The unbelievable tragedy of this nightmare integration is that the client trusted their Dynamics GP partner.  The client trusted the "Gold Certified" status of the Dynamics GP partner, who claimed to know something about Dynamics GP.  And in my humble opinion, the partner betrayed that client's trust.

The partner billed the customer, presumably an obscene amount of money, for this "solution", which is going to need to be scrapped and re-written so that it can be supported going forward.

Some might say that I shouldn't be exposing the egregious behavior of a GP partner, perhaps arguing that it makes the Dynamics GP community look bad, or it serves to reduce potential customer's trust in the GP partner community. 

Rather than pretend this never happens, let's rid the GP community of such problems.  If these stories can help customers learn and become more informed, and partners can be better educated, these hopefully rare situations will become less common, and the 'bad' partners will either clean up their act or go out of business.

Customers should get second opinions, and perhaps even shop around for quotes on a large project.  And partners should know better than to have a rogue developer write bad integrations--although I don't have any great ideas on how that self-realization might occur.

In the meantime, I'm stuck trying to fix a problem with this nightmare integration so that it can continue to work until the client upgrades to GP 2010 and replaces it.

Answer:  In my experience, the fastest way to spot a potentially 'bad' .NET GP integration is to look at the Windows Forms in the solution.  If the form includes a big image of the partner's logo or a really "pretty" user interface with fancy graphics, that should be a warning sign.  I've been asked to fix several horrible integrations, and the one thing they all had in common was that the developer clearly spent time making the forms look pretty and including a ludicrously large image of the partner logo.  And when a developer is wasting time doing that, I've found that they typically didn't spend adequate time on the code, often having no idea how to properly develop a Dynamics GP integration.  Sure enough, this integration consisted of one windows form, with one button (that only called a stored procedure), and a giant image of the partner's logo. 



Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified Professional.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com

Forecaster TRANSLATE -vs- TRANSLATEL

Subtlety is an art, right?  So it goes that the difference between the TRANSLATE and TRANSLATEL tasks in Forecaster is a very subtle one.  For those of you unfamiliar with currency translation in Forecaster, it actually works on a pretty basic idea.  You have an input set with a "originating" currency, and it is mapped to an input set with a "functional" currency.  When you save the "originating" input set, it translates to the "functional" input set (if you have set it up for auto-translate).

Even with auto-translate selected, you may still have need to use the TRANSLATE and TRANSLATEL tasks. So here is the difference:

• TRANSLATE- This task will translate all departments/assignable segments in the "originating" input set to the "functional" input set.  This would apply when all segments need to be translated.
• TRANSLATEL- This task will only translate those departments/assignable segments specified in the line set defined in the task. So, for example, if I set up a line set (Build>>Lines) with only three departments/assignable segments in it, only those three segments will be translated when the task is run.  This would apply if only certain segments need to be translated.

The difference is a subtle one, and it is even more subtle when you set up the task using the wizard.  First, you need to use Build>>Lines to create a line set of the departments/assignable segments to be used in the translation.  Then you need to create the task:

• Data>>Tasks
• Right-click, choose "New"
• Mark "Create Using Wizard"
• Click "Next"
• Click the "Insert Task Package" button in the upper right hand corner

• Choose the "Perform Currency Translation" task from the drop-down list
• Select the Input Set for translation
• Set the Translation Scope
• If you want this to be a TRANSLATEL task, you need to then mark the "I will choose a set of lines with assignable segment (Department) IDs for which translation is to be run" and then select the line set
• Click OK

So although it is a subtle difference, it is an important one if you only need to translate some departments/assignable segments.

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers.

Warning! Implementation Trauma Ahead

Here I am, sitting in hotel room in Indianapolis, where the task popped up reminding me to write my weekly (okay, okay, sometimes not quite weekly) blog post.  And I have to admit I am a bit uninspired for some reason. Maybe it's just the natural slow start that comes off of a holiday weekend.  Or maybe it is a symptom of being in a training state of mind for an end-user training tomorrow.  Whatever it is, I hope our legions of fans will indulge me a bit :)

Lately I have been spending a fair amount of time training and conducting study halls on Microsoft Dynamics Sure Step.  The conversations in class and afterwards bring to mind the so-called "sins" that seem to repeat themselves again and again.  So, here is my list of implementation "sins", I have created two lists-- one for customers and one for consultants.  I know this is a bit self-indulgent (I warned you earlier!), and I am not suggesting that I have not committed each and every one of these sins once or twice (or more often!), but I thought I would share the list and see what else you all would add.  In no particular order...

Top 5 Consulting Sins

1. Assuming you are the sole reason for the success/failure of the project
2. Forgetting that customer service is important even during the heat of an implementation
3. Ignoring risks as a way to avoid difficult conversations and/or to not "rock the boat"
4. Forgoing proactive change management for many of the same reasons as #3
5. Losing yourself in the "weeds" and forgetting the reasons/goals for the implementation

Top 5 Customer Sins

1. Assuming that the consulting team is the sole reason for the success/failure of the project
2. Approaching the consulting team as adversaries instead of partners
3. Underestimating the organizational change associated with implementing software
4. Not placing value on the time spent by employees on an implementation
5. Inadequately voicing/sharing your goals, whether due to limited budget, resources, or time (or energy!)

Later this week, I will give you more background on each of these.  They are all common themes that come up in class after class, study hall after study hall, conversation after conversation.  Please share your own as well!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers.

Lesson Learned- Importing Tax Details

Last week, a client asked if they needed to manually set up 100 tax details in Dynamics GP.  We are in the testing phase, so I knew it wasn't a matter of setting these up once...but twice if we did it manually (for the live database and for the test database, which couldn't be restored at that point due to other dependencies and testing).  So I did what I shouldn't have done.  I said, "Sure, we can do a quick table import".  Now, to be clear, the issue is not using table import.  It is that I used the work "quick".  (For those of you not familiar with table import, Mariano Gomez has a great blog post on the subject.)

So I took a "quick" look and imported the tax info to the Sales/Purchases Tax Master (TX00201).  When importing to this table (first lesson learned), first set up a record manually so you can see the proper settings for all fields (there are many that are not obvious and need to have the correct defaults set in the import).  Well, that was quick.  One table, check links (Microsoft Dynamics GP, Maintenance, Checklinks, Company), done.

Well, not so quick.  When users entered transactions using the tax details, all went fine.  But when they tried to post (even though there were no errors on the edit list), the posting journal would return "detail" errors and the batch would be sent to batch recovery.  And when we tried to manually delete the tax details through GP, we would get "record locked" errors.  Ugh.  Ugh.  Ugh.

Hunting.  And berating myself for not being more measured about it.  And then I found the Sales/Purchases Tax Summary Master (TX00202).  Ah-ha.  For every record saved in the TX00201, a record is also created in the TX00202.  So I needed two table imports, not one.  The TX00202 is a rather simple table storing historical summary information.  Easy to populate, but again, check an entry that has been made directly in GP to determine how to correctly populate the fields with defaults in the TX00202 table.

Good luck, hope a few of you can learn from my mistake!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers.

Use Tax and Dynamics GP- Match Made in Heaven?

For a lot of businesses, use tax is an annoyance for the most part.  There isn't enough volume to necessitate purchasing a solution, but the need to stay in compliance is vital.  So what to do with little money to spend on the issue, and no ready-made solution in Dynamics GP?

I have to give credit to the following solution to Cindy Boersma at BKD.  After many iterations of trying to find a solution, she came up with this final process -- all I did was go through and document and test.  And it seems to work like a charm.

Step #1

• Set up a "dummy" tax detail, Microsoft Dynamics GP, Tools, Setup, Company, Tax Detail
• Type should be set to "Purchases", Based On "Percent of Sale/Purchase", and percentage should be left at zero

Step 2

• Create the actual Use Tax Tax Details, one for each jurisdiction you need to track Use Tax for
• The important points here are that the Based On should be set to "Percent of Another Tax Detail", the Percentage should be the actual use tax percentage, and the Based on Detail should be the dummy detail you set up in Step 1

Step 3

• Enter your invoice information, Transactions, Purchasing, Transaction Entry
• Then click the Tax expansion arrow to open the Payables Tax Entry window

Step 4

• Enter use tax information
• Select the appropriate Tax Detail ID (created in Step 2)
• Enter the taxable portion of the purchase in the Total Purchase field (yes, yes, I know that is a little odd)
• Because the tax is based on a 0% tax detail, no taxable amount or tax is calculated.  This is good thing, as you don't want the tax amount to be added to the invoice.
• Repeat for additional use tax codes.

Step 5

• Create a custom report to pull the transaction tax detail information (PM10500, PM30700) and join it to the tax detail setup (TX00201).  You can then pull a report with the transaction information, including the total purchases amount (used for the taxable amount) and the tax setup information which includes the percentage of tax.  The report can then calculate the amount of tax owed.
• This can be done with SmartList Builder or SQL Reporting Services.

Yes, this process requires a custom report...but the value is that the process is very straightforward for users.

Another post to check out, which outlines some other methods of recording Use Tax in Dynamics GP:

http://www.socius1.com/purchasing-tips-and-inventory-tricks-in-dynamics-gp/

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers.



Another Day. More Convergence.

Another long couple of days here in Atlanta, but good ones.  Started out Tuesday with attending an interactive discussion on creating and deploying custom reports.  It is always great to hear the fabulous things that customers and partners are doing with the variety of tools we now have available to us (SQL Reporting Services, Management Reporter, Report Writer, Word Templates, SmartList Builder, Excel Report Builder, SQL Analysis Services....etc etc etc).

I then headed on over to check out a session on AX and Sure Step led by Chandru Shankar, Architect of Microsoft Dynamics Sure Step.  It was great to see a packed session, and lots and lots of questions for Chandru and his fellow presenters afterwards.

Then it was off to work in the Hands on Labs.  We had some great content this year, including guided and recorded demos using the Demomate technology.  Attendees can get more info on these demos by emailing info@demomate.com.  I have also received word that the self-paced lab manuals will be available on the Virtual Convergence site in the coming days.  In the GP world, we had some guided labs on R2 (coming up for release in May) as well as some great content on GP 2010 and SQL Reporting Services.  As a person who learns best by doing, I love these resources.

Attendees hard at work in the lab area...

I also managed to make it to a couple of sessions on Tuesday afternoon regarding reporting in GP 2010 R2.  Some things I am personally excited about:

• Customizable email messages with variables-- so now when emailing a customer their invoice, you can reference the number in the email body! Very cool.
• Business Analyzer-- Desktop display of SQL Reporting Services, a bunch of new standard reports/metrics for us to play with!  And even the ability to link metrics in to the Navigation Lists within Dynamics GP.  Pick vendor(s) in a list, and dynamically see the metric update!
• Word templates for all-- the new World template tool (emphasized to be a technical developer tool, not an end user tool) allows you to create a word template out of any GP report.  Wowza!

Interested?  See more cool new features in GP 2010 R2 by checking out the "Feature of the Day" on the Inside Microsoft Dynamics GP blog.

So Tuesday concluded with a relaxing dinner and a drink with an old friend, and then back to the room to finish up email for the day/night.  Wednesday began way too early with a trip over to the conference center early to finish up some labs before the keynote.  I was very excited to see Malcom Gladwell speak, since I am a fan of his books.  As usual, he dove in to the details of the real influences on everyday events like ballgames and the perceived home field advantage.  Sadly, I had to break out early to head over for my final shift in the labs.  There was definitely a brisk business when the labs opened, from attendees wanting to "fuel up" on knowledge before heading home.  Popular topics this year-- Management Reporter, Workflow, Business Analyzer, and definitely SQL Reporting Services.  I also saw a number of folks who were looking for labs on PowerPivot (which we had in the Office lab area).

Shift done.  Goodbyes done.  Off to lunch and airport.  Now on flight home (dontcha love inflight internet).

Wishing everyone safe travels home today and tomorrow.  It's been a great conference.  See you next year in Houston.

Take care,
Christina

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers.

Monday. Convergence. Atlanta.

After a successful slow start yesterday with working in the labs, managing a Sure Step study hall, and a fun evening at the Georgia Aquarium and the World of Coca-Cola, we are off and running at Convergence 2011 here in Atlanta, GA. 

First, a word about the labs....for GP specifically...we have some good ones.  Include some recording demo-mates for GP 2010 R2 which is coming soon. We also have a number of self-guided and recorded labs on SQL Reporting Services, Management Reporter, and a variety of other analytic and customization tools for Dynamics GP.  Exciting stuff.  If you are here, and haven't already, stop by the Community and Learning Center to check out the labs.  Staffed by MCTs, the lab area (in my humble opinion) is one of the great resources at Convergence. 

Second, behind each lab section are the product groups.  What a great opportunity to speak with many of the rock star support folks we have come to know (and let's admit it, love) over the years are hear.  Talk their ears off, learn as much as you can.  Definitely a great resource while you are here, but I think people often forget about the Community and Learning Center and become uber-focused on sessions only.

And third, the Keynote this morning was what we have come to know and expect at Convergence--  energy filled and real world focused.  Personally, I always leave excited for what Dynamics (in general, and GP in particular) is doing for companies today and even more excited for what is coming in the future-- more capabilities, more business intelligence, more flexibility, more simplicity-- more, more, more.  But we all know I am nothing short of enthusiastic when it comes to Microsoft Dynamics :)

More later on the Sure Step Study Hall and a lingering Use Tax issue I have been working on. Until then, the Microsoft Conference Store is calling my name.... Stop by the labs and say hi if you are in town (I am the one in the pink BKD hat).

Error Importing Custom VBA Windows on GP 2010

This week I upgraded some old GP 9 Modifier & VBA customizations to GP 2010.

The upgrades went smoothly on my GP 2010 development server, and everything worked fine.  I exported a package with all of the modified forms, VBA code, and three custom VBA user forms and provided it to the customer to import.

When they tried to import the package, they received a message "Errors during load", which then referred to a log file. 

 In the log file, we saw the following message.

"Class {C62A69F0-.......} of control VerticalLookupForm was not a loaded control class."

When I checked my package file, that GUID was the one assigned to my custom user form.  So for some reason the form was not loading.

I then tried exporting the form from VBA on my development server, and tried importing the .frm file on the customer server, but that didn't work either. 

I then removed all mods from the client's server, opened VBA, and tried to create a new user form.  Even that produced an error.  So that told me that the issue was with the client's GP 2010 server, and not necessarily with my package file.  The error was:

Class not registered.
Looking for object with CLSID:{AC9F2F90-E877-11CE-9F68-00AA00574A4F} 

 

Fortunately, this is documented in KB article 18250.   The VBA problem is caused when the file FM20.DLL is not properly registered on the machine.

So, it's an easy fix to run regsvr32--in our case it was a 64-bit server, so the command was:

regsvr32.exe "C:\Windows\SysWOW64\FM20.DLL"

After running that command, user forms could be created in VBA, and my package file imported just fine.

 

The problem occurred on a GP 2010 server, but I assume that the issue can occur with other versions of GP.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified Professional.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com