Using eConnect from Dynamics GP Dexterity

I have recently been switching all of my Dexterity code to use eConnect procedures to add data to Dynamics GP.  I find this combination greatly simplifies both my Dex code and efforts to develop and maintain the code.

As far as I know if you use Dexterity you have three choices if you want to get data into Dynamics GP tables:

1. Use the process described in the SDK.  This seems very complicated to me whereby you invoke forms in an object manner.  I'm opting to avoid this until I can find something that points me to how this can be done in a simple and straightforward fashion.

2. Code directly to GP tables from Dexterity.  This is borderline foolish.  If you develop this way when you move to a new GP release you must adapt your code to any table or business rule changes that apply to those tables you interact with.  Not only that you must know and understand the rules of every single field in the table before you can load data.  I started doing this but knew all along I would not be able to sustain this code.  That's why I chose the next option.

3. Use eConnect calls from Dexterity.  This has turned out to be a very simple way to get data into Dynamics GP safely and with minimal effort for GP upgrades and updates.  I have been using eConnect for years by way of Scribe Insight and it's extremely robust, reliable and fast.  Using eConnect makes upgrades painless.

I don't know what other Dexterity developers are using to get data into GP but I am offering to publish a How-To guide if there is interest.  Please let me know in the comments if you would like me to publish and I'll proceed.

Dynamics GP Sales Tax Qualifiers

We recently noticed that sales tax for local Florida county jurisdictions stopped calculating correctly.  While the proper functioning of this preceded my hiring, I'm assured at one time this worked.

What is required in certain FL counties there is a maximum taxable sale amount for a transaction of $5,000.  This means if a total sale exceeds $5,000 the tax only applies to the first $5,000, and the rest is not taxable.  We have the maximum taxable amount set up at $5,000 for the county tax detail, as required for this to work.

Well this apparently stopped calculating correctly in GP at some point, definitely before 2017.  Yes, at one time it did work, now it doesn't.  GP is calculating the tax on the total amount and ignoring the $5,000 max for the Tax Detail ID.

The source of this issue is the "Qualifiers" we have set to "Unit Amount" on each of these Tax Details.  Instead this should be set to "Invoice Total".  (See GP help for definition of the Qualifiers.)

Regrettably the UI does not allow changing this field once set and tax applied.  So it is necessary to override the value in the tax table using T-SQL to change it from Unit Amount to Invoice Total.

Once changed the tax resumed the proper calculation.

We are not sure when this "Qualifiers" field showed up in GP but it must have been after we created our tax details.  We've been using Dynamics GP since 2000.

Unhandled database exception from GP Dexterity

In my Dex app I added some new code and inadvertently did some sloppy work that resulted in the following error showing up.

"Microsoft Dynamics GP: Unhandled database exception:  A cal to a stored procedure has changed the transaction nesting level."

That's it - exactly as it was displayed.  Click OK and carry on.

I traced the problem and it was my own doing (of course.)  In one of my T-SQL stored procedures I had a RETURN statement fire in the middle of a SQL Transaction.  Bad dog!!!

I changed the code to allow a graceful exit through the SQL transaction and the error disappeared.

Could not find any reference to that error so here it is!

Running stored procedures from Dexterity vs. SSMS - an oddity

I was developing a customization for Dynamics GP using Dexterity.  The module I was writing in Dex called a stored procedure to load some data into a Dex table.

During testing no data was loading to the table so I started trouble-shooting to find the problem.

Most odd was the stored procedure returned data when I ran it from SQL.  However when it was run from Dexterity no results were returned.  A later iteration resulted in different counts between a SQL run or a Dex run.

I played with it enough to find the source of the problem:  One of my restrictions in test was to limit results using a hard-coded date value in the T-SQL WHERE clause.  When this date was set to 11/30/17 in the T-SQL WHERE I got no data returned in Dex.  When I changed this date to 11/1/17 I got all results returned, as if there were no date at all.  Again when run from SSMS the stored procedure produced expected results.

When I changed the year to 2017, i.e. using 11/1/2017, I got the same result from Dex as SQL.

Why does a stored procedure change behavior when it's being called from Dexterity versus when it's run from Management Studio?

WTF!!!