Month: July 2022

When installing Microsoft Dynamics GP for clients I’ll often create an installation package to simplify the installation process for a client. However, there are often additional files which need to be deployed to a client, such as Financial Elementz, Security Informer or other additional products or ini files for Integration Manager which contain a default location.

While you can tell the client where the files are and where to copy them to, there is always the risk of them getting it wrong. It’s easier to give them a batch file to double click which will run some command and copy all of the necessary files from their current location to the correct place on the client machine.

In the below example, the first yellow highlighted text on both rows is where the files are stored and the second highlighted text is the destination folder.

/*
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).
*/
@echo off

xcopy /S /Y "\\fileserver1\Dynamics Central\Installation\Packages\Dynamics GP Additional" "C:\Program Files (x86)\Microsoft Dynamics\GP\"

timeout 5

xcopy /S /Y "\\fileserver1\Dynamics Central\Installation\Packages\Integration Manager Additional" "C:\Program Files (x86)\Microsoft Dynamics\Integration Manager 18\"

pause

I was writing a report pulling data from the Inventory Control module of Microsoft Dynamics GP the other day and I needed to include the accounts assigned to the Item Classes. I’ve written this query before and, having a need for it again, I decided to create it as a separate SQL view in order to make it more easily reusable.

/*
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).
*/
-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_ItemClassAccounts', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_ItemClassAccounts
GO
-- create view
CREATE VIEW uv_AZRCRV_ItemClassAccounts AS
SELECT
	['Item Class Setup'].ITMCLSCD AS 'Item Class Code'
	,['Item Class Setup'].ITMCLSDC AS 'Item Class Description'
	,['Account Index Master - Inventory'].ACTNUMST AS 'Inventory Account'
	, ['Account Index Master - Inventory Offset'].ACTNUMST AS 'Inventory Offset Account'
	, ['Account Index Master - Cost Of Goods Sold'].ACTNUMST AS 'Cost of Goods Sold'
	, ['Account Index Master - Discounts'].ACTNUMST AS 'Discounts'
	, ['Account Index Master - Sales'].ACTNUMST AS 'Sales'
	, ['Account Index Master - Sales Returns'].ACTNUMST AS 'Sales Returns'
	, ['Account Index Master - In Use'].ACTNUMST AS 'in Use'
	, ['Account Index Master - In Service'].ACTNUMST AS 'In Service'
	, ['Account Index Master - Damaged'].ACTNUMST AS 'Damaged'
	, ['Account Index Master - Variance'].ACTNUMST AS 'Varianves'
	, ['Account Index Master - Drop Ship'].ACTNUMST AS 'Drop Ship'
	, ['Account Index Master - Purchase Price Variance'].ACTNUMST AS 'Purchase Price Variance'
	, ['Account Index Master - Unrealised Purchase Price Variance'].ACTNUMST AS 'Unrealised Purchase Price Variance'
	, ['Account Index Master - Inventory Returns'].ACTNUMST AS 'Inventory Returns'
	, ['Account Index Master - Assembly Variance'].ACTNUMST AS 'Assembly Variances'
FROM
	IV40400 AS ['Item Class Setup'] -- Item Class Setup (IV40400)
LEFT JOIN
	GL00105 AS ['Account Index Master - Inventory'] -- Account Index Master (GL00105)
		ON
			['Account Index Master - Inventory'].ACTINDX = ['Item Class Setup'].IVIVINDX
LEFT JOIN
	GL00105 AS ['Account Index Master - Inventory Offset']
		ON
			['Account Index Master - Inventory Offset'].ACTINDX = ['Item Class Setup'].IVIVOFIX
LEFT JOIN
	GL00105 AS ['Account Index Master - Cost Of Goods Sold']
		ON
			['Account Index Master - Cost Of Goods Sold'].ACTINDX = ['Item Class Setup'].IVCOGSIX
LEFT JOIN
	GL00105 AS ['Account Index Master - Sales']
		ON
			['Account Index Master - Sales'].ACTINDX = ['Item Class Setup'].IVSLSIDX
LEFT JOIN
	GL00105 AS ['Account Index Master - Discounts']
		ON
			['Account Index Master - Discounts'].ACTINDX = ['Item Class Setup'].IVSLDSIX
LEFT JOIN
	GL00105 AS ['Account Index Master - Sales Returns']
		ON
			['Account Index Master - Sales Returns'].ACTINDX = ['Item Class Setup'].IVSLRNIX
LEFT JOIN
	GL00105 AS ['Account Index Master - In Use']
		ON
			['Account Index Master - In Use'].ACTINDX = ['Item Class Setup'].IVINUSIX
