MVP Belinda Allen has posted a suggestion to MS Connect asking Microsoft to add a bar code field to Item Maintenance, Item Transaction Inquiry and Stock Count Entry.

I think this would be a good additin, so please take a look and cast your vote.

In the last post I mentioned that I create scripts when implementing Microsoft Dynamics GP and then make them available to clients when they would be of benefit. The last script I posted, allowed calendars to ve copied from one database to another. Afetr writing that scirpt I then adapted it by wrapping a cursor around it which allowed a source database to be defined and the calendar copied to all other databases.

The original script is still useful as it allows a targeted copying of calendars from a source to a destination database, but the new allows calendars to be quickly replicated across all companies if they share the same financial year.

The script has two parameters (highlighted) at the top which need to be set:

Year

SourceDatabase

Continue reading “Copy Fiscal Calendar To All Companies”

I have a bit of catching up to do on writing posts, but to start the year off I am back to some SQL scripts. As I have mentioned before I have a number of clients with multiple, and in some cases well over 100, companies in Microsoft Dynamics GP.

I do try to make life easier for them when possible so have been sharing a number of scripts I have for implementing systems. One such script is one which will copy financial (fiscal) calendars from one database to another.

There are three parameters at the top which need to be set before running the script:

1. Year
2. Source Company
3. Destination Company

These parameters are highlighted below:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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 @Year VARCHAR(4) = '2014'
DECLARE @SourceDatabase VARCHAR(5) = 'TWO'
DECLARE @DestinationDatabase VARCHAR(5) = 'THREE'
DECLARE @SQLStatement VARCHAR(1000)

SET @SQLStatement =
	'INSERT INTO ' + @DestinationDatabase + '..SY40101
		(YEAR1,FSTFSCDY,LSTFSCDY,NUMOFPER,HISTORYR)
	--VALUES
		(SELECT
			YEAR1,FSTFSCDY,LSTFSCDY,NUMOFPER,HISTORYR
		FROM
			' + @SourceDatabase + '..SY40101
		WHERE
			(SELECT
				COUNT(YEAR1)
			FROM
				' + @DestinationDatabase + '..SY40101
			WHERE
				YEAR1 = ' + @Year + ') = 0
		AND
			YEAR1 = ' + @Year + ')'
EXEC (@SQLStatement)

SET @SQLStatement =
	'INSERT INTO ' + @DestinationDatabase + '..SY40100
		(CLOSED,SERIES,ODESCTN,FORIGIN,PERIODID,PERIODDT,PERNAME
		,PSERIES_1,PSERIES_2,PSERIES_3,PSERIES_4,PSERIES_5,PSERIES_6,
		YEAR1,PERDENDT)
	--VALUES
		(SELECT
			CLOSED,SERIES,ODESCTN,FORIGIN,PERIODID,PERIODDT,PERNAME
			,PSERIES_1,PSERIES_2,PSERIES_3,PSERIES_4,PSERIES_5,PSERIES_6,
			YEAR1,PERDENDT
		FROM
			' + @SourceDatabase + '..SY40100
		WHERE
			(SELECT
				COUNT(YEAR1)
			FROM
				' + @DestinationDatabase + '..SY40100
			WHERE
				YEAR1 = ' + @Year + ') = 0
			AND
				YEAR1 = ' + @Year + ')'
EXEC (@SQLStatement)
GO

The calendar will only be copied if the destination database does not contain a calendar with the same name.

I ‘ve been involved in quite a few upgrades recently and have had a few issues arise. One of them was on one site after I had installed Microsoft Dynamics GP, the web client and web services and also Management Reporter.

I did some work to upgrade the customised forms and reports and then imported them through Customisation Maintenance (Microsoft Dynamics GP menu » Tools » Customisation » Customisation Maintenance). Or at least I tried to as I got an error with the SOP_Entry form:

Continue reading “Customization Maintenance: “Unable to open customizations dictionary””

While ding an upgrade of a client to Microsoft Dynamics GP I suddenly started getting an error when navigating the system; for example, when opening the General Journal Entry window:

Continue reading “Error Registering Table GL_Account_MSTR”

One of Perfect Image’s clients is merging with another organisation and I’ve been onsite a few times to help them configure the new companies and import data. One of the items on my list was to use Integration Manager to import the new chart of accounts. While doing this I used VBA to extend the integration to map between the chart of accounts in the new system to the new format and numbering wanted in Microsoft Dynamics GP.

It was when I came to test the integration with the mapping active that I encountered my problem:

Continue reading “Integration Manager Error: Error Executing Script ‘Before Integration’ Line 17: – Object required: ‘gpObj’”