Tuesday, March 1, 2016

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.


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


Miguel Lozano said...

Very good article Steve, in addition, there is a possibility that the same document number could be inserted several times with different DEX_ROW_ID in SOP10100, for example, in the case the user prepares to enter a document and a new number is generated (and inserted in SOP10100), but leaves the window without entering a customer number, GP will actually delete the record from the table. Then another user launches SOP window again, the SAME number is generated (for that SOPTYPE) and this could be repeated until the customer number is entered. This is one of those "unique-handling-situations". I am assuming this was designed to prevent duplicating document numbers in a multiuser environment?
Miguel Lozano

Matt said...

Does Microsoft know about this so they can issue a fix?

Unknown said...

I would guess that the attempt to require CUSTNMBR was the thought on the shell record that GP inserts so that nobody else can enter the same transaction number. Or maybe not - who knows?

However the end user fix is pretty easy.

Everything about the requesters is controlled from the eConnect_Out_Setup table.

If you want new ones, add a record into that table.
If you don't like how the existing ones work, change the table.

So in this case the field for "required" is REQUIRED1. And it seems that i have three records for CUSTNMBR on three lines.

If we don't want to have a required field for the sop header, we can remove it.

update eConnect_Out_Setup set REQUIRED1 = '' where ALIAS = 'SO_Trans'

I believe marking/unmarking the checkbox will drop/re-create the trigger and this time shouldn't look at a required field.

patrick roth

Mariano Gomez said...

An easy fix is to make the trigger a FOR INSERT, UPDATE trigger:

ALTER TRIGGER [dbo].[eConnect_Sales_TransactionSP10100_InsertTrigger] on [dbo].[SOP10100]

The trigger logic as is will do nothing the first time around (as is currently happening), but the second time around when the CUSTNMBR column (or any other column) is updated in the table, it will fire as intended.

Thinking about it, since an update is what's being done, it will make a lot of sense to have the trigger fire, each and every time a column in the header row is updated as you will always want to have the latest values for your integration.

Mariano Gomez, MVP

Steve Endow said...

Thanks Mariano.

In the case of my client, I found that Microsoft had created a custom insert/update trigger on SOP10100--they didn't even bother to use the standard Requester triggers. Which tells you something... Presumably they knew about the default SOP trigger.


Mariano Gomez said...

Well, it's always good to know there's a solution...out there somewhere, LOL!

Mariano Gomez, MVP