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