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

eConnect automatic document numbering

By Steve Endow

Once again, today I discovered that I didn't know something that I should have.  I'm sure I could come up with a good excuse, but you would think I would have at least stumbled across it before now.  Maybe I knew about it 10 years ago but forgot.

While eating a very tasty panang curry at lunch today, I was reading the ENTIRE eConnect Programmer's Guide PDF, trying to figure out how to eliminate the need for MSMQ from a customer's ancient eConnect integration.  That's a completely different story, but while reading that eConnect PDF, I learned something.

One of the common questions about eConnect is "How do I generate document numbers?"  And the developer then has to learn one of the techniques to generate document numbers.  Once you have the code it isn't too bad, but with slightly different code for different document types, it's a bit of a hassle.

And so today, with my tongue on fire from my curry, I read this in the eConnect guide, and stared in disbelief:

When I asked if he knew about this feature, Mr. Tim Wappat just shook his head in dismay, so disappointed with us American developers.

I just tested this and sure enough, it does work.

Will I use it?  Maybe.  But I regularly have integrations where I need to update a source data field with the GP document number after it is successfully imported.  In those cases, if I generate the document number first, I can assign it to the transaction, and then use it to update the source data--no need to parse the resulting eConnect doc to extract the number.

But in cases where you don't need to assign a document number and don't care what number was assigned to a transaction, sure, it's a convenient option.

Keep learning.

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

eConnect Requester for Sales Transactions doesn't seem to work

By Steve Endow

I'm having to setup eConnect Requester to simulate a client environment and modify an existing Dynamics GP eConnect integration.  I opened the eConnect Requester Setup, selected the Insert tab, checked the box for Sales_Transaction - SOP10100, and clicked Update.

I then entered a SOP Invoice, but when I checked the eConnect_Out database table, but it was empty.  Puzzled, I double checked the Requester Setup selection, but it looked okay.

I then ran a SQL Profiler trace while entering a SOP transaction, but I didn't find any trace of the trigger firing.

I then tried check the Sales_Transaction Line option in the Requester Setup and tested again, and this time I did get records in eConnect_Out--but only when I had a line on the SOP transaction.

I see the trigger on SOP10100, but for some reason it isn't firing.

So for some reason the main SOP10100 trigger doesn't work, but if you enable the trigger on SOP10200, you can capture the SOP insert activity.

I don't currently have time to dig into the cause, but hope to eventually look into it if I ever have some of exceedingly rare free time.

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

Trace database triggers using SQL Server Profiler

By Steve Endow

I generally try and avoid using triggers, but they do play an important role, and occasionally I do have to develop them or interact with them.

Today I was testing eConnect Requester with Dynamics GP to record the insert of Sales Transactions.  I turned on the Sales Transaction Insert trigger in Requester Setup, and then entered a SOP Invoice.

When I checked the eConnect_Out table, it was empty.  Hmmm.  I then entered a Sales Order.  That transaction did result in a record in eConnect_Out.

Puzzled, I thought I would trace the activity to see why eConnect Requester was not working with Invoices.

In my first Profiler trace, I didn't see the trigger activity.  After a quick search, I found this StackOverflow thread (who doesn't end up on that site regularly?), which noted that the SP:StmtStarting and SP:StmtCompleted events should be selected to trace triggers.

Once you have those, you can run your trace and your should see the trigger activity.

There doesn't appear to be any flags to indicate that the activity is coming from a trigger, so you need to know what you are looking for, but if you are tracing a trigger, you will presumably have some keywords to search for in the profiler trace data.


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

Retrieving a value from every Dynamics GP company database

By Steve Endow

Back in November 2015, I had a brief post about running a SQL statement against every Dynamics GP company database.  In that case, I needed to run a SQL update statement, so it was relatively simple.

But what if you want to retrieve some information from every single Dynamics GP company database?

The process is similar in that it uses a cursor, but if you want to have a nice clean result set, rather than a separate result for every database, it takes a little more creativity.

In this case, I needed to check the first GL account segment in every company database as part of a custom integration.  The customer said that each company database has a separate value for the first segment of every GL account, and that I can use that value to map external data to the appropriate Dynamics GP database.  So if the GL account in the source data is "15", I can find the company database with GL accounts of 15-XXXX-XXX, and that will be the database into which the data should be imported.

In addition to needing to map that first segment value to each database, I wanted the query to count how many different distinct segment 1 values existed in the company.  Each company should only have one distinct value, so if we find that there is more than one, then there is a problem and this technique won't work.

So here's what I came up with, which is a modified version of my November cursor example.

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 COUNT(DISTINCT ACTNUMBR_1) FROM ' + @INTERID + '..GL00100) AS Segment1Values, (SELECT TOP 1 ACTNUMBR_1 FROM ' + @INTERID + '..GL00100) AS CompanyID';

       FETCH NEXT FROM INTERID_cursor INTO @INTERID

END

CLOSE INTERID_cursor

DEALLOCATE INTERID_cursor

EXEC(@SQL)

This time, since I'm not running an update statement, I needed to use a Union to combine the multiple select statement results into a single result set.

As always, there are probably several ways to handle this requirement, but it was pretty easy to modify my existing cursor based query and get it done quickly.

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

Updating a Dynamics GP binary database field in SQL Management Studio

By Steve Endow

There is a lot of background to this, but the short story is that a consultant needed to update the SOPHDRFL field in the SOP10100 table.  But it turns out that the SOPHDRFL field is binary--not your average field type.

I haven't done any research yet, so I'm not familiar with the values that GP stores in the binary fields, but there are a few:

So if you need to update one of these binary fields, how do you do it?

Well, I think I've had to work with SQL binary fields maybe once in my life, so I can never remember how to deal with them.  If you do some searches, you'll likely find some elaborate and complex techniques that are needed for certain types of binary field updates.

But in this case, in SOP10100, the SOPHDRFL field has a default value of 0x00000000, but it gets updated during certain SOP processes.  The consultant needed to set it back to the default value of zero so that a custom process could be implemented in SOP.

Poking around at some examples, I found a technique that appears to be pretty simple.  Maybe this is obvious or elementary to the poor folks who work with binary fields regularly, but I thought it was pretty clever.

Rather than attempting to directly update the field value, you can just declare a binary variable and then use that in an update statement.

DECLARE @bin binary(4) = 0x00000000
UPDATE SOP10100 SET SOPHDRFL = @bin WHERE SOPNUMBE = 'STDINV6161'

It seems to work like a charm.  Just make sure to check the binary field length that you are updating, as that appears to be important in the case of GP's binary fields.  Granted, this is a very limited, simple situation, but it seems like it could come in handy for other cases.

And that is your very obscure SQL tip of the day.

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