T-SQL and some stupid math

This is driving me nuts!

How does the following not yield 50%?

25/50

Well, unless you're Transact-SQL I suppose it does.  This is probably some well known "feature" that I've missed all these years.

Guess what yields 50%?

25./50

See what I did there?

Unless you include the decimal, or cast one of your operands as a decimal-included type (such as REAL, FLOAT, etc.) you'll get integer truncation.

Do we have that problem in Excel?  No chance.

Oh, make no mistake this is documented.  Does that make it ok?  I suppose it depends on whether you realize it or not when you use it.  What would the average programmer think?  I would suggest .5.  But since you're Microsoft T-SQL and your types are loose then you make the rules.

/rant

Dynamics GP Letter Writing Assistant experience

I had to re-enter the world of the Letter Writing Assistant (LWA) in Dynamics GP after a long hiatus.  This did not go well.

What we wanted was a letter to notify employees of the online pay stub service we were going to start using.  Perfect match for the LWA right?

I wanted to start with the Employee Memo that is already in GP.  It's blank but has a decent outline with the company name on top, addressed to an employee, and signed by the user.

To do this I opened the Letter Writing Assitant from Reports > Letter Writing Assistant.  Click Next from the "Welcome" window.  Choose "Customize the letters by adding new letters or changing existing letters."  From the Letter Maintenance window select Letter Category "Employee" and Create New Letter > "Start with existing Word document" and click Next.

Now things get murky here.  A window presents prompting for a "Document Name" with a folder browser beside it, and "OK" and "Cancel" buttons.  If you try to enter any document name, in fact any name at all or browse anywhere and type a file name you will receive the following error:

this file name is a reserved word or contains invalid characters

For this to work properly you need to browse to the GP Letters folder and chose an existing letter.  Doing so will open the existing letter in Word for editing.

