Year: 2022

I meant to post this a while ago, but apparently didn’t and have just found the screen shots again. This was reported to me by a client who was trying to run a multicurrency report in Management Reporter.

When columns were flagged with currency translations, this error was produced and shown in the Report Queue Status window:

I checked in the event viewer and found this error:

After doing some investigation, I realised that this error is one I have seen before. The issue is that there is a longstanding bug in the legacy connection of Management reporter; if you need to use currency translations, then you need to be using the Management Reporter Data Mart.

A few years ago I posted about securing the Web Services for Microsoft Dynamics GP which is something I would recommend that everyone does, most especially if they are exposed externally.

However, when the certificate expires and needs to be replaced, there are a few steps you need to follow to do this and they are mainly done at the command line as it is the certificate binding which needs to be updated.

First, you need to delete the certificate (the highlighted section is the port used when the certificate was originally bound):

netsh http delete sslcert ipport=0.0.0.0:48666

Second, you need to add the new certificate; the certhash is the certificate thumbprint:

netsh http add sslcert ipport=0.0.0.0:48666 certhash=7c69255774b41dd0f4edc3a34514e5f08a526443 appid={8EFC5047-33C1-438D-B76B-3634DDCF585F}

Once the above steps have been completed, the Microsoft Dynamics GP Service Host service should be restarted.

I’m a little late, but there was a good article fromAngela Eckman Ebensteiner on the Dynamics GP Support and Services Blog on the differences between the Historical Inventory Trial Balance and the Historical Stock Status Report and why the HITB (Historical Inventory Trial Balance) is the one you should be using to determine the value of your stock on a given day and to reconcile with the GL.

If you’re a user of the Inventory Control module, then the article is definitely worth a few minutes of your time.

Back in Microsoft Dynamics GP 10 and before, the ability to produce EFT files was not available in UK language installs. This meant a lot of clients used other methods for producing the EFT files and many of them are still using those alternative methods. That said, I’ve done work with a few recently where they had changed banks or otherwise needed to mke changes to the EFT format, so we hve switched them to using the standard functionality so the format can be maintained esily through the EFT File Format window ((Financial area page » Cards » EFT File Format)).

This largely works, but as the clients had been running EFT payment runs, there was a multi-year backlog of entries in the Generate EFT File window ((Financial area page » Transactions » Generate EFT File)) which needed to be cleared. While it is possible to hit the generte button and have them cleared by creating the files, for some cients we were looking at hundreds of files which would need to be created.

Instead I explored the database and found two tables holding entries which would need to be cleared down to remove the pending batches:

/*
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).
*/
DELETE FROM CM20202 --Checkbook Transaction Electronic Funds Transfer (CM20202)
DELETE FROM CM20203 --Checkbook EFT Transaction Batch (CM20203)

This script removes all entries from the Generate EFT File window; before running the script make sure you don’t need any of the entries, test the script on a test company or system and make sure you have a good backup before running the script on live.

While I am a big advocate of making changes to data via the front end or using an officially supported integration tool, there are times when a SQL script is the quicker way of making a small update.

I recently needed to update the purchasing unit of measure on 60,000 inventory items. The below SQL script was created to make this update:

/*
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
	['Inventory Item Master']
SET
	['Inventory Item Master'].PRCHSUOM = ['Inventory U of M Schedule Detail Setup'].UOFM
FROM
	IV00101 AS ['Inventory Item Master'] --Item Master (IV00101)
INNER JOIN
	IV40202 AS[/sqlgrey] ['Inventory U of M Schedule Detail Setup'] --Inventory U of M Schedule Detail Setup (IV40202)
		ON	[/sqlgrey]
			['Inventory U of M Schedule Detail Setup'].UOMSCHDL = ['Inventory Item Master'].UOMSCHDL

This script was suitable for the client’s data against which it was run, but may not be suitable for all data configurations.

As always before running a script, make sure you understand what the script does, test it on a test company/system and have a good backup before running on live and verify the results.