Friday, March 24, 2017

Musical Tribute: I Want My MVP

By Steve Endow

My colleague Andrew Dean and I have come up with a few wild ideas, mostly related to Dynamics GP development and customization.  The other day, he came up with a really, really wild idea.

I mentioned that MVP submissions were due at the end of March, so I had to finish up a few things in the next few weeks.

He said, "Why don't you make a video to the song Money for Nothing, but make it 'I Want My MVP'?"

That was one wild idea.  After I got over the surprise, I didn't know where to begin.  How does one make a music video, and how would I create the song?

With a little ingenuity, a friend who has a rock band, and some low budget guerrilla film making with my daughter, we pulled it together in 3 days.

Enjoy!




Lyrics

I want my MVP

I want my MVP

Now look at them users
That’s the way you do it
Getting help from those MVPs
That ain’t workin’ that’s the way you do it
Knowledge for nothin’ and your help for free

Now that ain’t workin’ that’s the way you do it
Lemme tell ya them users ain’t dumb
Maybe get an answer from the GP forums
Maybe get an answer from a GP blog

We gotta install all the latest patches
ERP software deliveries
We gotta move these big databases
We gotta write these SQL queries

See the busy worker with Excel and tons of numbers
Yeah buddy, no need to despair
That busy worker calls on the MVPs
That busy worker, he don’t need to care

We gotta install all the latest patches
ERP software deliveries
We gotta move these big databases
We gotta write these SQL queries

I shoulda learned to post a couple o’ questions
I shoulda learned to read some blogs
Look at that accountant, she’s got all the answers
Man accounting is so much fun
And he’s up there, what’s that? Reconciliations?
Bangin’ out reports like SQL wizardry
That ain’t workin that’s the way you do it
Get your knowledge for nothin’ get your help for free

We gotta install all the latest patches
ERP software deliveries
We gotta move these big databases
We gotta write these SQL queries

Now that ain’t workin’ that’s the way you do it
You get your help from an MVP
Now that ain’t workin’ that’s the way you do it
Knowledge for nothin’ and your help for free
Knowledge for nothin’ and help for free


Saturday, March 18, 2017

SQL Trivia: Leading spaces on string field

By Steve Endow

I think I've been writing SQL statements since 1996.  Maybe I encountered this scenario many years ago, but completely forgot about it.  But I am pretty sure that since I have been working with GP over the last 13 years, I can't remember ever encountering it.

During the SmartConnect training class that I attended this week, the trainer, Mark Anderson, pointed out a situation where an extra space could accidentally make its way into a SQL statement in a SmartConnect map.  He explained that the leading space in the WHERE clause value would cause the query to return zero records.

Somehow, I don't think I've run into this problem--I just haven't made that particular typo.

Here's an example of what that might look like.  The first query has no leading space in the WHERE clause, but does have trailing spaces, and it retrieves one customer record.  But the second query adds a few spaces in front of the customer ID value in the WHERE clause, and that query returns zero records.


Most Dynamics GP application fields do not seem to allow leading spaces--the only exception I could find is the Note field/window.  Given this, it is unlikely that you would have a situation where Dynamics GP data would have a leading space and cause this scenario.

However, if you have integrations or reporting with custom queries, or queries that are concatenated through script or code, it's possible for a space to creep in.  In SmartConnect, this can occur when you are inserting a pre-defined data field name into a SQL statement, like this:

SELECT * FROM RM00101 WHERE CUSTNMBR = ' _CUSTOMERID'

When you drag the _CUSTOMERID source data field name into your SQL script, it can result in an extra space before the field name, causing the SQL statement to return zero records.

Since I can't remember having ever encountered this scenario, it isn't something I would think to look for.  It makes sense, but it wouldn't have been obvious to me.  I've become almost indifferent to trailing spaces, because they have no consequence with a typical GP SQL install, but leading spaces are definitely a gotcha.

I thought it was interesting, and a neat reminder about the mechanics of SQL Server.


You can also find him on Twitter, YouTube, and Google+




Tuesday, March 7, 2017

SQL Script to verify Dynamics GP GL batch control totals

By Steve Endow

I have a customer who is importing GL batches into Dynamics GP, and they explained that they a bug in their integration that is causing the batch control totals to be incorrect. So they may import 50 transactions into GP, but the batch control total will show 85 transactions.  I believe that this discrepancy is causing some issues.

So I quickly threw together this SQL query to check the number of GL transactions and JE totals and compare those numbers to the batch control totals in SY00500.

There are probably several different ways to accomplish this, but I happened to pull this version together in a few minutes using a CTE.


WITH glTotals (BACHNUMB, BatchCount, BatchTotal)
AS (
SELECT glh.BACHNUMB, COUNT(glh.JRNENTRY) AS BatchCount, SUM(dtGLTotals.JEAmount) AS BatchTotal FROM GL10000 glh
JOIN (SELECT JRNENTRY, SUM(DEBITAMT + CRDTAMNT) AS JEAmount FROM GL10001 GROUP BY JRNENTRY) AS dtGLTotals ON dtGLTotals.JRNENTRY = glh.JRNENTRY
GROUP BY glh.BACHNUMB
)

SELECT b.BACHNUMB, b.NUMOFTRX, b.BCHTOTAL, glTotals.BatchCount, glTotals.BatchTotal, 
CASE WHEN b.NUMOFTRX <> glTotals.BatchCount THEN 'Count Diff' ELSE '' END AS CountDiff, 
CASE WHEN b.BCHTOTAL <> glTotals.BatchTotal THEN 'Amount Diff' ELSE '' END AS AmountDiff 
FROM TWO..SY00500 b 
JOIN glTotals ON glTotals.BACHNUMB = b.BACHNUMB
WHERE b.BCHSOURC = 'GL_Normal' AND b.BACHNUMB LIKE 'TEST%'


Here's what the results look like:


If there is a discrepancy in the transaction count or the total amount, it will display a note in the CountDiff or the AmountDiff columns.

Next, I will likely need to write a SQL script that will correct the control totals in SY00500.



You can also find him on Twitter, YouTube, and Google+