LEFT JOIN
	GL00105 AS ['Account Index Master - In Service']
		ON
			['Account Index Master - In Service'].ACTINDX = ['Item Class Setup'].IVINSVIX
LEFT JOIN
	GL00105 AS ['Account Index Master - Damaged']
		ON
			['Account Index Master - Damaged'].ACTINDX = ['Item Class Setup'].IVDMGIDX
LEFT JOIN
	GL00105 AS ['Account Index Master - Variance']
		ON
			['Account Index Master - Variance'].ACTINDX = ['Item Class Setup'].IVVARIDX
LEFT JOIN
	GL00105 AS ['Account Index Master - Drop Ship']
		ON
			['Account Index Master - Drop Ship'].ACTINDX = ['Item Class Setup'].DPSHPIDX
LEFT JOIN
	GL00105 AS ['Account Index Master - Purchase Price Variance']
		ON
			['Account Index Master - Purchase Price Variance'].ACTINDX = ['Item Class Setup'].PURPVIDX
LEFT JOIN
	GL00105 AS ['Account Index Master - Unrealised Purchase Price Variance']
		ON
			['Account Index Master - Unrealised Purchase Price Variance'].ACTINDX = ['Item Class Setup'].UPPVIDX
LEFT JOIN
	GL00105 AS ['Account Index Master - Inventory Returns']
		ON
			['Account Index Master - Inventory Returns'].ACTINDX = ['Item Class Setup'].IVRETIDX
LEFT JOIN
	GL00105 AS ['Account Index Master - Assembly Variance']
		ON
			['Account Index Master - Assembly Variance'].ACTINDX = ['Item Class Setup'].ASMVRIDX
GO

GRANT SELECT ON uv_AZRCRV_ItemClassAccounts TO DYNGRP
GO

When doing some work with a client recently, they reported an error in SmartConnect when trying to import new assets into Microsoft Dynamics GP.

This integration had been in use for a while and was known to work; it was was only being encountered in a newly created company.

The error they were receiving was very brief:

A quick search revealed a Microsoft Docs article covering this issue. As no assets have yet been created, the next number hasn’t yet been initiated so integrations can’t get the next FAINDEX. The solution is to manually create the first asset in Dynamics GP, after which the integration should run successfully.

However, the first time we tried the integration after manually creating an asset, we received another error:

The solution to this was also quite simple; on the manually created asset, manually add the Book information.

Once a single asset was created and had it’s book information added, the integration was then able to run successfully.

This post is part of the GP Elementz from ISC Software series.

One of the available modules in the GP Elementz is the Color Changer. This allows you to change the colour of certain elements of the Microsoft Dynamics GP client such as the windows background. Out-of-the-box the available colours are quite strong and I was asked by a client if we could add some softer colours.

In order to get them the colours quickly, I had a look around online for some pastel colours and found a reasonable palette which I worked out the RGB values for. Those colours are embedded in the below script which, when run against the system database, will insert them into the ELZ_COLD table for use in the Color Changer tool.

/*
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).
*/
INSERT INTO ELZ_COLD
	(
		ELZ_Definition_ID
		,DSCRIPTN
		,ELZ_Red_Paint
		,ELZ_Green_Paint
		,ELZ_Blue_Paint
		,ELZ_Colour_Value
	)
VALUES
	('Azure*','Azure','0','124','255','31999')
	,('Turquoise*','Turquoise','172','233','221','11332061')
	,('Emerald*','Emerald','181','239','206','11923406')
	,('Peter River*','Peter River','183','220','244','12049652')
	,('Amethyst*','Amethyst','221','198','231','14534375')
	,('Wet Asphalt*','Wet Asphalt','185','192','201','12173513')
	,('Green Sea*','Green Sea','173','222','212','11394772')
	,('Nephritis*','Nephritis','180','228','201','11855049')
	,('Belize Hole*','Belize Hole','181','212','233','11916521')
	,('Wisteria*','Wisteria','219','189','229','14400997')
	,('Midnight Blue*','Midnight Blue','181','189','196','11910596')
	,('Sun Flower*','Sun Flower','251','235','165','16509861')
	,('Carrot*','Carrot','246','211','175','16176047')
	,('Alizarin*','Alizarin','248','195','185','16303033')
	,('Clouds*','Clouds','250','252','252','16448764')
	,('Concrete*','Concrete','219','226','226','14410466')
	,('Orange*','Orange','253','221','170','16637354')
	,('Pumpkin*','Pumpkin','240','195','163','15778723')
	,('Pomegranate*','Pomegranate','235','186','180','15448756')
	,('Silver*','Silver','232','234','236','15264492')
	,('Asbestos*','Asbestos','211','217','217','13883865')
GO