What’s New in the Microsoft Dynamics GP October 2020 Release

Microsoft Dynamics GPI’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:

  • System

    • 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
  • Financial

    • 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
  • Purchasing

    • 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
  • Human resources

    • 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.

Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Paste from Excel in Web Client

Microsoft Dynamics GPThis 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:

Import from Excel

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.

Hands On with Microsoft Dynamics GP October 2019 Release New Features

Hands On with Microsoft Dynamics GP October 2019 Release New Features
Hands On with Microsoft Dynamics GP October 2019 Release New Features – System Enhancements: Sort companies in User Access Setup
Hands On with Microsoft Dynamics GP October 2019 Release New Features – System Enhancements: Filter inactive users in User Access Setup
Hands On with Microsoft Dynamics GP October 2019 Release New Features – System Enhancements: More than 32 report options in Report Group
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Long description for payables transaction entries
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Expand the view of fiscal periods
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Show user who posted for Journal Entry Inquiry
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Add Class ID to Fixed Assets Transfer
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Document Attach available in Bank Reconciliation
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Visual Cue for EFT Vendor on Edit Payment Batch
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Show Check Number in Apply Sales Document window
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: User Workflow
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: User Security Workflow
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: Security Roles Workflow
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: Security Tasks Workflow
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: Export and import workflows
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: Change approver for active workflow tasks
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: Vendor approval enhancements
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Copy Report Option
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Filter Item Stock Inquiry by Date
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Exclude Inactive Items on Item Price List Report
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Minimize transaction when Go To is selected for PO that exists
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Save setting for how to show new POs
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Inactive field added to Item SmartList
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Show the User ID in the Items SmartList
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Paste from Excel in Web Client
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests
Copy Report Option
Filter Item Stock Inquiry by Date
Exclude Inactive Items on Item Price List Report
Minimize transaction when Go To is selected for PO that exists
Save setting for how to show new POs
Inactive field added to Item SmartList
Show the User ID in the Items SmartList
Paste from Excel in Web Client

MDGP October 2019 Release Feature of the Day: GL Journal Entry Inquiry Enhancements

Microsoft Dynamics GPThe 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.
Journal Entry Inquiry

Copy Account Categories Between Microsoft Dynamics GP Companies

Microsoft Dynamics GPWhen 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”

Error Creating Fiscal Calendar In Fabrikam Caused by Audit Trail Codes

Microsoft Dynamics GPI’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:

Error when calculating a new year
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”

Currency ID Missing From Batches Integrated Via eConnect

Microsoft Dynamics GPWe’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:

General Transaction Entry showing blank Currency ID field

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.

SQL Script to Delete Unused Segments

Microsoft Dynamics GPWhile 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

DELETE FROM
	GL40200
WHERE
	SGMTNUMB = @SGMTNUMB
AND
	SGMNTID NOT IN (
			SELECT
				CASE @SGMTNUMB 
				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
				END
			FROM
				GL00105 AS GL105
			INNER JOIN
				GL00100 AS GL100
					ON
						GL100.ACTINDX = GL105.ACTINDX
			)
GO

SQL View to Report on Fixed Allocation Accounts

Microsoft Dynamics GPIf 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
GO
-- 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).
*/
SELECT
	['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'
FROM
	GL00103 AS ['Fixed Allocation Master'] WITH (NOLOCK)
INNER JOIN
	GL00105 AS ['Account Index Master - Fixed Allocation'] WITH (NOLOCK)
		ON
			['Account Index Master - Fixed Allocation'].ACTINDX = ['Fixed Allocation Master'].ACTINDX
INNER JOIN
	GL00100 AS ['Account Master - Fixed Allocation'] WITH (NOLOCK)
		ON
			['Account Master - Fixed Allocation'].ACTINDX = ['Fixed Allocation Master'].ACTINDX
INNER JOIN
	GL00105 AS ['Account Index Master - Fixed Allocation Distribution'] WITH (NOLOCK)
		ON
			['Account Index Master - Fixed Allocation Distribution'].ACTINDX = ['Fixed Allocation Master'].DSTINDX
INNER JOIN
	GL00100 AS ['Account Master - Fixed Allocation Distribution'] WITH (NOLOCK)
		ON
			['Account Master - Fixed Allocation Distribution'].ACTINDX = ['Fixed Allocation Master'].DSTINDX
INNER JOIN
	GL00102 AS ['Account Category Master'] WITH (NOLOCK)
		ON
			['Account Category Master'].ACCATNUM = ['Account Master - Fixed Allocation Distribution'].ACCATNUM
GO
-- grant permissions to view
GRANT SELECT ON uv_AZRCRV_FixedAllocationAccounts TO DYNGRP
GO

Create SmartConnect Journal – Standard Map: Conclusion

eOne SolutionsThis 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.

Create SmartConnect Journal – Standard Map: Could Not Run The Scheduled Map

eOne SolutionsThis 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.

The final error I received while creating the Journal – Standard map was when I scheduled the map to run automatically:

Windows Event Viewer showing error

JOURNALSTANDARD : SmartConnect Scheduler : Could not run the scheduled map You do not have access to the connectors required for this map.

Continue reading “Create SmartConnect Journal – Standard Map: Could Not Run The Scheduled Map”