Select Microsoft Dynamics GP GL Accounts for Import

Microsoft Dynamics GPWorking 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[

Assign a Microsoft SQL Server Role to a User in All Microsoft Dynamics GP Databases

Microsoft SQL ServerI did some work for a client recently which involved creating a report which could be run against any database; we therefore needed to add the user to a database role in all databases which gave access to the relevant SQL objects.

The below script generates a SQL script which can be used to alter the role to assign it to the specified user in all of the Microsoft Dynamics GP company databases.

The script assumes the user already has a server login and that the role exists in all databases:

DECLARE @DatabaseRole VARCHAR(140) = 'db_reports'
DECLARE @Username VARCHAR(140) = 'AZRCRV\iang'

SELECT 'USE [' + RTRIM(INTERID) + ']
GO
ALTER ROLE [' + @DatabaseRole + '] ADD MEMBER [' + @Username + ']
GO'
FROM
	SY01500 AS ['Company Master']
GO

Enable Workflow Step Email Notifications in Microsoft Dynamics GP

Microsoft Dynamics GPI was doing some work for a client to build a workflow process in Microsoft Dynamics GP recently and we only got the SMTP details quite late in the process. This meant that we built quite a large workflow process without being able to enable the email notifications. Once we did have the SMTP available we needed to go back through all of the steps and enable the email notifications.

Rather than do this manually, which would have been a time consuming process, I looked at the tables and identified the table and fields which would need to be updated. This was the Workflow Step Table (WF100003) table; the below script can be used to enable the notifications, set the Message ID and enable the include Document Attachments (this was for a payables batch approval process):

/*
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 WF100003 SET EmailMessageID = '^ASSIGN PO APPROVAL' ,Workflow_Step_Send_Email = 1 ,WFIncludeDocumentAttach = 1 WHERE Workflow_Step_Send_Email = 0

As always with scripts, make sure you have a good backup and test before using on a live system.

Upcoming Microsoft Dynamics GP Webinars from ISC Software

ISC Software SolutionsEvery month at ISC Software I present a webinar on Microsoft Dynamics GP and related products. We typically have the next three upcoming monthly webinars I’ll be delivering scheduled.

We run these webinars on a monthly basis, with occasional extra webinars added to the schedule so it is worth checking the Webinar Schedule page every so often.

The upcoming webinars are:

Reporting from Microsoft Dynamics GP
In March is Reporting from Microsoft Dynamics GP; explore the standard reporting options available with Microsoft Dynamics GP.​​​​

Tue, 15th March, 2021 4:00 PM – 4:45 PM BST

Register Here

GP Elementz  from ISC Software
In April is GP Elementz ​from ISC Software; discover the add-ons for Microsoft Dynamics GP from ISC Software.

Tue, 19th April, 2021 4:00 PM – 4:45 PM BST

Register Here

Integrating with  Microsoft Dynamics GP
In May is Integrating with ​Microsoft Dynamics GP​; learn how you can integrate with Microsoft Dynamics GP​​.

Tue, 17th May, 2022 4:00 PM – 4:45 PM BST

Register Here

Script to Help Check For Missing Microsoft Dynamics GP Workflow Customisations

Microsoft Dynamics GPIf you’re a regular reader, you’ll be aware that I am a big fan of the Microsoft Dynamics GP Workflow module which can be used for the approval of batches, documents, security and more. Workflow can easily be customised to allow for extra fields to be available in the workflow conditions and on the notification emails (which I cover in chapters 9 and 11 of my Microsoft Dynamics GP Workflow (3rd Edition) book.

One issue with making these changes is that they can be removed or overwritten when you upgrade Microsoft Dynamics GP. The following script can be used to help check the three tables in which this can have an impact; the way I work is that I export the tables before running the upgrade and then again afterwards to see what the changes are. If any of the customisations are gone, I determine the best way of adding them back.

The highlighted section is the Workflow Type Name which you want to verify.

/*
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 @WorkflowTypeName VARCHAR(51) = 'Purchase Requisition Approval' DECLARE @FieldsListGuid VARCHAR(37) = (SELECT FieldsListGuid FROM dbo.WF100001 WHERE Workflow_Type_Name = @WorkflowTypeName) -- Query Designer Fields List SELECT * FROM CO00121 WHERE FieldsListGuid = @FieldsListGuid ORDER BY SEQNUMBR -- QueryDesigner_Relationships SELECT * FROM CO00122 WHERE FieldsListGuid = @FieldsListGuid ORDER BY SEQNUMBR -- Workflow Template Fields - Workflow Assignment SELECT * FROM WF40202 WHERE Workflow_Type_Name = @WorkflowTypeName AND Email_Message_Type = 2 ORDER BY SEQNUMBR -- Workflow Template Fields - Workflow Action Completed SELECT * FROM WF40202 WHERE Workflow_Type_Name = @WorkflowTypeName AND Email_Message_Type = 3 ORDER BY SEQNUMBR

Script to Remove Microsoft Dynamics Workflow History Where No Action Is Required

Microsoft Dynamics GPI have quite a few clients using the Microsoft Dynamics GP Workflow module as standard out of the box with no customisations required, but a fair number of clients do have one customisation or another.

A few of them have a customisation allowing a SQL view to be used in the workflow conditions and a few have a script like the one in this post to remove some of the workflow history.

If you have a workflow process which branches off so not all steps are followed, then a record is record in the history as “no action is required”; if you have a large workflow process, this can result in many history records being created which can significantly slow down some of the handling of workflow in the system (such as viewing history).

I took a look at the data and after some testing determine that entries in two tables could be removed without causing any issues and would allow the workflow history to be viewable.

The tables are Workflow Step Instance Table (WFI10003) and Workflow History (WF30100); in theory this could be set up as a trigger, but after discussion with the client we created it as a stored procedure which runs on a schedule in SQL Server Agent:

/*
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). */
CREATE PROCEDURE usp_AZRCRV_DeleteWorkflowHistoryNoActionIsRequired AS -- Delete from Workflow Step Instance the lines which do not require approval DELETE FROM WFI10003 -- Workflow Step Instance Table (WFI10003) WHERE Workflow_Step_Status = 1 -- Delete from Workflow History the lines which do not require approval DELETE FROM WF30100 -- Workflow History (WF30100) WHERE Workflow_Comments LIKE 'No approval is required%' GO

Script to Remove Advanced Bank Reconciliation Transaction Matches

Microsoft Dynamics GPI do demonstrations of quite a bit of functionality in Microsoft Dynamics GP which often necessitates restored databases or otherwise reversing transactions and processing. One of the modules I’ve done some demonstrations of recently is the Advanced Bank Reconciliation module from Nolan Business Solutions. One of the features of this module is allowing you to atch statement entries with transactions in Dynamics GP.

If you use the auto-reconcile rules to do the matching and then want to undo the match, you need to select each matched transaction and unmatch them and then do the next and so on, which can take quite a while. The below script will remove the matches from both the NC Matched Statement Trx 11003k (NCABR011) and NC_Matched_GL_TRX (NCABR012) tables:

/*
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 NCABR011 --NC Matched Statement Trx 11003k (NCABR011) SET NC_Matched = 0 ,NC_Match_Number = 0 WHERE NC_Matched = 1 GO UPDATE NCABR012 --NC_Matched_GL_TRX (NCABR012) SET NC_Matched = 0 ,NC_Match_Number = 0 WHERE NC_Matched = 1 GO

This script was created for use ona demo/test system and appears to run without adverse effect, but I would recommend caution (via a backup) and thorough testing before using it on any system.

Web Services Upgrade Error: “The provided windows account is not formatted correctly”

Microsoft Dynamics GPWhen I do an upgrade of the Web Services For Microsoft Dynamics GP, my usual approach is to ru a two Microsoft supplied scripts which remove all web services objects from the system and company databases; I generally do this as we had problems with the upgrade process within the Configuration Wizard. On a recent upgrade I thought I would give the standard upgrade process aother try, as it is a few years since I last tried it.

When the Configuration Wizard was in progress, it stopped with the below error message:

Web Services Configuration Wizard error

A loader exception has occurred.
Loader Errors:
- Microsoft.Dynamics.Security.InvalidWindowsAccountException: The provided windows account is not formatted correctly.
   at Microsoft.Dynamics.Security.ConcreteValidator.ValidateAndCanonicalizeMemberName(String member)
   at Microsoft.Dynamics.Security.ConcreteValidator.ValidateAndCanonicalizeMemberList(List`1 memberList)
   at Microsoft.Dynamics.Security.AzManRoleAssignmentServiceImplementation.ValidateForUpdate(SecurityContext context, SecurityObject securityObject)
   at Microsoft.Dynamics.Security.SecurityService.InvokeValidateForUpdate(SecurityContext context, SecurityObject updatedObject)
   at Microsoft.Dynamics.Security.SecurityService.Update(SecurityContext context, SecurityObject updatedObject)
   at Microsoft.Dynamics.GP.GPSecurityMetadataSystemLoader.PerformSpecialConfigTasksForAdminUsers(Action action)
   at Microsoft.Dynamics.InstallData.Loader.Process(String[] args)

The user account being used was the user account the Web Services had been running under since the last upgrade. I reverted back to running the scripts to remove the web services and then doing a fresh deployment of them. It looks like I will be sticking with this approach going forward.

Microsoft Dynamics GP VBA Entering Debug Mode Without Breakpoint For All Users On First Login

Microsoft Dynamics GPA client reported a problem with some VBA customisations in Microsoft Dynamics GP where the code was stopping as if at a debug point in two places even thought there were no debug points present. Once a user hit F5 to continue the code past a “debug” point, they would not see the problem again until the next time they logged in.

I did a lot of investigation and fiddling around, but could not work out what the issue was.

Eventually, I used Customisation Maintenance to export the two problematic packages and then imported them again. When we tested, the problem had gone away.

I do not know what caused the problem, but it looks like even though there were no visible debug points there was something in the two files making Dynamics GP appear as if there was.

I’d be interested in hearing from anyone who has encountered this issue before and might be able to explain what was happening.

We have a project on the go to replace the VBA modifications, which are quite complex, so for this client the should only be a short term possibly of it reoccurring.

Microsoft Dynamics GP Statement Emailing “Path for the E-mail Status Report is not set up…” Error

Microsoft Dynamics GPI worked on a Microsoft Dynamics GP upgrade project recently for a client who uses the old Adobe statement process. I am not all that familiar with this as most clients are now using the On Blank Paper statement and the standard emailing functionality. Once the upgrade was complete and we were doing some basic testing we encountered an error when trying to email the statements:

E-mail Status Report error

Microsoft Dynamics GP

Path for the E-mail Status Report is not set up. Statements will not be e-mailed.

I did some digging around and found there is a path stored in the Dex.ini file for the e-mail status report:

EmailStatusPath=\\fileserver\Dynamics Central\EmailStatus\

This can be set through the client, but with a number of clients to update it was quicker to do by adding the line to the Dex.ini file.