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 (http://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 (http://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

Delete Corrupt Fixed Allocation Master (GL00103) Data

Microsoft Dynamics GPI was recently putting together a report for a client on Fixed Allocation Accounts and came across some corrupt data in the Fabrikam, Sample Company, Database. To avoid encountering this issue again, next time I am working in this area, I put together a simple script to remove the corrupt records by joining Fixed Allocation Master (GL00103) with Breakdown Account Master (GL00100) to identify the data which should not be there and delete it; this makes the script generic enough that I can use it on a live company should the need ever arise.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://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).
*/
DELETE FROM
	['Fixed Allocation Master']
FROM
	GL00103 AS ['Fixed Allocation Master']
INNER JOIN
	GL00100 ['Account Master']
		ON
			['Account Master'].ACTINDX = ['Fixed Allocation Master'].ACTINDX
		AND
			['Account Master'].FXDORVAR = 2

As always, before you run any script, ensure you have a good backup of your database and verify the results after the script has been run.