Month: August 2021

I’ve recently been working with a client to implement Jet Reports as a replacement for Management Reporter. As part of this implementation, it was necessary to change the account categories in Microsoft Dynamics GP.

This particular client has over 1 million account strings in their chart of account which meant any update could not be done manually. While it could technically be done through Integration Manager this would have meant integrating a 1 million plus line file, which again wasn’t really feasible.

Instead what we did was have the client compile a list of the natural segment (the third segment) along with the new category and I created an update script to use this file to update Account Category Master (GL00102).

The below script creates a temporary table, imports the text file and then updates all the account category on all accounts in Breakdown Account Master (GL00100) based on the third segment.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
CREATE TABLE #IMPORT
	(
		ACTNUMBR_3 VARCHAR(4)
		,ACCATDSC VARCHAR(50)
	)
GO

BULK INSERT
	#IMPORT
FROM
	'C:\Temp\Categories.txt'
WITH
	(
		FIELDTERMINATOR = '\t'
		,ROWTERMINATOR = '\n'
		,FIRSTROW = 1
	)
GO

UPDATE
	['Account Index Master']
SET
	ACCATNUM = ['Account Category Master'].ACCATNUM
FROM
	GL00100 AS ['Account Index Master']
INNER JOIN
	#IMPORT
		ON
			#IMPORT.ACTNUMBR_3 = ['Account Index Master'].ACTNUMBR_3
INNER JOIN
	GL00102 AS ['Account Category Master']
		ON
			['Account Category Master'].ACCATDSC = #IMPORT.ACCATDSC
GO

DROP TABLE #IMPORT
GO

As always when running a script which does updates, make sure you have a good backup, test the script in a test company and verify the update before repeating on live.

I’ve been working on a project implementing Microsoft Dynamics GP for a new client recently. They are a manufacturing company who have bins enabled for storing and tracking items as they move through the factory; goods booked in or out through POP and SOP were also recorded to and from bins. One day during UAT (user acceptance testing) I received an email from the client that users were receiving an error that they could not create or post invoices when multiple bins were enabled:

You can't create or post invoices when the multiple bins functionality is enabled.

At this time security was still being configured so users had access to more than they would usually have had. The issue was that a user had tried, when wanting to process a sales invoice, had tried to access Invoice Entry which is part of the deprecated Invoicing module instead of Sales Transaction Entry.

We pointed the users at the correct window and got the creation of security roles prioritised a little higher so that users didn’t get confused by windows on the menu they would not be using.

Earlier this year I did a series on Implementing Post Master Enterprise from Envisage Software.

This is one of my favourite addons for Microsoft Dynamics GP which I have implemented for quite a few customers, including two in the last month. The basic functionality is that it will run as a Windows service and automatically post batches which match the autodetection rules. Envisage also make regular updates to the product both to improve functionality and to fix any issues which arise.

While upgrading some products is complicated, upgrading Post Master is nice and straightforward. All you need to do, is uninstall the previous version and install the new following the installation for a new implementation and updating the serviec to auto start and logon with the correct account.

I noticed recently that if you create a new company in Microsoft Dynamics GP and then use the Company Copy function of the PSTL (Professional Services Tools Library) you will receive an error when trying to post transactions from the Payables Management module:

The following errors were found while attempting to post
Tax detail information is incorrect.

The issue is that while PSTL will copy the tax schedules and details across, it doesn’t actually copy all of the related information across in the background. The fix is quite simple; each tax detail needs to be loaded and saved in the Tax Detail Maintenance window (Administration area page » System » Company » Tax Details).