What happens if you lose your Dynamics GP administrator or developer?

By Steve Endow

I received a call this morning from a company that I've worked with previously, having assisted them with a custom eConnect integration and with Post Master Enterprise for automating their batch posting.  The caller introduced himself and explained that the company's GP developer, with whom I've worked in the past, was in the hospital with a serious issue, and he may not live.

The company has been using Dynamics GP since at least version 8.0, and they have over 300 active Dynamics GP companies.  The company has developed several custom integrations over the past 5 years to automate several system integrations.  Things are still working, but now that the GP developer isn't around, they are finding some integrations that aren't occurring.  And they don't know where to start looking.

They are now attempting to reconstruct the business processes, integrations, data sources, and many other things that the developer managed.  They generally know what he was responsible for, but nobody else in the company has any knowledge of the specifics.  They don't know where source data files come from, whether they are transformed prior to import, where they are saved, or what integration processes them.  Most of the integrations are automatic, but they are finding a few that appear to involve some manual processing.

I spent about two hours working with the customer, scouring logs and files on their terminal server, to identify some information about the integrations, identify some source code, and get some understanding about how things work.  During the call, I didn't see any signs of source code control or documentation.

While we learned some things about some integrations, they were looking to process a specific type of monthly AP invoice, and unfortunately, we were unable to find anything that appeared to be related to that monthly import into 200 company databases.

They are still trying to get the developer's laptop and see if they can access the files on it in the hope that it may have some clues as to how the monthly AP invoices are processed.

It's an unfortunate situation in many regards.  A typical response might be "Why doesn't anyone else know what he was working on?".  Yes, in an ideal world, you would have a backup employee for every task who can step in and take over another role.  But if you are a GP consultant, you've probably seen dozens of companies in the SMB space where most employees exclusively handle certain tasks.  Most mid-market companies don't have the resources to have two employees doing every task--even critical ones.

While IT is sometimes general enough that a system administrator might be able to pick up some SQL or GP related tasks, it isn't common for an average GP customer to know the details of custom code, and I speculate that it's even less common for a GP customer to have more than one GP developer on staff.  The customers and partners I work with might know that Steve Endow is the guy to call for a particular integration, but other than the documentation I provide with all of my work, they wouldn't know much about the details of the work that I've done and all of the context related to some of the projects.

I think there are many lessons to this story.  Off the top of my head, a few come to mind.

1. Centralized source code control should be used without exception
2. Documentation is important, even though nobody likes creating it, maintaining it, or reading it
3. Being organized is very important if you expect to cope with the loss of a key employee
4. It is prudent to maintain a "Hit by a bus" plan

Source code control is probably the easiest practice to maintain.  Visual Studio has excellent integrations with Team Foundation Server, Git, and SVN, so other than the initial learning curve of each of those source code control tools, it's just an extra button click in Visual Studio.  There just isn't an excuse for a developer to not use some type of source control and ensure that someone else can access the repositories in an emergency.

Documentation is disappointingly rare, particularly for internal IT projects or where employees are doing custom development.  And even if something is documented, the documentation usually doesn't cover all of the hundreds of details that someone might need if the main developer is gone.

Being organized can be a challenge.  Some people are organized and have good habits to keep things organized, while others aren't.  Some people are organized in certain areas, but not in others.  And some projects start out organized, but after 5 years of development, the scope of a solution can grow to a point where code can become very difficult to understand if it wasn't refactored or reorganized over time.

And then there is what I call my "Hit by a bus" plan.  If I'm hit by a bus, what information would be required to minimize the disruption to my family, my colleagues, and my customers?  Perhaps a nicer name might be an "Extended vacation plan".

I provide documentation with every project I develop, and all of my source code is hosted online.  I provide partners with access to their code repositories so that if I disappear, another developer can step in and update the code if necessary.  Even with all of this in place, I'm sure there are still hundreds of missing details and unanswered questions, but all of the critical information is available to move forward.

It's not something that we typically think about or even want to think about, but I think it's a prudent part of business continuity that any company needs to work on.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Query the functional currency for all Dynamics GP company databases

By Steve Endow

When installing GP Web Services, all company databases must have a functional currency assigned, otherwise the Web Services Configuration Wizard will give you a validation error.

If you have several company databases, it can be a bit of a hassle to login to every one to see which one does not have a functional currency assigned.

And I suppose there might be some other reason why you would want to see or verify the functional currency value in all of your companies.


Here is a query that will show the functional currency for each company database.

DECLARE @INTERID varchar(10) = ''
DECLARE @SQL varchar(MAX) = ''

DECLARE INTERID_cursor CURSOR FOR
SELECT INTERID FROM DYNAMICS..SY01500

