Month: July 2020

I created an integration recently using Integration Manager for a client to import sales invoices into Microsoft Dynamics GP. It worked fine when I was developing it, logged in as DYNSA, and for one of the finance teams managers, but produced an error for the user:

Opening source query...
Establishing source record count...
Beginning integration...
DOC 1 ERROR: Unknown Great Plains field subtype '10016'. - Field 'Deposit Received' of window 'SOP_Entry' of form 'SOP_Entry'
DOC 2 ERROR: Unknown Great Plains field subtype '10016'. - Field 'Deposit Received' of window 'SOP_Entry' of form 'SOP_Entry'
DOC 3 ERROR: Unknown Great Plains field subtype '10016'. - Field 'Deposit Received' of window 'SOP_Entry' of form 'SOP_Entry'
DOC 4 ERROR: Unknown Great Plains field subtype '10016'. - Field 'Deposit Received' of window 'SOP_Entry' of form 'SOP_Entry'
DOC 5 ERROR: Unknown Great Plains field subtype '10016'. - Field 'Deposit Received' of window 'SOP_Entry' of form 'SOP_Entry'
DOC 6 ERROR: Unknown Great Plains field subtype '10016'. - Field 'Deposit Received' of window 'SOP_Entry' of form 'SOP_Entry'
DOC 7 ERROR: Unknown Great Plains field subtype '10016'. - Field 'Deposit Received' of window 'SOP_Entry' of form 'SOP_Entry'
Integration Failed
Integration Results
    7 documents were read from the source query.
    7 documents were attempted:
        0 integrated without warnings.
        0 integrated with warnings.
        7 failed to integrate.

I toggled the Integration Manager ini file parameters so I could see what was happening with the windows to get an indication of the problem. The first window opened was Sales Order Processing Setup; why tis opened I’m not sure, but the user didn’t have permissions for this window. I did a quick test by copying the users security to a test account and added access to that window and the integration ran through without a problem.

This seems to be an operating as designed thing, but it seems odd that the SOP Setup window needs access.

I’ve been involved in a project which has included a lot of development and therefore a lot of QA and UAT over a period of time. The client’s system is configured with daily exhcnage rates which has caused some issues with testing as not everyone doing testing has access to insert new rates.

To provide some consistency in the exchange rate for testing and to ensure that testing is not held up by missing exchange rates, I created the below SQL script to copy the last exchange rate and replicate forward for every day until a specific date (set in the highlighted parameter at the top); the rate tables which are updated are listed out qithin the query (second set of highlighted text).

This script was ***NOT*** created for use on a live system, which would include a test company on the live system, as the exchange rate tables are system setup data. It was created only for use on a standalone test system with no connection to live.

