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

Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Copy Report Option

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 first enhancement from the top requested features is the ability to copy a report option. This allows you to replicate one report option under a different name, make some changes and then save the changes, allowing you to quickly set up standard options on a range of report options.

To copy a report option, enter the new Option name and click the Copy button on the action pane:

Copy report option

Continue reading “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 – Financial Enhancements: Add Class ID to Fixed Assets Transfer

Microsoft Dynamics GPThis post is part of the Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements series where I am going hands on with the financial enhancements 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 fourth financial enhancement is the addition of Class ID to the Transfer and Mass Transfer windows; when you change the class Id, the assigned GL accounts will default from a related account group and can then be edited:

Tramsfer Maintenance

This addition should prove quite useful when transferring assets, as updating the GL accounts is often something clients want to do when performing this action.

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 – Financial Enhancements
Long description for payables transaction entries
Expand the view of fiscal periods
Show user who posted for Journal Entry Inquiry
Add Class ID to Fixed Assets Transfer
Document Attach available in Bank Reconciliation
Visual Cue for EFT Vendor on Edit Payment Batch
Show Check Number in Apply Sales Document window

Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Series Index

Microsoft Dynamics GPThis post is part of the Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements series where I am going hands on with the financial enhancements 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.

This post is the series index for the financial enhancements made to Microsoft Dynamics GP October 2019 Release

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 – Financial Enhancements
Long description for payables transaction entries
Expand the view of fiscal periods
Show user who posted for Journal Entry Inquiry
Add Class ID to Fixed Assets Transfer
Document Attach available in Bank Reconciliation
Visual Cue for EFT Vendor on Edit Payment Batch
Show Check Number in Apply Sales Document window

The series index will, if you’re heading this on azurecurve|Ramblings of a Dynamics GP Consultant, automatically update as posts go-live.

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”

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

Reformat Number into Dynamics GP Account Number

Microsoft ExcelA few weeks ago, Steve Endow retweeted about formatting a number in Microsoft Excel into a Microsoft Dynamics GP account number (I can’t find the tweet now, unfortunately). I had a fiddle around with this and it works fine for display, but the underlying data is still a number; it is just the display which has been changed to a formatted number.

However, with only a couple further steps, the account number can be correctly reformatted. I’m going to step through this one from the start; the basic premise is that we have some account strings which are not formatted; e.g. they do not have the segment separators or leading zeros:

Microsoft Excel with unformatted data

Continue reading “Reformat Number into Dynamics GP Account Number”

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