OPEN INTERID_cursor
FETCH NEXT FROM INTERID_cursor INTO @INTERID

WHILE @@FETCH_STATUS = 0
BEGIN
       IF @SQL <> '' BEGIN SET @SQL += ' UNION '; END
       SET @SQL += ' SELECT ''' + @INTERID + ''' AS INTERID, (SELECT FUNLCURR FROM  ' + @INTERID + '..MC40000) AS FunctionalCurrency';
       FETCH NEXT FROM INTERID_cursor INTO @INTERID
END

CLOSE INTERID_cursor
DEALLOCATE INTERID_cursor

EXEC(@SQL)


It outputs the INTERID database name, and the assigned Functional Currency.

Enjoy!

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Replacing the root node on eConnect Requester GetEntity XML

By Steve Endow

If you are reading this, and if you actually understand the title of this post, you have my condolences, because it probably means that you are going through the punishment I'm currently going through of having to use eConnect Requester and GetEntity.

To be fair, eConnect Requester GetEntity isn't that bad.  But there is one seemingly tiny difference in the XML that GetEntity returns, and the XML that is generated by the eConnect Outgoing Service that is sent to MSMQ.

In the lovely eConnect integration that I'm currently updating, this is what the XML looks like when it is sent to MSMQ by the eConnect Outgoing Service:

< Sales_Transaction:root xmlns:Sales_Transaction="http://microsoft.com/eConnect/Sales_Transaction.xsd" >
< eConnect ACTION="2" Requester_DOCTYPE="Sales_Transaction" DBNAME="TWO" TABLENAME="SOP10100" DATE1="2016-03-10T15:51:34.117" SOPNUMBE="ORDST2244" SOPTYPE="2" >
< SO_Trans >
< SOPNUMBE >ORDST2244< /SOPNUMBE >


Notice the fancy root node, with the explicit namespace on the root node, and the xmlns namespace identifier (or whatever it's called).

This is what GetEntity returns:

< root >
  < eConnect ACTION="2" Requester_DOCTYPE="Sales_Transaction_RRD" DBNAME="TWO" TABLENAME="SOP10100" DATE1="2016-03-10T15:51:34.117" SOPNUMBE="ORDST2244" SOPTYPE="2" >
    < SO_Trans >
      < SOPNUMBE >ORDST2244< /SOPNUMBE >

Notice that it only supplies the root node, with no namespace.

I originally hoped that wouldn't matter for my project, but as my luck would have it, the code I'm working with uses XSL to transform the eConnect XML into the XML format for the trading partner.  And that XSL will not work with the plain old "root" node.  And this XSL file is 350 lines long.

If you have ever worked with XSL, you can understand my desire to not want to touch it or make any changes.  Life is hard enough without XSL.

So, the path of least resistance was to modify the GetEntity root node to have it match the eConnect Outgoing MSMQ root node.

Great! Sounds like a plan!  Except I had no idea how to do that either.

It turns out, from what I could find, that the XMLDocument API does not have a supported means of changing the name of a node.

There might be a way to assign the namespace to an existing node, but I don't think I was able to find any good approaches to that, so I cobbled together a few samples of building an entirely new XML document, with a new root node, using the XDocument object.

It happened to be a good lesson, as I've never used XDocument before, and it is much easier to work with than XMLDocument.

So here's what I cobbled together after much trial and error.


public static string RenameRootNode(string xmlString)
{
    try
    {
        XDocument doc = XDocument.Parse(xmlString);
        XDocument newDoc = new XDocument();

        XNamespace ns = "http://microsoft.com/eConnect/Sales_Transaction.xsd";
        XElement newRoot = new XElement(ns + "root", 
            new XAttribute(XNamespace.Xmlns + "Sales_Transaction", "http://microsoft.com/eConnect/Sales_Transaction.xsd"));

        newDoc = new XDocument(newRoot);
        newDoc.Root.Add(doc.Root.Elements("eConnect"));

        return newDoc.ToString();
                                
    }
    catch (Exception ex)
    {
        throw ex;
    }
}


I don't fully understand the strange nesting available with XDocument and XElement, but it works, and given my limited time, that is plenty good for me.  And again, there may be a way to assign the namespace to the document and the root node to produce equivalent results--if so, go for it.

But this function converts the GetEntity XML so that it matches what is generated by the eConnect Outgoing service, and my lovely XSL transform works properly with it.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Confirmed: 8 year old bug in eConnect Requester SOP Transaction trigger

By Steve Endow

Last week I wrote about an issue where eConnect Requester didn't seem to properly capture inserted SOP Transactions.

At that time, I was able to work around it for one task that I was working on, but while working on the next part of the project, the same issue with eConnect Requester came up again, preventing me from continuing, yet again.

So I finally dug into the eConnect Requester SOP10100 Insert Trigger and found what appears to be an obvious bug that must be at least 8 years old.  The problem exists in GP 2015, 2013, and 2010, and I wouldn't be surprised at all if it's in GP 10 and earlier versions of eConnect.

Clearly the Insert "SO_Trans" record in eConnect Requester was never tested.  As in never QA tested, and never subsequently tested in the last 8+ years.  I guess if the bug has been around this long, nobody noticed, or nobody cared, and the few dozen people who use eConnect Requester just worked around it without questioning why it doesn't work.

Unfortunately, I'm stuck trying to clean up a 10 year old integration that uses the Requester extensively, with MSMQ no less, and so I will have to figure out a way to deal with this bug.

So let's look at where the developer of the trigger went awry, and why the Sales Transaction Insert trigger therefore doesn't work.

First, when you enable the Sales_Transaction "SO_Trans" Insert Trigger, eConnect Requester creates a trigger on the SOP10100 table. So far, so good.

And if you look at that trigger, it has a bunch of SQL in it that looks impressive, and gives you the impression that it might actually do something.  All fancy with a cursor, looping through the inserted records.

But if you enter a SOP transaction in Dynamics GP, the eConnect_Out table will remain empty.  Nada.

If you are feeling particularly adventurous and have lots of free time on your hands, you may wonder:  Why isn't it working?  (Or you could ignore it like everyone else apparently has)

You could then fire up SQL Server Profiler and run a trace, including the SP:StmtStarting or SP:StmtCompleted events to capture the trigger activity.

And the SQL Profiler activity will show that the trigger is indeed firing, the cursor is fetching, and all sorts of fun is definitely occurring.

But the eConnect_Out table remains empty.

So, since we know the trigger is firing, that tells us that there is some condition that is causing the trigger script to ignore the inserted SOP record.

After thinking about it for a few seconds, it dawned on me, and I bet that I knew why.

So why would an Insert trigger on the SOP10100 table not capture a record when a SOP transaction is saved?

Do you know?  Try and guess.

Well, if you've ever gone spelunking into the bowels of the SOP tables during entry of a SOP transaction, you may know that when you start to enter a SOP transaction, as soon as the Document Number (SOPNUMBE) is displayed in the Sales Transaction Entry window, a placeholder record is inserted into SOP10100.

What does this placeholder record look like?  It looks like a dummy record with a lot of blank values.

In this example, I show the customer number and Dex Row ID first, as those are important.

So now we have a placeholder record that sits there, but it's clearly incomplete.  That record only gets updated when you click Save on the Sales Transaction Entry window.

You will want to notice two things about this record.  First, the CUSTNMBR field now has a value.  Second, the Dex Row ID value is the same as before.  

Why is the Dex Row ID value important?  If it is the same value, that tells us that the record was subsequently updated, and not deleted and re-inserted.

Do you see where this is going?

Let's jump back to the SOP10100 Insert Trigger script, and look a little closer at the SQL.

And there is the fatal flaw.

            SELECT  @required = CUSTNMBR

            FROM    inserted 

            IF @required <> ''

The developer of the trigger decided to key off of the Customer Number, indicating that the value was required in order for the transaction to be written to eConnect_Out.  Makes sense.

But as we saw before, when the record is Inserted into SOP10100, the CUSTNMBR field is blank, because the record is only a placeholder field--GP doesn't yet have a customer number value when it inserts that record.

But, you might think, the customer number value is eventually inserted into SOP10100, right?

Technically, no.

As we saw earlier, the Dex Row ID doesn't change for the placeholder record, telling us that an Update statement is used to add the customer number value--not an Insert statement.  And an Insert trigger does not fire when a record is updated.

Because of the way that GP uses placeholder records in SOP10100, and because of the way that the SO_Trans trigger is written, the eConnect Requester SOP10100 Insert trigger will never actually do anything, and will never insert a record into the eConnect_Out table.

Ugh.

So if you are another poor soul who must face this reality, what are your options?

One option appears to be to use the eConnect Requester Sales_Transaction Line trigger instead.

Depending on your specific requirements, this may work, as it will at least allow you to know that a new Sales Transaction was entered.

There is a very slim chance that you might be able to use the eConnect Requester Update trigger for SOP10100, but that is unlikely, as any update to an existing SOP transaction will write a record to eConnect_Out, and you would have to sort out which ones you need to deal with.

For now, I'm going to try the Line trigger on SOP10200 and see if that will work, and hope that there aren't any downsides or side effects.

Happy coding.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com