/*
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 @StartDate DATETIME = FORMAT(GETDATE(), 'yyyy-MM-dd')
       ,@EndDate DATETIME = '2020-12-31'
;

WITH NewDatesToInsert AS
	(
		SELECT
			@StartDate AS EXCHDATE
		UNION ALL
			SELECT
				DATEADD(day, 1, EXCHDATE)
			FROM
				NewDatesToInsert
			WHERE
				DATEADD(day, 1, EXCHDATE) <= @EndDate
	)

INSERT INTO MC00100 -- Multicurrency Exchange Rate Maintenance (MC00100)
	(
		EXGTBLID,CURNCYID,EXCHDATE,TIME1,XCHGRATE,EXPNDATE
	)
--VALUES
	(
		SELECT
			['Multicurrency Exchange Table Dates To Insert'].EXGTBLID
			,['Multicurrency Exchange Table Dates To Insert'].CURNCYID
			,['Multicurrency Exchange Table Dates To Insert'].EXCHDATE
			,'1900-01-01 00:00:00000' AS TIME1
			,ISNULL((
				SELECT TOP 1 XCHGRATE FROM MC00100 WHERE EXGTBLID = ['Multicurrency Exchange Table Dates To Insert'].EXGTBLID ORDER BY EXCHDATE DESC
			), 1) AS XCHGRATE
			,DATEADD(day, 30, ['Multicurrency Exchange Table Dates To Insert'].EXCHDATE) AS EXPNDATE
		FROM
			(
				SELECT
					['Multicurrency Exchange Table Setup'].EXGTBLID
					,['Multicurrency Exchange Table Setup'].CURNCYID
					,['New Dates To Insert'].EXCHDATE
				FROM
					NewDatesToInsert AS ['New Dates To Insert']
				CROSS JOIN
					MC40300 AS ['Multicurrency Exchange Table Setup'] -- Multicurrency Exchange Table Setup (MC40300)
				WHERE
					['Multicurrency Exchange Table Setup'].EXGTBLID IN ('Z-UK-BUY','Z-UK-SELL','Z-UK-AVG')
			) AS ['Multicurrency Exchange Table Dates To Insert']
		INNER JOIN
			MC40200 AS [Currency Setup'] -- Currency Setup (MC40200)
				ON
					[Currency Setup'].CURNCYID = ['Multicurrency Exchange Table Dates To Insert'].CURNCYID
		WHERE
			(
				SELECT COUNT(*) FROM MC00100 WHERE EXGTBLID = ['Multicurrency Exchange Table Dates To Insert'].EXGTBLID AND EXCHDATE = ['Multicurrency Exchange Table Dates To Insert'].EXCHDATE AND XCHGRATE IS NOT NULL
			) = 0
	)
	OPTION
		(MAXRECURSION 0)

the scrpt below returns all the accounts assigned to a specific user (defined at the top) using the Account Level Security module of Microsoft Dynamics GP.

The script is configured to only return values if the Account Level Security module is enabled via the Company Setup window (Administration area page » Setup » Company » Company). To remove this restriction the where clause with the comment following it should be removed.

/*
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 @UserID AS VARCHAR(100) = 'iang'

SELCT
	['Account Index Master'].ACTNUMST
	,['Account Master'].ACTDESCR
FROM
	DYNAMICS..SY01400 AS ['User Master'] -- Users Master (SY01400)
INNER JOIN
	DYNAMICS..SY01500 AS ['Company Master'] -- Company Master (SY01500)
		ON
			['Company Master'].INTERID = DB_NAME[/sqlpink()
LEFT JOIN
	(
		SELCT
			RELID
			,ACTINDX
		FROM
			GL00100F1 -- Account Master Filter1 (GL00100F1)
		UNION ALL
			SELCT
				RELID
				,ACTINDX
			FROM
				GL00100F2 -- Account Master Filter2 (GL00100F2)
			UNION ALL
				SELCT
					RELID
					,ACTINDX
				FROM
					GL00100F3 -- Account Master Filter3 (GL00100F3)
				UNION ALL
					SELCT
						RELID
						,ACTINDX
					FROM
						GL00100F4 -- Account Master Filter4 (GL00100F4)
	) AS ['Account Level Security Accounts']
		ON
			['Account Level Security Accounts'].RELID = ['User Master'].RELID
LEFT JOIN
	GL00105 AS ['Account Index Master'] -- Account Index Master (GL00105)
		ON
			['Account Index Master'].ACTINDX = ['Account Level Security Accounts'].ACTINDX
LEFT JOIN
	GL00100 AS ['Account Master'] -- Breakdown Account Master (GL00100)
		ON
			['Account Master'].ACTINDX = ['Account Level Security Accounts'].ACTINDX
WHERE
	['Company Master'].SECOPTS = 0x01000000 -- ALS enabled in Company Setup
AND
	['User Master'].USERID = @UserID

If you wanted to use the query in a SmartList or other report, the User ID parameter could be removed and the User ID column added into the list of returned accounts.

On a recent project we encountered an issue with the test ssytem running remarkably slowly in many areas. While logging in took longer than it should have, even opening the SmartList window was taking 45 seconds; when a SmartList favourite was selected, each column header and field was drawn slowly enough that it could be read before the next one appeared.

Posting a Payables batch with 50 invoices would take more than 50 minutes (I’m not sure how long it took as that was when I disconnected my session and let it run to completion by itself at the end of the day).

We installed a client directly onto the SQL Server for testing purposes and found that the speed was what we would expect to see, rather than the snails pace the users were seeing.

One of the network team noticed that the traffic speed going over the network was very ;pw compared to what should have been possible and lower than we’d expect to see. The entire environment was a virtualised one and all machines were configured with virtual 10Gbit network cards; the solution was to remove the virtual 10Gbit ethernet cards and replace with 1Gbit.

After this change was made on both the SQL Server and terminal servers, users saw the performance of Microsoft Dynamics GP increase across the board. SmartList would open in three seconds, a favourite would return rows of data far faster than could be read and batch posting was rapid.

I’m not sure if there was a peculiarity elsewhere in the system contributing to the slowness, but the only change made at that time was the replacement of the virtual ethernet cards.

I often end up creating test, demo or upgrade requirements and have a number of scripts I run against them to make changes. The script below can be used to change the settings for the message displayed when both SOP and Invoicing are both installed.

The script is set up to leave the message enabled for Invoicing, but switch it off for Sales Order Processing (the comments include the setting for switching the other way).

/*
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
	IVC40100 -- Invoicing Setup (IVC40100)
SET
	IVCDSTCK = 0x01000040 -- set to 0x00000040 to switch off warning
GO

UPDATE
	SOP40100 -- Sales Setup (SOP40100)
SET
	IVCDSTCK = 0x00000000 -- set to 0x01000000 to switch on warning
GO

While created a new version of a customised window for a client (the old customised version was causing problems for submission to Workflow) I needed to change the tab order of the fields on the window; I’d removed quite a few fields and changed the positions of the remaining.

I couldn’t remember how to do this (I last remember doing this in Visual Studio where you can select a field and change the tab order directly) so a quick online search found me a blog from David Musgrave, originally on the old Developing for Dynamics GP blog and now on his Winthrop DC blog.

The short version is to click Layout » Set Tab Sequence and then tab through the fields, double-clicking a field to change the order:

When doing this make sure you tab through fields, even after double-clicking to change the order, or odd tab ordering will result.

David’s blog post has a lot more detail available on preparatory steps, steps to accomplish the reordering as well as cautions on what to be careful.