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.

MDGP 2018 R2 Feature of the Day: Mass Update Master Records from Navigation List

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2018 R2 on which I am following and adding commentary. The series index for this series of posts is here.

The twenty-second Feature of the Day is mass update of master records from navigation lists.

This feature today is the ability to mass update a group of master records from the Navigation Lists; you can now inactivate and reactive multiple records at the same time for the following master record types:

  • Accounts
  • Checkbooks
  • Customers
  • Vendors
  • Items
  • Employees

In addition, the vendor navigation list allows you to mass update the temporary status.

A visual indicator quickly lets you know the status of these records:

Vendors navigation list

Since navigation lists were introduced in Dynamics GP 10, they have grown increasingly common in usage, in particular new clients; there are some navigation lists which rarely see use, but ones for master records and transactions seem to be used a lot. There have been a few enhancements to ists over time, and I welcome even more being introduced, so it is good to see this feature.

Click to show/hide the MDGP 2018 R2 Feature of the Day Series Index

MDGP 2018 R2 Feature of the Day: Duplicate Check Numbers Option Extended

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2018 R2 on which I am following and adding commentary. The series index for this series of posts is here.

The fourteenth Feature of the Day is Duplicate Check Numbers option extended.

This feature sees the Checkbook Maintenance (Financial area page » Cards » Chechbook) option to allow or not allow Duplicate Check Numbers extended to other windows. This means that if this option is not marked, duplicate checks are not allowed in these three windows. Previously, this option only applied to Payables checkbooks but now it includes:

  • Bank Transaction Entry
  • Payroll Manual Checks
  • Miscellaneous Checks
Chequebook Maintenance

I don’t recall this ever coming up before, so I actually thought the Bank Transaction Entry and Miscellaneous Checks window already did this. That said, we don’t have that many clients using the standard Bank Rec window and none would use the Miscellaneous Checks window as virtually all payments in the UK are via EFT.

Click to show/hide the MDGP 2018 R2 Feature of the Day Series Index

Hands On With MDGP 2018 RTM New Features: Copy User Access Across AA Dimensions

Microsoft Dynamics GPMicrosoft Dynamics GP 2018 RTM has now been released and I have been taking a Hands On look at the installation and configuration of the new version.

I am now going to take a hands on look at the new and enhanced features introduced in Microsoft Dynamics GP. Rather than do this as a continuation of the Hands On series I wrote in December, as originally intended, I am going to split it out into a separate hands On With the features post. The series index is for this new features series, can be found here.

The seventeenth feature of the day is Copy User Access Across AA Dimensions.

Analytical Accounting is one of those modules which I, and a lot of other consultants, will try to guide clients away from. It is overly complex and time consuming to setup, use and, most certainly, to report from.

This new feature should, somewhat, simplify the configuration of access to alphanumeric dimensions by allowing the granted access to be copied between users.

To do this, open the User Access to Trx Dimension Codes window (Administration area page >> Setup >> Company >> Analytical Accounting >> User Access) and click the Copy button:

User Access to Trx Dimension Codes

Continue reading “Hands On With MDGP 2018 RTM New Features: Copy User Access Across AA Dimensions”

Update Year on Budgets in Fabrikam Sample Company

Microsoft Dynamics GPI’ve written a few scripts to update data in the Fabrikam sample company (I will be posting a series index to link them all together in the near future).

The sample company ships with a few budgets preloaded, but they are not named very well. For example, the budget for 2027 (the year in which Fabrikam is operating) is named BUDGET 4 and the budget for 2025 is named BUDGET 2005.

Continue reading “Update Year on Budgets in Fabrikam Sample Company”