In our most recent webinar, we took a look at Year-End Best Practice in Microsoft Dynamics GP. In this webinar, we covered how some key points to action or consider before starting, the order in which the year end should be run before running through the closing process in each of the standard modules in Microsoft Dynamics GP covering not only what to do. but also what the year-end closing process does. If you want to catch up on this, or any other, webinar, you can do so here.
In this blog post, I am going to recap the webinar and include some links to documentation from Microsoft which will expand on some areas with more detail. The topics I'll cover are:
- Key Points
- Year-End Processing Order
- Year-End Closing steps by module
There are five key points I always stress when discussing year-end closes with clients:
- Always have a good backup before starting a year-end close and take another after the year-end close has completed - a good backup is defined as one which you know will successfully restore; unfortunately I've worked with more than one client over the years who created a maintenance plan to take backups, but then, when the need came, found that the backups were not valid and could not be restored.
Taking a backup before starting means you can easily revert back should there be a problem in the close; taking a backup after the close completes, means that if problems encountered later in the day that you can revert back to after the close has completed instead of having to repeat the close.
- Make sure all users log out of the company in which the year-end is being processed.
- Make sure there is sufficient space for database files to double in size; one significant cause of this is that the GL year-end close will copy data from Year-to-Date Transaction Open (GL20000) to Account Transaction History (GL30000) and during this process the data will exist in two places causing the database to grow.
- Perform a trial year-end close on a copy of live in test before closing the year-end on live; this will allow you to verify that there are no issues which might require a roll-back.
- Once one of the Year-End Closes has been started, be patient and allow it to run to completion. When processing, the close might cause Dynamics GP to white-screen, but this is just a sign that there is a lot of server-side processing underway and not that there is a problem.
Continue reading "Recent ISC Software Webinar: Year-End Best Practice in Microsoft Dynamics GP"
I've been keeping my out for any news of the next version of Microsoft Dynamics GP but haven't seen anything on the official blogs (if I've missed it, I'd appreciate someone pointing me in the direction), but there have been some recent articles from other partners listing some of the upcoming new functionality.
One such article is from Aisling Dynamics Consulting, LLC on the ERP Software Blog.
Since the introduction of the modern lifecycle the official name will remain Microsoft Dynamics GP and the technical name being Microsoft Dynamics GP 18.3, I will be referring to it as the Microsoft Dynamics GP October 2020 Release from here on.
I don't know if this is the complete list of new functionality for the Microsoft Dynamics GP October 2020 Release, but the new features listed by Aisling are:
- Ability to disable the system print dialog box when printing a Word Template
- Maximizing GP reports on "print to screen" rather than needing to resize the window
- A New User Defined Field is available in GL Transaction Entry
- Limiting Excel Copy/Paste decimal places to what is in Currency Setup
- Reconcile all years in the General Ledger Reconcile utility, instead of one year at a time
- Copy/Paste transactions in Payables transactions (as per GL transaction Entry)
- Two new user-defined fields on Payables Transaction Entry (adding to the long description on Payables Transactions added in the October 2019 Release)
- Added the DBA Name Field on the Vendor Card and 1099 form updates
- The 1099-NEC form! For payables Non-Employee Compensation reporting requirements starting at the end of this year, Microsoft will include the new 1099-NEC form
- Ability to mask the Social Security Number on HR reports
Included in this fix, as always, will be a number of bug fixes.
Keep an eye on the Product Lifecycle page to make sure you remain on a supporte dversions of Microsoft Dynamics GP; GP 2010 will be out of extended support as of October 2020, so any site still using this versions should be looking to upgrade as soon as possible.
This post is part of the Hands On with Microsoft Dynamics GP October 2019 Release New Features - Top Feature Requests series where I am going hands on with the top feature requests of the new version of Microsoft Dynamics GP; it is part of the larger Hands On with Microsoft Dynamics GP October 2019 Release New Features series.
The eighth, and final, new feature from the top user requests, is copy and paste from Excel in the web client. This has not worked since 2015 R2 with fixes promised always in the next version.
Again it has been included as a feature in the October 2019 release, but while it allows you to get the data from Ececl into the GL Transaction Entry window, but is instead an import. To import the journal lines in the web client, click the Paste button and then click Choose file:
You can browse to select a file to import as journal lines. The format is the same as when pasting in the desktop client.
It works, but it is not as nice and easy as the copy and paste possible in the desktop client and is not a copy and paste despite the announcement.
The Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP "October 2019" Release on which I am following and adding commentary. The index for this series of posts is here.
The ninth Feature of the Day is GL Journal Entry Inquiry Enhancements. A number of enhancements have been made to the Journal Entry Inquiry window:
- The year drop down list will now display in reverse order with the most recent year displayed first. This change has also been included in the following windows:
- Net Change Inquiry
- Detail Inquiry
- Summary Inquiry
- GL Trial Balance Report options
- The User who Posted field has been added to the window; this field will display for both open and historical journal entries.
When implementing Microsoft Dynamics GP for a new client, they usually have very similar setup between companies. While you can use the Professional Services Tools Library tool Company Copy, you sometimes need to replicate data which was configured after this function had been used.
I've previously posted scripts to copy segments and financial calendars; today's script will copy Account Categories to a new company ensuring you have the categories in both companies:
Continue reading "Copy Account Categories Between Microsoft Dynamics GP Companies"
I've been receiving an error in the Fabrikam Sample Company database when trying to create a new Financial Calendar for a while and finally had the time to track it down.
The error appears when you enter a new year and click the Calculate button:
Microsoft Dynamics GP
[Microsoft][SQL Server Native Client 11.0][SQL Server[Violation of PRIMARY KEY constraint 'PKSY40100'. Cannot insert duplicate key in object 'dbo.ST40100'. The duplicate key value is (0, 2021, 0, 2 ,General Entry ).
Continue reading "Error Creating Fiscal Calendar In Fabrikam Caused by Audit Trail Codes"
We've been doing some work with a client recently creating a number of integrations in SmartConnect. One of these was creating General Journals into a consolidation company from other Microsoft Dynamics GP databases.
Everything looked fine from a SmartConnect perspective; success reported for the integration and no warnings or errors. However, when we looked at the resulting journal, there was no Currency ID:
The problem here isn't actually a SmartConnect one, but an eConnect one which has been known for a long time. There are a number of reports of this, but the one we found when researching was on the Dynamics Blogger.
The solution is to create a chequebook (checkbook) for the Functional Currency.
The company we were importing into, didn't have one as only the GL was going to be used; once the chequebook was created, the integration worked fine.
While the General Ledger Year-End Close routine can delete unused segments, during implementation, or creation of new companies , we sometimes end up with segments created which are not needed. The below script can be used to remove all segments not assigned to an account (segments which have been used will not be removed).
The script allows the user to define which segment should be removed by changing the highlighted parameter:
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
DECLARE @SGMTNUMB AS VARCHAR(2) = 3
SGMTNUMB = @SGMTNUMB
SGMNTID NOT IN (
WHEN 1 THEN GL100.ACTNUMBR_1
WHEN 2 THEN GL100.ACTNUMBR_2
WHEN 3 THEN GL100.ACTNUMBR_3
WHEN 4 THEN GL100.ACTNUMBR_4
WHEN 5 THEN GL100.ACTNUMBR_5
WHEN 6 THEN GL100.ACTNUMBR_6
WHEN 7 THEN GL100.ACTNUMBR_7
WHEN 8 THEN GL100.ACTNUMBR_8
WHEN 9 THEN GL100.ACTNUMBR_9
WHEN 10 THEN GL100.ACTNUMBR_10
GL00105 AS GL105
GL00100 AS GL100
GL100.ACTINDX = GL105.ACTINDX
If you are using Fixed Allocation Accounts in Microsoft Dynamics GP, there is only a standard report which shows the distribution accounts against one of the accounts. These reports are not very user friendly and can;t be exported to Microsoft Excel in usable way.
Below is a SQL View which can be added to a reporting tool such as SmartList Designer, or a refreshable Excel report, which will allow users to see how Fixed Allocation accounts have been setup.
-- drop view if it exists
IF OBJECT_ID (N'uv_AZRCRV_FixedAllocationAccounts', N'V') IS NOT NULL
DROP VIEW uv_AZRCRV_FixedAllocationAccounts
-- create view
CREATE VIEW uv_AZRCRV_FixedAllocationAccounts AS
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
['Account Index Master - Fixed Allocation'].ACTNUMST AS 'Account Number'
,['Account Master - Fixed Allocation'].ACTDESCR AS 'Account Description'
,CAST(['Fixed Allocation Master'].PRCNTAGE AS NUMERIC(15,2)) AS 'Distribution Percentage'
,['Account Index Master - Fixed Allocation Distribution'].ACTNUMST AS 'Distribution Account Number'
,['Account Master - Fixed Allocation Distribution'].ACTDESCR AS 'Distribution Account Description'
,CASE WHEN ['Account Master - Fixed Allocation Distribution'].ACTIVE = 1 THEN 'Yes' ELSE 'No' END AS 'Distribution Account Active'
,['Account Category Master'].ACCATDSC AS 'Distribution Account Category Description'
,CASE WHEN ['Account Master - Fixed Allocation Distribution'].PSTNGTYP = 1 THEN 'Profit & Loss' ELSE 'Balance Sheet' END AS 'Distribution Account Posting Type'
,['Account Master - Fixed Allocation Distribution'].USERDEF1 AS 'Distribution Account User-Defined 1'
,['Account Master - Fixed Allocation Distribution'].USERDEF2 AS 'Distribution Account User-Defined 2'
,['Account Master - Fixed Allocation Distribution'].USRDEFS1 AS 'Distribution Account User-Defined 3'
,['Account Master - Fixed Allocation Distribution'].USRDEFS2 AS 'Distribution Account User-Defined 4'
GL00103 AS ['Fixed Allocation Master'] WITH (NOLOCK)
GL00105 AS ['Account Index Master - Fixed Allocation'] WITH (NOLOCK)
['Account Index Master - Fixed Allocation'].ACTINDX = ['Fixed Allocation Master'].ACTINDX
GL00100 AS ['Account Master - Fixed Allocation'] WITH (NOLOCK)
['Account Master - Fixed Allocation'].ACTINDX = ['Fixed Allocation Master'].ACTINDX
GL00105 AS ['Account Index Master - Fixed Allocation Distribution'] WITH (NOLOCK)
['Account Index Master - Fixed Allocation Distribution'].ACTINDX = ['Fixed Allocation Master'].DSTINDX
GL00100 AS ['Account Master - Fixed Allocation Distribution'] WITH (NOLOCK)
['Account Master - Fixed Allocation Distribution'].ACTINDX = ['Fixed Allocation Master'].DSTINDX
GL00102 AS ['Account Category Master'] WITH (NOLOCK)
['Account Category Master'].ACCATNUM = ['Account Master - Fixed Allocation Distribution'].ACCATNUM
-- grant permissions to view
GRANT SELECT ON uv_AZRCRV_FixedAllocationAccounts TO DYNGRP
This post is part of a series on creating a Journal - Standard integration using SmartConnect from eOne Solutions; I recently posted a series on Implementing SmartConnect.
Over the last few posts in this series, I have shown how to create a simple General Journal integration.
The process is slightly more involved than creating a similar integration, but the huge advantage is being able to schedule the integration to automatically run and select all files from a folder.
With Integration Manager, to allow multiple people to use the same integration you had to point it at a shared drive letter which all users had in common (and often not all did, due to being in different locations).
In addition, SmartConnect can also process the Excel spreadsheet directly, without requiring the user to save the active page down as a CSV; something which Excel really doesn't want you to do.
The other main benefit of SmartConnect is that if there is an eConnect node available, it can integrate that type of record into Microsoft Dynamics GP, whereas Integration Manager could only use the ones available via the standard or eConnect adapters.
eOne Solutions also have a product called Node Builder which allows additional eConnect nodes to easily be created without the need for a developer. I'll be taking a look at NodeBuilder in a future series.