Month: June 2021

I try to do as much through the front-end of Microsoft Dynamics GP as possible in order that the correct business logic be applied to the changes as possible. However, sometimes doing it that way takes far too much time. Recently when working with a particular client, we needed to assign all bins to all items.

Bins were being being introduced so there were no bins currently assigned, which meant a simple SQL script could be created using CROSS JOIN which produces a resultset where each item umber in the Item Master (IV00101) table was paired with the location code/bin number combination in the Site Bin Master (IV40701) table and inserted into Item Site Bin Priorities (IV00117).

/*
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 IV00117 --Item Site Bin Priorities (IV00117)
	(
		ITEMNMBR
		,LOCNCODE
		,Priority
		,BIN
		,MINSTOCKQTY
		,MAXSTOCKQTY
	)
--VALUES
	(
		SELECT
			['Item Number'].ITEMNMBR
			,['Site Bin Master'].LOCNCODE
			,ROW_NUMBER() OVER(PARTITION BY ['Item Number'].ITEMNMBR, ['Site Bin Master'].LOCNCODE ORDER BY ['Item Number'].ITEMNMBR, ['Site Bin Master'].LOCNCODE, ['Site Bin Master'].BIN) AS Priority
			,['Site Bin Master'].BIN
			,0 AS MINSTOCKQTY
			,0 AS MAXSTOCKQTY
		FROM
			IV00101 AS ['Item Number'] --Item Master (IV00101)
		CROSS JOIN
			IV40701 AS ['Site Bin Master']--Site Bin Master (IV40701)
	)
GO

If any assignments exist in the IV00117 table the script will fail with a duplicate key error; the script also assigns priority based on the alphabetical order of the site/bin number combinations which may not be suitable in all contexts.

I’ve been involved with a number of upgrade projects recently where we were both upgrading the client to the latest version of Microsoft Dynamics GP, but also introducing some new functionality such as workflow for approvals.

Quite a few of these clients are long time users of Dynamics GP who started on version 9 or before. This means that in most cases, when introducing workflow, we find that the Not Submitted and Pending Approval navigation lists don’t work. By this, I mean they do not display any data.

This issue was covered back in 2017 by Derek Albaugh on the Dynamics GP Support and Services Blog.

The blog post includes scripts to clear down the List View Master (SY07220) and List View Filters (SY07230) tables whether you have custom navigation lists or not. These tables are like the Menu Master (SY07110) table which repopulate when you log back into Dynamics GP.

For most of the clients we have found that even if there are custom navigation lists, we need to delete the contents of the two tables and recreate the customisations rather than removing only the defaults, as Dynamics GP crashes whenever we tried to access a navigation list after removing the defaults.

The below SQL function returns the last Microsoft Dynamics GP workflow final approval date and time; it is always returned, not just when final approved is the current status.

This function was created to be used in a check of approved documents to see when they had last been approved; it was used in conjunction with the SQL function which returns the current workflow status.

IF object_id(N'uf_AZRCRV_GetLastWorkflowFinalApprovalDate', N'FN') IS NOT NULL
    DROP FUNCTION uf_AZRCRV_GetLastWorkflowFinalApprovalDate
GO
CREATE FUNCTION dbo.uf_AZRCRV_GetLastWorkflowFinalApprovalDate(@WorkflowTypeName CHAR(50),@WfBusObjKey CHAR(200))
	RETURNS DATETIME
AS
/*
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).
*/
BEGIN
	RETURN ISNULL((
		SELECT TOP 1
			FORMAT(['Workflow History'].Workflow_Completion_Date, 'yyyy-MM-dd') + ' ' + FORMAT(['Workflow History'].Workflow_Completion_Time, 'HH:mm:ss.fff')
		FROM
			WF30100 AS ['Workflow History']
		INNER JOIN
			WFI10002 AS ['Workflow Master']
				ON
					['Workflow Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID
		WHERE
			['Workflow Master'].Workflow_Type_Name = @WorkflowTypeName
		AND
			['Workflow Master'].WfBusObjKey = @WfBusObjKey
		AND
			['Workflow History'].Workflow_Action = 10
		ORDER BY
			['Workflow History'].DEX_ROW_ID DESC)
	,'1900-01-01 00:00:00.000')
END
GO

GRANT EXECUTE ON uf_AZRCRV_GetLastWorkflowFinalApprovalDate TO DYNGRP
GO

For as long as I’ve been working with Microsoft Dynamics GP, I’ve been setting clients up with centralised forms and reports dictionaries in order to simplify deployment. As long as you’re not using VBA which needs to be installed onto each client, centralising the forms and reports dictionaries means you can import and customisations omly once. If each client machine has its own local forms and reports dictionaries then you need to import onto every computer.

This week when I did this, I ran into problems getting the path working correctly. I’m sure I didn’t do anything differently than normal, but it didn’t work. It took me a few minutes to get sorted out, so I am posting the below path as a reminder than you can use a UNC path for accessing the remote dictionaries, but the slashes are backslashes instead:

//FILESERVER/Dynamics Central/Dictionaries/REPORTS.DIC

As long as you have a good network, centralising the forms and reports dictionaries is my recommendation to ensure that all clients have exactly the same customisations in use. You would not centralise the application dictionaries, just the ones for forms and reports.