If you truly want a "New Letter" you need to Save As a new letter name within the same Letters folder (i.e. for me it's the Letters\Employee folder.)  You can also navigate to the Letters folder and copy the existing letter to a new name first if you want.

I do believe this is a fairly dangerous way to create a "New Letter", as there is substantial risk you might accidently save over your existing letter if you forget to do the Save As with a new name soon enough.

So far a bit of a rough start in my view.  I will continue my journey in this world as we need to bring in data from an outside source to include in the letter.  That's a topic for another post.

Well the end result is, as many others have found, the Letter Writing Assistant in GP is fairly limited in the number of available fields.  As a result we are not able to utilize it for what we need.  Instead we will use Smartlist and Word mail-merge for this purpose.

"error retrieving/setting data - unable to retrieve notification monitor" message when attempting to access Site Settings

Just posted this on the Scribe message boards.

This is a new Insight 7.9.3 installation on Server 2016. ScribeInternal database is newly created on a remote SQL 2016 server. A domain user account is used for the Scribe services, all of which are running with no errors. UAC is off as is the domain firewall on the Scribe server.

The service account user is added to DCOM for the Scribe services and is a local admin on the box. The Scribe server name is correct in the appropriate Scribe tables. The Scribe service user is a sysadmin on the remote SQL server.

I will update with any resolution.

** Update

With the help of Scribe tech support we found that I used a dev domain service account user instead of the prod domain service account in the Scribe Console User group.

So this error shows up when your Scribe service account is not in the Scribe Console User group.

Using eConnect from Dexterity - Part 2

In Part 1 I introduced my reasons for using eConnect in Dexterity customizations of GP, and recommended installation of the eConnect SDK so you could obtain the (CHM) Programmer's Guide, which will be helpful in writing eConnect integrations.

Now I will introduce my basic framework for integrating the two technologies.

First, understand that, among other things, eConnect includes a bunch of stored procedures that are installed in every Dynamics GP database.

The Programmer's Guide will help identify which of these procedures you'll need.  When you figure out which you'll be using you can use SQL Management Studio to view the parameters you'll need to feed.

The above example shows the parameters needed to pass to the taGetNextJournalEntry eConnect procedure.  If you want to create Journal Entries in GP you can use this stored procedure to get the next number.  Oddly these "Next Number" stored procedures are not in the documentation.

You can reference these procedures directly from Dexterity, but I like to wrap them in my own stored procedures and call mine instead.

Notice the reference to taErrorCode in the Dynamics database.  Because I only want the next JE number I also take back any error information.  I follow this structure for all my eConnect procedure calls.  This makes the Dexterity call fairly simple:

Next time I will look at a transaction example.

Using eConnect from Dexterity. Part 1.

As I mentioned in a previous post eConnect is the tool of choice when integrating to Dynamics GP.  Dexterity is the tool of choice for customizing Dynamics GP.  You can use Dexterity to access all the tables in GP, and you can CRUD all that data as you'd like.  However you do so at the risk of introducing corruption in the GP database if you don't know exactly what you are doing.

Add to that the hazards of upgrades and frankly I'd just assume avoid all that trouble.  This is where eConnect comes to the rescue!

eConnect is the tool of choice for integrations to GP because it acts as an API to the data layer by way of the business logic.  This means it will default data where it can, and prevent you from putting invalid data where you can't.  And it's almost upgrade-proof.  There are situations where upgrades introduce new requirements on eConnect, but they are rare.  This makes upgrades much more manageable.

So if you are writing Dexterity code and want to integrate data to GP you can certainly use eConnect.  However it helps to understand how eConnect works.

As a longtime Scribe user I've learned that you need to use a bottom-up approach when integrating.  Since Scribe bases its GP integration on eConnect I never realized that the reason it did that was because eConnect required it.  It wasn't until I started to use eConnect directly that I realized Scribe did it this way because eConnect did it that way.

What this means is you have to insert your detail transactions before the header.  For example if you are trying to create a GL transaction you will have to create all the GL lines before you can insert the GL header.  This applies to most transactions in Dynamics GP.

If you don't have any experience with Scribe or eConnect (and perhaps even if you do) I'd suggest installing the eConnect SDK that comes with GP.  While this is not required there's a nugget in there that is the eConnect Programmer's Guide.  When you install the SDK it installs the Guide in both CHM and PDF form (Program Files (x86)\Microsoft Dynamics\eConnect xx\Help.)  In the back of the CHM is the eConnect Node References that will really help out.

For some odd reason Microsoft left the Node reference out of the PDF version of the guide.

In my next post I'll describe how I put eConnect to work from inside of Dexterity.

Loss of database access in SQL

I administer several SQL Server instances.  To connect to these I use my domain account which has Sysadmin privilege on each server.

Last night when I logged in to perform some updates I was greeted with the "server principal is not able to access the database under the current security context" message.  This was to a SQL 2012 server, again with my domain account.

Not only is my domain account a Sysadmin but I'm also a local admin on the server.  I was able to access the server just fine using SA.  I am also able to access other servers fine with my domain account.  Just this one SQL 2012 instance was giving me trouble.

This morning I've been searching the heck out of this and testing all kinds of things.  I was able to create a SQL user and give it Sysadmin and access all the databases just fine.

Finally I tried to take away and give back Sysadmin to my user - that didn't work.

What did work was dropping my user altogether, then adding it back and granting it Sysadmin again.  All seems well now.

Simply inexplicable!

More range mysteries

Revisited my scrolling window of the ranges from my previous article and found some very strange results.  Any display of data more than the window size would result in very odd scrolling results.  I even tried a brand new form and still got erratic scrolling.

Looking back at previous scrolling windows I've done, some of the most solid results I've obtained were using "range where" conditions.  I switched to that and the window is now rock solid.

I just cannot seem to master ranges and scrolling windows, except when using the "range where".  So "range where" it is.  You go with what works, right?

Dexterity ranges and scrolling windows

I have been doing quite a bit of Dex development and much of it centers around scrolling windows and the ranges that drive them.

I'm very thankful for David Musgrave's post in which he describes how to "future proof" your ranges.  I immediately updated all my code to comply.

Now if you create a scrolling window there's a good change you're using a range behind it to drive the content.  I'm going to explain a problem I had with this and how I solved it.

The table driving my range had a key that consisted of three filter values the user would provide on the form then click Redisplay.  That worked fine with the three filter fields as the key but I noticed that the data displayed in the window was not sorted.

The only way I know how to sort data in a scrolling window is through the range that is driving it.  And the only way I know how to control that is have the sort fields in the key used to fill the window.

(On a side note, I don't know if this is a real limitation, or just my lack of knowledge, but I have no idea how to do a reverse sort in a scrolling window.)

So here I am adding the sort fields to the key needed to present the data properly in the scrolling window.  Acting on a major dose of ignorance regarding ranges and keys I placed the sort fields at the top of the key, instead of the bottom.

The scrolling window immediately lost control and presented nonsense results, basically it stopped responding to the filter fields in the form.

After going round-about this problem for an hour-plus, I decided that maybe having the filter fields come first in the key, followed by the sort fields might help resolve the issue.

And solve it that did!  (Edit: or did it?  See my next article.)

So if you need a key on your table that includes filter fields and sort fields place your filter fields first in the key and then your sort fields.

And by all means if you find a way to reverse the sort in a Dex scrolling window PLEASE SHARE!

Found it!  Create a key with a Descending segment, and reverse the Clear/Fill options to create a Descending sort.  I also found that Dexterity will not create the index with a DESC key.  More work to be done there.

Missing script in Dexterity forms

I was creating a new form in Dexterity and I needed a form "Post Script" to clear a table before the form closed.  I wrote the script and saved it but when I went back to it there was no script present.

The other sign of this problem you'll see when you open the script again and it has the name with "_DUP" after it.  This plus your missing script is a clear indication that Dexterity lost your script.

While this happened to me on a form Post Script it could as well happen on a Pre Script.

I know this has happened to me before but I could not recall the solution.

The script is still in the form but Dexterity cannot locate it.  I don't know internally what is happening but to solve this you have to export, edit, then import the form as detailed below.

1. Stop development of the form.  Go to the Resource Explorer toolbar and Export the form to a file.
2. Create a second copy of the export file to work on, preserving the original for backup if needed.
3. Open the exported file in a text editor and locate the following lines of code.  These should be fairly high in the file.

 EventPost ""
 EventPre ""

4. Add the script name to the appropriate Event as below.

 EventPost "PO_Receiving_FORM_POST"
 EventPre ""

5. Save the modified form and import it back into Dexterity.

Open the form again in Dexterity and you should find the script present.

If you need to find the script name scroll down to the bottom of the export file and locate your code.  You should see the PRE or POST script name beside the EventScript keyword.

*UPDATE*
This just happened again in the same form with a script on my scrolling region.  I was testing and noticed the script appeared to not be firing.  When I went back into the form to look at the script it was gone and the tell-tale "_DUP" was there in the name.

To solve this I followed the above process but this time had to update the EventLineFill value in the ScrollingWindow section with the name of the SCROLL_FILL script from the EventScript section.

Also I have found that you need to recompile the dictionary after importing the form to avoid "FP:Script not found" errors.

Dynamics GP Dexterity Posting Dilemma

I developed a Use Tax add-in for Dynamics GP using Dexterity.  It adds an “Additional” window to the PM Transaction Entry window that can be used to enter use tax and not affect the payable transaction.

One of the requirements of this is to create a GL Journal Entry to recognize the use tax expense and liability.  This is a simple enough matter now that I’m using eConnect from inside Dexterity.  However another requirement is that the resulting JE be posted as part of the payable transaction posting process.  NOT a simple matter.

Two Choices?

I figure there’s two choices here:

1.       Capture the posting event from the Post button on the transaction entry windows; or

2.       Capture the creation of the PM Open transaction record from the posting.

There are two distinct problems here relative to the above options.

1.       When triggering from the Post button you have no means of knowing whether the transaction posted successfully; and

2.       When the PM Open transaction record is created the “Database Trigger” runs as part of a database transaction.  This causes the posting procedure to abort with the following error.

The Error:

“The stored procedure glpPostBatch returned the following results:  DBMS: 0, Microsoft Dynamics GP: 20821.

glpPostBatch: Error occurred this procedure can not be called in a nest transaction.”

This awkward message refers to the open transaction that (apparently) is happening when GP transfers payables data from the Work to the Open tables.

Solution?

This puts a serious monkey wrench into our two posting options and I can only see two workarounds.

1.       Have the user post the GL batches manually (potential objection by the users); or

2.       Have a background SQL process seek these GL transactions out and post them.

Anyone else work around this?

 

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!!!