My blog has moved! Please visit the new blog at: https://blog.steveendow.com/
I will no longer be posting to Dynamics GP Land, and all new posts will be at https://blog.steveendow.com
Thanks!
Sometimes it helps to just get back to the basics of how things flow in GP. And a client question this week gave me an opportunity this week to do just that. To understand how purchase order processing works in Dynamics GP, a basic posting flow is helpful to illustrate where accounts come from.
It lays out the source of transactions and the corresponding default account locations. Hope you all find this useful!
Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a director with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.
The best laid plans... Word templates are not my favorite thing. There I said it. It is not because I don't think are pretty and functional and allow for more flexibility than report writer reports. It's just that they can be time consuming in a way that clients don't expect. Modifications that might be worth 1-2 hours of effort end up taking double or triple due to the quirks of word templates.
I have found that this disconnect happens most frequently with clients who already have modified report writer reports. The process to recreate a word template that looks like the report writer report can be challenging and time consuming. So in those cases, I try to use HTML for email delivery. That way clients can keep using the report writer report, and the cost to implement email delivery can be quite low.
Sometimes, though, you get stuck. HTML doesn't work well with complicated layouts, and can be unpredictable in terms of how it displays for different recipients. So then we have to use the word templates and send in PDF. This is fine, except if you have already set up customers for emailing. So now you have to change the format in Cards-Sales-Customer-Email Settings.
For that purpose, you can use the script below. Keep in mind that EmailDocumentID (in this case it is set to Sales Invoice) and EmailSeriesID (in this case it is set to Sales) would vary for different modules and documents. The EmailDocumentFormat is the field you want to update. It is the same as the dropdown (1-docx, 2-html, 3-pdf, 4-xps).
--Update sales invoice email settings for all customers to use PDF
update SY04905 set EmailDocumentFormat=3 where EMAILSeriesID=3 and EmailDocumentID=3 and MODULE1=11 and EmailDocumentEnabled=1
As always, test first and always back up your data!
Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a director with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.
Well, it turns out that it is quite a bit more configurable than I thought based on my first glance, but you wouldn't realize it unless you knew about the Secret Codes.
What are the Secret Codes, and why exactly are they secret? Good questions.
First, they are secret because they are apparently not documented yet. The D365 Financials help web pages do not currently have any reference to Payment Terms. And they are Secret Codes because unless you know what they are and how to use them, you would never know that they exist, or that you could wield their power in the Payment Terms window.
While I'm starting to get a little more familiar with the UI and navigation, there are a lot of nooks and crannies in Dynamics 365 Financials and very different names and concepts, so the training still seems very fast paced as I try and digest all of the new material.
Here are some observations from Day 3.
1. UI updates.
Menus are dead. Other than the few 'area page' links/buttons you can access from your Role Center (home page), there aren't really any menus in the application. You interact with actions on your Role Center, the Ribbon above a window, or use the Global Search feature to locate things.
Here are my observations from Day 2: 1. UI updates. The user interface is nice, but the web interface does have a few minor quirks. Sometimes the web site is very responsive, sometimes it pauses or lags when you perform an action, and other times there are multi-second delays. And the responsiveness varies by user. My UI was generally very responsive, but the instructor has had several 10-15 second delays on steps where I had no delay. The inconsistency is strange, but I'm guessing Microsoft is doing tuning on the back end.
A few additional UI observations.
When you have multiple 'windows' open, you can only see the one on top. You can click the X in the upper right to close the top window, or you can press the Esc key to close it. Today I found that you can click anywhere on the gray bar on the left to close the top window. So if you are a keyboard person, you can use Esc. If you are a mouse person, it's quicker and easier to click the gray bar than click on the X. I am finding the gray bar to be the most convenient, as you can easily click multiple times to close several windows.
UPDATE: As James Crowter notes in the tweet shown below, D365 Financials has been updated to allow entry of GL accounts on transactions, similar to the full Dynamics NAV product. As a result, some of the discussion in my post below isn't relevant anymore, but the discussion of philosophy around access to GL distributions on transactions is timeless!
In my last post on Dynamics 365 Financials training, I mentioned that in the first day, one difference I noticed was that D365 Financials does not allow access to transaction distributions during transaction entry, and that users cannot change distribution accounts directly on a transaction. Dynamics 365 Financials (NAV) uses a different design involving "Posting Groups" that control the GL account assignments for transactions.
As someone who has worked with Dynamics GP for a long time, this seemed like a big difference to me. In the GP world, Distributions are a pretty big deal. When certain transactions are entered in GP, you often have to verify the distributions. And some customers have processes that require users to change the distributions.
For example, some customers may manually code revenue accounts on a sales invoice, while other customers may code expense accounts on a vendor invoice. In the GP world, Distributions are a thing, and they're an important thing. When transactions fail to post, we sometimes ask customers to check the posting journal or Edit List to see if the distributions might have an issue. When importing transactions using eConnect, I have to make sure that the correct distributions are imported. We're so used to dealing with Distributions that it's second nature for us in the GP world.
Each day I'll try and share a few observations from the training, primarily from the perspective of Dynamics 365 Financials vs. Dynamics GP.
Overall, the training was excellent. There was a lot of content covered in four hours, but the trainer, Kerry Rosvold, was able to get through all of the content while answering dozens of questions along the way.
Here are my observations from Day 1:
1. Beautiful user interface. The Dynamics 365 user interface just looks fantastic. The look, feel and visual cues are really impressive. There are definitely elements to the UI design that aren't obvious or intuitive, and have some complexity that you have to figure out (like any complex application), but I think the design is gorgeous, and it's really amazing how much functionality they were able to put into a web client.
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.
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
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.
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.
