Year: 2022

I did some work for a client recently where we created a new check remittance layout and showed them how to enable emails in Microsoft Dynamics GP. When we deployed ive however, we encountered a problem whereby they client could not enable, en masse, the creditors for emails as the button was missing fromthe action pane in the Creditors navigation list.

On the standalone test system the E-mail Settings option on the Modify section of the navigation pane was present:

But on live, this option is missing:

While comparing the two systems, I realised that the Vendors Not Submitted and Vendors Pending Approval navigation ist favourites were missing from live, but present on test (just like hte E-mail Settings option.

We had two small projects running in parallel; one for the remittance and emails and the other for workflow approvals. We had identified the navigation list favourites had been missing from the test system and had planned fixing them when workflow was deployed to live, but the remittance project was signed off first.

The navigation list favourites is a known issue after upgrade, but hadn’t been fixed as the client had not been using the navigation lists and so hadn’t noticed they were missing. We fixed the navigation ists on live and were then abe to use the email settings option to enable email on creditors en masse.

I’m not sure how, but I stumbled across an article on the ERP Software Blog by Michael Krasivsky of The Resource Group where he has posted a more efficient version of the Grant.sql script.

For those who don’t know, the Grant script is used to apply the correct permissions to SQL objects (tables, views and stored procedures) used by Microsoft Dynamics GP; this script can take a while to run as it utilises a cursor to update the permissions on every single SQL objects in the database regardless of the current permissions.

What Michael has done is create a script which only selects the SQL objects which do not have the correct permissions and grant them on those objects only; this makes the script much faster to run, as in most cases the majority of the SQL objects will already have the correct permissions so it is only a minority of objects which need to be updated.

I recently needed to find which databases in SQL Server had a specific view deployed to them. I’ve created scripts in the past to find SQL objects in all databases (table,trigger and functions), but not one for SQL views.

Some of the previous scripts looked in all databases and others were limited to only the Microsoft Dynamics GP databases; this script is one of the latter, using the SY015000 table to only search for a SQL view in the Dynamics GP databases:

/*
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).
*/
DECLARE @command NVARCHAR(MAX)

DECLARE @SystemDatabase VARCHAR(15) = 'D20'
DECLARE @View VARCHAR(50) = 'uv_AZRCRV'
CREATE TABLE #ReturnedData(
	DBNAME VARCHAR(15)
	,VIEWNAME VARCHAR(100)
)

SELECT @command = 'IF EXISTS (SELECT 1 FROM sys.databases AS dbs LEFT JOIN ' + @SystemDatabase + '..SY01500 SY ON SY.INTERID = dbs.name WHERE dbs.name = ''?'' AND (dbs.name = ''' + @SystemDatabase + ''' OR SY.INTERID IS NOT NULL))
						BEGIN
							USE [?];
							INSERT INTO #ReturnedData (dbname, VIEWNAME) (SELECT DB_NAME() AS ''DB_NAME'', o.name FROM sys.objects AS o WHERE o.type = ''V'' AND o.name LIKE ''' + @View + '%'')
						END'

EXEC sp_MSforeachdb @command

SELECT * FROM #ReturnedData

DROP TABLE #ReturnedData

After assisting a client with a server migration, an issue was reported with a fixed asset integration:

The first thing we checked was to see if the stored procedure did exist, which it did. The next option was a suspicion of permissions. After checking in SQL I found that the service account did not have access to all of the company databases; we added the DYNGRP database role to the service account and tried the integration again and found that it now ran successfully.

I was doing some work with a client recently where we needed to output two different formats of EFT file for the same bank account. This is possible using the EFT Transfer Method on the bank on the creditors. However, with a large number of creditors we needed a way to update the records in bulk. While it would be possible to do this with a macro, it would be fiddly and take a while.

Instead, I created a SQL script which can be run to update the Address Electronic Funds Transfer Master (SY06000) table:

/*
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
	['Address Electronic Funds Transfer Master']
SET
	EFTTransferMethod = 2
		/*
			1=Not Specified
			2=Business Account
			3=Corporate Account
			4=Personal Account
			5=Foreign Account
		*/
FROM
	SY06000 AS ['Address Electronic Funds Transfer Master']
WHERE
	SERIES = 4
AND
	EFTBankType = 3 
		/*
			2 = Ireland
			3 = United Kingdom
			26 = Other 1
			27 = Other 2
			30 = Canada
			31 = United States
		*/
GO

The script is currently set up to update only UK banks and to set the EFT Transfer Method to Business Account. The green comments show some of the other values which you can use.

If you use the script, make sure you have a good backup before.

Working on a recent project for a client we needed to extract the chart of accounts from one Microsoft Dynamics GP company and import it into a couple of others (altering the first segment during the process. The below script was created to extract all of the chart of account data in the format required for import through Integration Manager:

SELECT
	['Account Index Master'].ACTNUMST AS 'Account Number'
	,['Account Master'].ACTDESCR AS 'Account Description'
	,CASE WHEN ['Account Master'].ACTIVE = 1 THEN 'Active' ELSE 'Inactive' END AS 'Active'
	,['Account Master'].ACCTENTR AS 'Allow Account Entry'
	,['Account Master'].ACTALIAS AS 'Account Alias'
	,['Account Category Master'].ACCATDSC AS 'Account Category Description'
	,CASE WHEN ['Account Master'].PSTNGTYP = 0 THEN 'Balance Sheet' ELSE 'Profit and Loss' END AS 'Posting Type'
	,CASE WHEN ['Account Master'].TPCLBLNC = 0 THEN 'Debit' ELSE 'Credit' END AS 'Typical Balance'
	,['Account Master'].USERDEF1 AS 'User Defined 1'
	,['Account Master'].USERDEF2 AS 'User Defined 2'
	,['Account Master'].USRDEFS1 AS 'User Defined 3'
	,['Account Master'].USRDEFS2 AS 'User Defined 4'
FROM
	GL00100 AS ['Account Master']
INNER JOIN
	GL00105 AS ['Account Index Master']
		ON
			['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX
INNER JOIN
	GL00102 AS ['Account Category Master']
		ON
			['Account Category Master'].ACCATNUM = ['Account Master'].ACCATNUM[