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 365 Business CentralI 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.

SmartConnect 2021 Error: “HTTP Error 500.19 – Internal Server Error”

eOne SolutionsI’ve been doing some work with a client recently implementing SmartConnect 2021, although the client has done much of the install themselves using the manual as a guide (this was their choice to help keep costs down). When we tried to enable the REST API and navigate to the endpoint to check, we were receiving an error:

HTTP Error 500.19 – Internal Server Error

The requested page cannot be accessed because the related configuration data for the page is invalid.

We did some investigation and research and found that the issue was that one of the required features had been missed during the installation. There are three elements of the WCF Services component of the .NET Framework 4.7 Features which need to be installed, but weren’t.

The screenshot below shows my demo system with the three components installed:

Server Manager - Add Roles and Features Wizard

Once the client added these three components, the REST API worked correctly.

SmartConnect 2021 Error With Excel Data Sources: “Data source name not found and no default driver specified”

eOne SolutionsWe’ve recently upgraded a client from SmartConnect 2018 to SmartConnect 2021 and mosstly had no problems. One issue which came up is when users tried to run an integration which had a Microsoft Excel file as the data source. Each time they tried the integration, and on several machines, they always received the same error:

Error running integration

Connection could not be validated

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

This is an error I have seen before; the users in the previous case who were having problems had the 64-bit version of Microsoft Office installed and the working ones had the 32-bit version. this required us to install the 32-bit version of the Microsoft Access Database Engine 2016 Redistributable from the command line as a silent install.

All of the clients in this case already had a 32-bit version of Excel; a quick check and I found that for SmartConnect 2021 you actually need the 64-bit version of the Microsoft Access Database Engine 2016 Redistributable which again should be installed silently from the command line.

azurecurve ClassicPress Plugins: Load Admin CSS

ClassicPress PluginsThis is part of the azurecurve ClassicPress Plugins series which introduces the plugins I have available for ClassicPress.

The plugin I am going to cover in this post, is one written specifically for ClassicPress ; Load Admin CSS.

Functionality

Change the styling of your admin dashboard with custom CSS.

Options are maintained via a Settings page on the **azurecurve** menu.

Example CSS usage to change the font size in the textarea:

textarea.wp-editor-area{
	font-size: 1.33em;
}

This plugin is multisite compatible, with options set on a per site basis.

Download

The plugin can be downloaded from my GitHub.

Click to show/hide the azurecurve ClassicPress Plugins Series Index

Error Logging into Jet Hub : “IDX20803: Unable to obtain configuration from: ‘System.String’.”

Jet HubI did an install of Jet Reports recently on new demo VM and found that I had a problem logging into the Jet Hub. When I tried to log in, I was always getting this error:

Error message when trying to log into Jet Hub

Error

IDX20803: Unable to obtain configuration from: 'System.String'.

I did some debugging myself and got nowhere in working out the cause of the problem as everything looked fine. An online search found an article on the
Jet Support Knowledge Base which discussed a similar error.

Stepping through the recommended troubleshooting steps I found that running IISRESET in an administrator command prompt resolved the problem. Running this command is part of the installation steps for jet Hub, so it looks like I overlooked running it during the install.

One to make sure I remember to do on the next install of Jet Repots that I do.

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.

Create Grant Statement for All Custom Views in Microsoft SQL

Microsoft SQL ServerOn a recent project, I needed to create a new database role for some new SQL views which had been created.

Creating the role is easy enough, but to make sure all the relevant views (and there were quite a few) looked like a bigger job until I realised I could select from sys.views and generate the grant script at the same time.

In the below script, the first highlighted section is the database role and the second the start of the views to have select permissions granted (I always use a uv_ prefix for views):

/*
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). */
SELECT 'GRANT SELECT ON ' + name + ' TO urpt_SalesViews' + CHAR(10) + 'GO' FROM sys.views WHERE name LIKE 'uv_[/highlight%'

The output of the above should be returned to text and can then be copied and pasted into a new script window.