Category: GP (Great Plains)

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.

Integration Manager installs with a sample database in the install folders as the default database. In virtually every case, the clients I work with have a centralised database shared by all clients. While users can amend the path through the Integration Manager application, you can also add a setting in the Microsoft.Dynamics.GP.IntegrationManager.ini file (by default located in C:\Program Files (x86)\Microsoft Dynamics\Integration Manager 18) to change the location for all users.

Under the [IMBaseProvider] heading add the following line where the highlighted section is the path to the database:

DBPath=\\fileserver\Integrations\IM.mdb

I’m working on a few projects for a client at the moment and became aware that logging into their main company was taking almost a minute. Most of the work had been in subsidiaries at the start of the projects, but we’ve moved into the main company now. The subsidiaries are quite quick to log into, say 10 seconds, but the main company was over a minute.

It didn’t matter if this was logging into Dynamics GP itself or just switching between companies. Everything else was running normally (entering or posting transactions and running reports), but as I investigated I found that the problem was specifically related to the loading of the Dynamics GP home page and it was only affecting out user account (I confirmed with the client that they were not seeing speed problems when loading the home page).

The problem turned out to be very prosaic; there were a number of SmartList reminders setup for sales and inventory which were all running before the home page displayed. The main company is where most of the inventory is processed so these reminders are checking large numbers of items and related transactions; removing these favourites from our user restored the quickness of the home age in the main company.

Being able to use SmartList favourites as reminders is good, but if there are a few of them on large datasets, then they can have an impact on performance. As it was just our user account, I was able to remove the reminders without impact; I think they had been added when showing users how to add reminders. If this speed was affecting a user, I would look to see if the reminders could be removed and, if not, create some SmartList Builder (if they have it, otherwise SmartList Designer) which only select the relevant subset of data (such as by selecting only from the work table and not the history table).

I’ve been using, off and on, the eConnect developers documentation for a number of years. However, it seems that Microsoft have now closed down the old location and moved the documents to the Microsoft Docs website.

The new location is here.

I’m posting this here as it took me a long time to actually find the new location.

I’ve been working with a client a lot recently, so when one of their users was not able to log into Microsoft Dynamics GP today, they got in touch.

The problem only affected one user who got the below error message; all other users were unaffected and could log in normally:

This sort of issue is covered in a Microsoft KB article.

The first three possibilities listed, could be ruled out as they would have affected more than just one user; the final option, records stuck in either the SACTIVITY or Batch Activity (SY00800) tables.

I used the below script to check for corrupt records (the highlighted section is the user id to check for):

SELECT * FROM ACTIVITY WHERE userid = 'userid'
SELECT * FROM SY00800 WHERE userid = 'userid'

Once I had confirmed the records were present, I used the below scripts to delete the stuck rows:

DELETE FROM ACTIVITY WHERE userid = 'userid'
DELETE FROM SY00800 WHERE userid = 'userid'