This was a blog I was planning on writing earlier this year but the topic had slipped my mind. Earlier today I was browsing the forums at GPUG and one thread reminded me of this topic so I figured today’s a good time to get this post written while it’s fresh in my mind.
I have been working with a client recently where the focus is to minimize any manual reporting in Excel and use Management Reporter more, to the extent that they are able to leverage it. They are using Dynamics GP 2013 R2 and of course, Management Reporter (MR) comes with Dynamics GP so it’s theoretically “free”. It is hard to argue against using a product that comes with your ERP solution right? Realistically now I see it as a good stepping stone to, perhaps, ultimately a more robust reporting tool in the future.
In this part of the project, the client is booking entries to “last year” (a closed year) to record opening IFRS balance sheet adjustments that they have been doing manually in Excel to produce IFRS statements. They are also now using an “elimination” company in Dynamics GP to hold entries they don’t want to really book in production, like consolidation entries between their entities, and the odd IFRS reclassification they don’t want permanently in production just for the sake of financial statement presentation.
If the client was using FRx, everything I’ve been trying to do, based on what I have done in the past, would be easy to do and it would be relatively smooth sailing. I was a loyal FRx proponent for years and as much as that product had some warts, my memories of it grow fonder with every day I work with Management Reporter so far. It’s that frustrating at times.
What I’m posting today is hopefully of use to someone out there who runs into this same problem. Admittedly it’s rare but it took me a while to find the answer.
In this case, “I” didn’t find the answer, I found it buried in a community forums post from Sivakumar, one of the Microsoft Dynamics GP MVPs.
This question came up in a forum recently – not in this exact form – but essentially the question was “where can you change the description of a transaction from a Dynamics GP Receivings Shipment or Shipment/Invoice”?
The short answer is, it’s very well hidden but it is there, and most users don’t see it and generally it stays at the default value which is “Receivings Transaction Entry”. That means if you print transaction descriptions on a Accounts Payable cheque or EFT remittance, that’s what you see.
This falls under the category of “who knew?”!
There is a long story to why I even tested this in the first place, but it's not very interesting so I will spare you the details! Suffice it to say I'm evaluating some requirements for a client who is planning to add a significant volume of transactions to Dynamics GP (potential volume in the millions of transactions per year).
In a discussion, someone asked me “how big is the Journal Entry number field?”.
Great question! I can't say I ever really looked at it, it is sufficiently large I never have needed to look.
The answer? It's an 8 digit field, and the highest allowed number is 99,999,998 (oddly enough, 99,999,999 is not allowed, go figure!).
So like any other geek out there, my mind wandered to “what happens if you get to that number?”. I thought oh my, this conceivably could be an issue some day far down the road if this volume in fact translates to individual journal entries.
What Happens Next?
In almost any other area of GP, this usually means you may be, well, snookered. We're trained to leave leading zeros in front of all sorts of “next number” fields for future growth.
Well with the Journal Entry number, after you post/save the document with JE# 99,999,998, it automatically reverts back to 1 or the lowest unused journal entry number not used in an open year.
That last “or” is an interesting one. If you test this in Fabrikam, assuming you haven't manually closed any years, you will find this out.
The JE# cycle will start again at 1, in most cases, assuming you are a normal operation and have closed your years and are not running this many transactions through in the course of an open business year.
However, with Fabrikam (and it could happen anywhere), it so happens that in my sample data JE#1 to 4 were posted but in an open year. For those that don't use Fabrikam much, it has a gazillion open fiscal years which usually people ignore (or fix) for their testing purposes.
So I closed the year 2014 and once JE#1 to 4 etc. was in history, I could use it again. Before closing the year it jumped to JE#5, and then after that to some number in the 60's, and it would keep jumping to the lowest JE# not already used in the YTD open table.
If you try to re-use a JE# in an open year, it says this JE# has already been used.
I love learning something new, no matter how trivial is may seem! I doubt I will use this morsel of information often in my consulting but you never know!
Do you have trouble finding the right G/L account to use when you enter transactions? Do you frequently use the same G/L accounts on your day to day transactions and have the key them in or look them up each time? Have you recently changed your G/L account numbering and can’t remember the new account numbers? Aliases may be useful to you if you answered yes to any of these questions!
What is it?
Aliases are a little-used field on the Account Maintenance window meant for an abbreviation or shortcut to a G/L account. During implementations they are often ignored and left blank.
How to use it
On any window where you can select a G/L account (i.e. where there is a lookup field), there is also a handy little expansion button that looks like this: . In older versions of Dynamics GP the icon was different – it looks like two small overlapping boxes if memory serves correct. The alias field is inside the expansion window (called the Account Entry window). Once you type in an alias, and tab off that field, the Account Entry window auto-closes and the G/L account it is associated with appears in your G/L account field.
- Click on Expansion Button (blue arrow) or use CTRL-Q in transaction windows (see Things to keep in mind below for a caveat)
- Type in your alias (case sensitive!)
- Tab off the field
- Voila! Your G/L account appears
Reasons to use it
- Aliases are ideal for entries that don’t have default G/L account distributions – payables transactions, receivables transactions and journal entries commonly don’t have defaults. I wouldn’t recommend creating aliases for control accounts such as Accounts Payable or Accounts Receivable since any good configuration will default these in based on the company or vendor/customer setup. However in any situation where users are looking up G/L accounts, those can be updated to include aliases.
- Aliases can speed up data entry if your account numbers are long (i.e. multiple segments).
- Aliases can also reduce data entry errors from selecting incorrect accounts if you rely on numbering instead of mnemonics.
- Aliases can increase the adoption rate of converting to a new G/L account structure by using old account numbers as part of the alias until users get to know the new account numbers.
Things to keep in mind
- Aliases are case sensitive. Before implementing this feature, poll your users to see how they would key in shortcuts. Do they keep their CAPS LOCK button on all the time? Would they want to have spaces in the alias shortcut? Consider keeping all aliases similar – all lowercase, all uppercase or in some kind of pattern so everyone knows how to key them in.
- The Alias field is 20 characters long, alpha-numeric.
- The simpler your aliases are, the more likely your users will adopt it. Don’t make the aliases too complicated or they may find it just as easy to look the accounts up manually!
- Aliases must be unique. See Tips below for how to handle multiple departments or other areas where it’s hard to create unique aliases.
- The CTRL-Q shortcut ONLY works in certain transaction windows. This shortcut does not work in windows like Account Maintenance and it also does not appear to work in ALL transaction windows. I.E. it does not work in the Miscellaneous Cheque window for some reason. However, it does work in the vast majority of common transaction entry windows!
Tips for implementing
Tip 1: Export your chart of accounts to Excel using Smartlist and review the accounts to trim it down to accounts where aliases would be useful. (Delete whatever you don’t need it for). Then create aliases in your spreadsheet and import them back into Dynamics GP using Integration Manager or your choice of integration tool.
Tip 2: Make the aliases as short as possible – the goal is speeding up data entry, not typing in 20 character phrases! Abbreviate common distinguishing features.
- Do you have accounts in multiple currencies? Consider using either full currency ISO codes or abbreviations of those for currency differentiators. Example for Prepaid Expenses in say Canadian Dollars and US Dollars, “PPD C” and “PPD U”.
- Do you have lots of departments or projects in your G/L Account structure? Perhaps use a mix of alpha and numeric – if department numbers are well-known throughout your user base, then use alpha shortcuts for common expenses and keep the numerics for departments. Example for Office Supplies “offsupp 100” for department 100, “offsup 200”, “offsup 300” etc.
Tip 3: Create cheat sheets for your staff. Don’t print a list of all 2000 G/L accounts with their alias but instead look at the patterns you used and create cheat sheets to identify the patterns. Your users are smart enough to put the patterns together to enter complete aliases! Example: one list is the common expense codes with aliases like “offsup” = Office Supplies Expense. If you have 40 office supply accounts because of different departments, projects etc., just list it once with a separate sheet listing the shortcuts for the departments or projects.
I hope this is useful information for some of you!