Category: GP (Great Plains)

With the future of Microsoft Dynamics GP now being more limited than it was, with the announcement of the end of licence sales to new customers starting with perpetual licences in 2025 and subscription licences in 2026, conversation around projects to replace it with a new system are starting to intensify.

I started having these conversations with clients a few years ago while I was still working with Dynamics GP, with clients who had one eye on the future and had concerns about the longevity and product updates. Not all concerns were around Dynamics GP itself, but also the other Microsoft products surrounding it, like Windows Server, SQL Server and Office 365.

Even then we were seeing increasing issues with, especially Office, where the Office team would change something without, apparently the Dynamics GP team knowing the change was coming, so that important functionality, like the production of documents broke.

One example was Word template functionality which was broken in September 2020 and not fixed until the release of the next version in October which required the Dynamics GP to be upgraded (something which is not necessarily a small project for a company.

As time passes, unfortunately, I think that issues like this are going to continue to occur and with increasing frequency as Microsoft focus is on Dynamics 365 Business Central and Finance & Operations, rather than Dynamics GP and the level of investment in the latter will continue to reduce.

Now, this said, unlike some articles I have seen from other partners, I am not saying that it is urgent that you take immediate action. There is still a roadmap for Dynamics GP through 2028 (and contrary to some articles I’ve seen, no end of life of announcement has been made by Microsoft) and licences are still being sold to new clients (until 2026), although I’d be surprised if there are many buyers following the recent announcement.

However, the writing is on the wall in the longer term, which was what prompted my decision to switch to Microsoft Dynamics 365 Business Central in 2022 (although please note, I no longer work with the company I mention in that article; instead I now work for MADIC dynamics).

Alongside the end of licence sales, there a few other issues which I think will have an impact on companies continuing to use Dynamics GP.

Firstly, there is a reduced investment in developing new and enhanced functionality. This isn’t to say that there is none, but the improvements and additions will be relatively small and I’m sure will get smaller as time passes; instead Microsoft are making huge investments in new functionality for their other ERP offerings like Business Central.

Secondly, Dynamics GP is increasing expensive to maintain compared to cloud based ERP like Dynamics 365 Business Central; with Business Central you only need to pay your licence subscriptions for Business Central and Microsoft 365; with Dynamics GP, you need to pay your licence renewals, Windows and SQL Server licences and the electrical costs to run the servers (or hosting fees if they’re hosted in the cloud), the salaries or third party IT support costs to maintain the servers, as well as any Microsoft 365 subscription you have for email and Office and, also, in order to remain on a supported version, the consultancy fees for an annual upgrade of Dynamics GP.

Thirdly, integration with other popular Microsoft products like Office 365, Power BI (reporting and business intelligence) and Power Automate (automation of processes and integrations between systems), may be available with Dynamics GP. but only with an increasing umber of hoops to be jumped through; cloud ERP like Business Central are designed to work seamlessly with these other Microsoft products and sees improvements in every release wave.

Fourthly, on-premises ERP like Microsoft can be quite difficult, or at least technically involved, for remote workers to access; cloud ERPs like Business Central are available in any browser on any device. Some also have dedicated apps for Android and iOS.

Fifthly, products like Copilots are only being made available with the cloud ERP. These AI driven tools open the door for intelligent suggestions and predictive analytics which can improve operational efficiency.

Sixthly, cloud ERP like Business Central typically receive updates more frequently (Business Central has two release waves each year) which can be scheduled for automatic deployment; testing is available through easily created sandbox copies of production environments prior to being implemented on production. this reduces the effort required for upgrades which Dynamics GP can be quite involved and require a substantial amount of time from a consultant.

I worked with Microsoft Dynamics GP for a long time, 19 years in fact, before I decided to make the switch to Microsoft Dynamics 365 Business Central.

The future of Dynamics GP has been a hot topic since 2008 and the ill-fated Project Green which was a project to look at the feasibility of merging the various Microsoft ERP into one product; despite this project completing fairly quickly with the conclusion that it wasn’t feasible, Project Green kept being mentioned for years afterwards by competitors as meaning the end of Dynamics GP.

Rumours have kept cropping up over the years, sometimes with no basis (such as in 2018 by a Dynamics NAV MVP who said GP had been put into maintenance mode), and sometimes by the words of senior people within Microsoft.

One of the most recent of these were from Mike Morton who, at the Summit NA 2022 conference in October said “There will be no more new releases, and no more new features, for Dynamics GP”. Well, as I’m sure you can imagine, this created quite a ruckus from the partners at the conference, leading to an emergency session between them and Microsoft and leading to a statement from Morton the next day to “clarify” his meaning.

Continue reading “The Future of Microsoft Dynamics GP”

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script sets the On Hold flag for a vendor as the record is created.

/*
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 TRIGGER 
	dbo.utr_AZRCRV_NewCreditorInactivate
ON 
	dbo.PM00200 --PM Vendor Master File (PM00200)
FOR INSERT
AS
UPDATE
	PM00200
SET 
	HOLD = 1
WHERE 
	VENDORID = (SELECT VENDORID FROM INSERTED)
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script changes the On Hold flag on a vendor record if EFT details are added, updated or deleted.

/*
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 TRIGGER 
	dbo.utr_AZRCRV_HoldonUpdateEFTDetails
ON 
	dbo.SY06000 --Address Electronic Funds Transfer Master (SY06000)
FOR INSERT,UPDATE,DELETE
AS
UPDATE
	PM00200
SET 
	HOLD = 1
WHERE 
	PM00200.VENDORID = (SELECT VENDORID FROM DELETED)
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script will activate horizontal scroll bars for all existing users

/*
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).
*/
UPDATE
		['Users Master']
	Set
		HSCRLARW = 1
	FROM
		SY01400 AS ['Users Master'] --Users Master (SY01400)
	WHERE
		HSCRLARW = 0

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script creates a trigger which activates horizontal scroll bars for new users as they are created.

/*
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 TRIGGER
	utr_AZRCRV_UpdateSY01400ActivateHorizontalScrollArrows
ON
	SY01400 --Users Master (SY01400)
AFTER INSERT AS
	UPDATE
		['Users Master']
	Set
		HSCRLARW = 1
	FROM
		SY01400 AS ['Users Master']
	INNER JOIN
		inserted AS INS
			ON
				INS.USERID = ['Users Master'].USERID
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was written for a client to extract Payables transactions from all companies tnto a temporary table; the script is run from the system database (typically called DYNAMICS) and selects all linked company databases.

The start and end years can be specified by the user in the highlighted variables.

/*
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 #SupplierExtract(
	INTERID CHAR(50)
	,Company_Name CHAR(100)
	,Vendor_ID CHAR(15)
	,Vendor_Name CHAR(65)
	,Document_Type CHAR(20)
	,Document_Date datetime
	,Voucher_Number CHAR(21)
	,Document_Number CHAR(21)
	,Document_Amount NUMERIC(19,5)
	,Document_Net NUMERIC(19,5)
	,Document_Tax NUMERIC(19,5)
	,Payment_Voucher_Number CHAR(21)
	,Payment_Doc_Number CHAR(21)
	,Payment_Date datetime
	,Apply_Date datetime
	,Apply_Amount NUMERIC(19,5)
	,Payment_Type CHAR(20)
)
GO

DECLARE @StartYear CHAR(4) = 2017
DECLARE @EndYear CHAR(4) = 2024

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
					SELECT
					CHAR(13)
					+'select
						'''+INTERID+'''						AS Database_Name,
						'''+CMPNYNAM+'''					AS Division,
						P.VENDORID							AS Vendor_ID,
						PM.VENDNAME							AS Vendor_Name,
						case P.DOCTYPE
							when 1 then ''Invoice''
							when 2 then ''Finance Charge''
							when 3 then ''Misc Charge''
							when 4 then ''Return''
							when 5 then ''Credit Memo''
							when 6 then ''Payment''
						else ''''
						end									AS Document_Type,
						P.DOCDATE							AS Document_Date,
						P.VCHRNMBR							AS Voucher_Number,
						P.DOCNUMBR							AS Document_Number,
						P.DOCAMNT							AS Document_Amount,
						P.PRCHAMNT							AS Document_Net, 
						P.TAXAMNT							AS Document_Tax,
						coalesce(PA.VCHRNMBR,'''')			AS Payment_Voucher_Number,
						coalesce(P2.DOCNUMBR,'''')			AS Payment_Doc_Number,
						coalesce(P2.DOCDATE,''1/1/1900'')	AS Payment_Date,
						coalesce(PA.DATE1,''1/1/1900'')		AS Apply_Date,
						PA.APPLDAMT							AS Apply_Amount,
						case PA.DOCTYPE
							when 1 then ''Invoice''
							when 2 then ''Finance Charge''
							when 3 then ''Misc Charge''
							when 4 then ''Return''
							when 5 then ''Credit Memo''
							when 6 then ''Payment''
						else ''''
						end									AS Payment_Type  
					from
					 (select 
						VENDORID, 
						DOCTYPE, 
						DOCDATE, 
						VCHRNMBR, 
						DOCNUMBR,
						DOCAMNT, 
						PRCHAMNT, 
						TAXAMNT, 
						VOIDED 
					  from '+INTERID+'.dbo.PM30200 with (NoLock)

					 union all

					  select 
						VENDORID, 
						DOCTYPE, 
						DOCDATE, 
						VCHRNMBR, 
						DOCNUMBR,
						DOCAMNT, 
						PRCHAMNT, 
						TAXAMNT, 
						VOIDED
					  from '+INTERID+'.dbo.PM20000 with (NoLock)) P
  
					left outer join
					 (select	
						VENDORID, 
						APTVCHNM, 
						APTODCTY, 
						APFRDCNM, 
						DOCTYPE,
						DOCDATE, 
						APPLDAMT, 
						VCHRNMBR, 
						DATE1, 
						GLPOSTDT,
						case POSTED when 0 then ''Unposted'' else ''Posted'' end POSTED
					from '+INTERID+'.dbo.PM10200 with (NoLock)

					UNION

					select	
						VENDORID, 
						APTVCHNM, 
						APTODCTY, 
						APFRDCNM, 
						DOCTYPE,
						DOCDATE, 
						APPLDAMT, 
						VCHRNMBR, 
						DATE1, 
						GLPOSTDT,
						''Posted'' AS POSTED
					from '+INTERID+'.dbo.PM30300 with (NoLock)

					UNION

					select	
						VENDORID, 
						VCHRNMBR AS APTVCHNM, 
						DOCTYPE AS APTODCTY,
						APTODCNM AS APFRDCNM, 
						APTODCTY AS DOCTYPE, 	
						APTODCDT AS DOCDATE, 
						APPLDAMT, 
						APTVCHNM AS VCHRNMBR,
						DATE1, 
						GLPOSTDT,
					case POSTED when 0 then ''Unposted'' else ''Posted'' end POSTED
					from '+INTERID+'.dbo.PM10200 with (NoLock)

					union

					select	
						VENDORID, 
						VCHRNMBR AS APTVCHNM, 
						DOCTYPE AS APTODCTY,
						APTODCNM AS APFRDCNM, 
						APTODCTY AS DOCTYPE, 	
						APTODCDT AS DOCDATE, 
						APPLDAMT, 
						APTVCHNM AS VCHRNMBR,
						DATE1, 
						GLPOSTDT,
						''Posted'' AS POSTED
					from '+INTERID+'.dbo.PM30300 with (NoLock)) PA
						on P.VCHRNMBR = PA.APTVCHNM and P.VENDORID = PA.VENDORID
						and P.DOCTYPE = PA.APTODCTY
  
					left outer join
					 (select 
						VCHRNMBR, 
						DOCTYPE, 
						DOCNUMBR, 
						DOCDATE
					  from '+INTERID+'.dbo.PM20000 with (NoLock)

					 union all

					  select
					   VCHRNMBR, 
					   DOCTYPE, 
					   DOCNUMBR, 
					   DOCDATE
					  from '+INTERID+'.dbo.PM30200 with (NoLock)) P2
						on P2.VCHRNMBR = PA.VCHRNMBR and P2.DOCTYPE = PA.DOCTYPE
  
					left outer join '+INTERID+'.dbo.PM00200 PM with (NoLock) 
						on P.VENDORID = PM.VENDORID
  
					where P.DOCTYPE in (1,2,3,4,5,6) and P.VOIDED = 0 AND (P.DOCDATE BETWEEN '''+@StartYear+'/04/01'' AND '''+@EndYear+'/03/31'')' 
					FROM
						SY01500 with (NoLock)
					WHERE CMPNYNAM NOT LIKE '%Test%'
					FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

INSERT INTO #SupplierExtract (
						InterID,
						Company_Name,
						Vendor_ID,
						Vendor_Name,
						Document_Type,
						Document_Date,
						Voucher_Number,
						Document_Number,
						Document_Amount,
						Document_Net, 
						Document_Tax,
						Payment_Voucher_Number,
						Payment_Doc_Number,
						Payment_Date,
						Apply_Date,
						Apply_Amount,
						Payment_Type)	
EXEC sys.sp_executesql @SQL
GO

SELECT
	* 
FROM
	#SupplierExtract
GO

DROP TABLE #SupplierExtract
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script creates a view on payables transactions which was created for a client who was migrating from Dynamics GP to another ERP system.

/*
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 VIEW uv_AZRCRV_SupplierTransactionExtract AS
SELECT
                        DB_NAME()							AS Database_Name,
                        SY.CMPNYNAM							AS Division,
						P.VENDORID							AS Vendor_ID,
						PM.VENDNAME							AS Vendor_Name,
						case P.DOCTYPE
							when 1 then 'Invoice'
							when 2 then 'Finance Charge'
							when 3 then 'Misc Charge'
							when 4 then 'Return'
							when 5 then 'Credit Memo'
							when 6 then 'Payment'
						else ''
						end									AS Document_Type,
						P.DOCDATE							AS Document_Date,
						P.VCHRNMBR							AS Voucher_Number,
						P.DOCNUMBR							AS Document_Number,
						P.DOCAMNT							AS Document_Amount,
						P.PRCHAMNT							AS Document_Net, 
						P.TAXAMNT							AS Document_Tax,
						coalesce(PA.VCHRNMBR,'')			AS Payment_Voucher_Number,
						coalesce(P2.DOCNUMBR,'')			AS Payment_Doc_Number,
						coalesce(P2.DOCDATE,'1/1/1900')		AS Payment_Date,
						coalesce(PA.DATE1,'1/1/1900')		AS Apply_Date,
						PA.APPLDAMT							AS Apply_Amount,
						case PA.DOCTYPE
							when 1 then 'Invoice'
							when 2 then 'Finance Charge'
							when 3 then 'Misc Charge'
							when 4 then 'Return'
							when 5 then 'Credit Memo'
							when 6 then 'Payment'
						else ''
						end									AS Payment_Type  
					from
					 (select 
						VENDORID, 
						DOCTYPE, 
						DOCDATE, 
						VCHRNMBR, 
						DOCNUMBR,
						DOCAMNT, 
						PRCHAMNT, 
						TAXAMNT, 
						VOIDED 
					  from dbo.PM30200

					 union all

					  select 
						VENDORID, 
						DOCTYPE, 
						DOCDATE, 
						VCHRNMBR, 
						DOCNUMBR,
						DOCAMNT, 
						PRCHAMNT, 
						TAXAMNT, 
						VOIDED
					  from dbo.PM20000) P
  
					left outer join
					 (select	
						VENDORID, 
						APTVCHNM, 
						APTODCTY, 
						APFRDCNM, 
						DOCTYPE,
						DOCDATE, 
						APPLDAMT, 
						VCHRNMBR, 
						DATE1, 
						GLPOSTDT,
						case POSTED when 0 then 'Unposted' else 'Posted' end POSTED
					from dbo.PM10200

					UNION

					select	
						VENDORID, 
						APTVCHNM, 
						APTODCTY, 
						APFRDCNM, 
						DOCTYPE,
						DOCDATE, 
						APPLDAMT, 
						VCHRNMBR, 
						DATE1, 
						GLPOSTDT,
						'Posted' AS POSTED
					from dbo.PM30300 

					UNION

					select	
						VENDORID, 
						VCHRNMBR AS APTVCHNM, 
						DOCTYPE AS APTODCTY,
						APTODCNM AS APFRDCNM, 
						APTODCTY AS DOCTYPE, 	
						APTODCDT AS DOCDATE, 
						APPLDAMT, 
						APTVCHNM AS VCHRNMBR,
						DATE1, 
						GLPOSTDT,
					case POSTED when 0 then 'Unposted' else 'Posted' end POSTED
					from dbo.PM10200

					union

					select	
						VENDORID, 
						VCHRNMBR AS APTVCHNM, 
						DOCTYPE AS APTODCTY,
						APTODCNM AS APFRDCNM, 
						APTODCTY AS DOCTYPE, 	
						APTODCDT AS DOCDATE, 
						APPLDAMT, 
						APTVCHNM AS VCHRNMBR,
						DATE1, 
						GLPOSTDT,
						'Posted' AS POSTED
					from dbo.PM30300) PA
						on P.VCHRNMBR = PA.APTVCHNM and P.VENDORID = PA.VENDORID
						and P.DOCTYPE = PA.APTODCTY
  
					left outer join
					 (select 
						VCHRNMBR, 
						DOCTYPE, 
						DOCNUMBR, 
						DOCDATE
					  from dbo.PM20000

					 union all

					  select
					   VCHRNMBR, 
					   DOCTYPE, 
					   DOCNUMBR, 
					   DOCDATE
					  from dbo.PM30200) P2
						on P2.VCHRNMBR = PA.VCHRNMBR and P2.DOCTYPE = PA.DOCTYPE
  
					left outer join dbo.PM00200 PM
						on P.VENDORID = PM.VENDORID
						
					INNER JOIN
						D20..SY01500 AS SY WITH (NOLOCK)
							ON INTERID = DB_NAME()
  
					where P.DOCTYPE in (1,2,3,4,5,6) and P.VOIDED = 0 AND (P.VENDORID NOT LIKE '5%' AND P.VENDORID NOT LIKE '3%')

GO					
GRANT SELECT ON uv_AZRCRV_SupplierTransactionExtract TO DYNGRP
GO

SELECT * FROM uv_AZRCRV_SupplierTransactionExtract
GO