Restore The Account Segment Warning

● Ian Grieve ●  ● 4 Comments   ● 

Microsoft Dynamics GPWhen new accounts are being created in Microsoft Dynamics GP a warning message is displayed if an entered segment does not exist in the Segment Master (GL40200) table:

Restore The Account Segment Warning

If the checkbox is marked then the warning message is not displayed for that user anymore and there is no way to restore the message through the front end of the system.

The below script, allows the message to be restored for a named user (change the highlighted text to the required user):

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
DECLARE @USERID VARCHAR(20)
SET @USERID = 'userid'

DELETE FROM
	SY01401
WHERE
	coDefaultType = 13
AND
	USERID = @USERID
GO
● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2016 R2: GL Distribution Line Display UI change

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPWith the release of Microsoft Dynamics GP 2016 R2 it’s time for a series of “hands on” posts where I go through the installation of all of it’s components and also look at the new functionality introduced; the index for this series can be found here.

The feature I am going “hands on” with is the seventh Feature of the Day, GL Distribution Line Display UI change.

Continue reading → Hands On With Microsoft Dynamics GP 2016 R2: GL Distribution Line Display UI change

● Categories: Dynamics, GP, Microsoft, Uncategorized ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

After Removing Fixed Asset Management Tables Add Entry To DB_Upgrade

● Ian Grieve ●  ● 3 Comments   ● 

Microsoft Dynamics GPIn the previous post, I posted a SQL script which can be used to delete all tables from a MIcrosoft Dynamics GP implementation for a specified product. That script will generate you the drop and delete commands required to remove a product, but may, for some products, still cause an error if you try to add the product again. I don’t have a screenshot of the error, but it is one where GP Utilities complains the module is too old to upgrade.

This is the case for Fixed Asset Management (FAM); I’ve also had the same problem when adding FAM for the first time. The below script can be used to add an entry to the DB_Upgrade table which will resolve the error.

The highlighted parameters, at the top of the script, will need to be defined; the versions should be the same as the products installed in Dynamics GP; I would avoid setting them to the same as the Dynamics GP product id as this is often different to the other features.

For fixed assets on Microsoft Dynamics GP 2016 R1, the major version and build number are set to 16 and 389 respectively.

If you have already tried to run GP Utilities, a row will have been inserted into DB_Upgrade table which will need to be upgraded.

But worry not, the script will update an existing row or insert a new one as appropriate.

Before running the script make sure that the update statement will not overwrite existing data with an invaoid value.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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).
*/
USE D16R1
GO

DECLARE @PRODID INT = 309
DECLARE @db_verMajor INT = 16
DECLARE @db_verBuild INT = 389

UPDATE DB_Upgrade SET db_verMajor = @db_verMajor, db_verBuild = @db_verBuild,db_verOldMajor = @db_verMajor, db_verOldBuild = @db_verBuild WHERE db_name = DB_NAME() AND PRODID = @PRODID

IF (SELECT COUNT(*) FROM DB_Upgrade WHERE db_name = DB_NAME() AND PRODID = @PRODID) = 0
	INSERT INTO DB_Upgrade (db_name,PRODID,db_verMajor,db_verMinor,db_verBuild,db_verOldMajor,db_verOldMinor,db_verOldBuild,db_status) VALUES (DB_NAME(),@PRODID,@db_verMajor,0,@db_verBuild,@db_verMajor,0,@db_verBuild,0)
GO
● Categories: Fixed Asset Management, GP, Microsoft ● Tags: , , , , ,  ● Permalink ● Shortlink ●

SQL Script To Remove Fixed Asset Management Tables

● Ian Grieve ●  ● 5 Comments   ● 

Microsoft Dynamics GPEvery so often when doing an upgrade, or implementing a module, for a client, we encounter errors when doing the GP Utilities database upgrade. This time round we encountered the error when implementing Fixed Asset Management (FAM) in Microsoft Dynamics GP 2015 R2.

We had previously upgraded the client from Dynamics GP 2010 R2 where they had not been using Fixed Asset Management and never had. However, despite the feature not being installed in Dynamics GP 2010 R2 and the client never knowing having used it, there were tables for Fixed Asset Management in their of the 20+ company databases; this looks like one of their previous partners had done something odd when creating these companies.

The solution in this case was to remove all of the Fixed Asset Management tables from the database. When I have done this type of thing before I have manually written scripts to do this, but have tired of doing so (the previous time I had to do this it was the HR modules.

So I wrote a simple script using a cursor which is run against the system database and which loops through all of the company databases and generates delete scripts for all of the tables for the designated module.

It also generates scripts to delete the rows from the DU tables in the system database.

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

  1. The database at the top which should be a system database
  2. @PRODID which is the numeric product id; for FAM this 309
  3. @TablePrefix which is the alpha prefix to the table names, which for FAM is FA

The parameters are not authenticated or verified in any so oyu need to make sure the product id and table prefix are correct before proceeding.

When the script is run, output it to Text which will give you a series of DROP TABLE commands you can then verify you are happy with the scripts before running them. I would strongly recommend checking the scripts and running them on a test system containing a copy of live first to ensure the result is what you require.

These fields have been highlighted in the, below, script:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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).
*/
USE D16R1
GO

DECLARE @PRODID INT = 309
DECLARE @TablePrefix VARCHAR(5) = 'FA'

DECLARE @SQL_Statement VARCHAR(1000)

CREATE TABLE #Scripts(
	COMMAND VARCHAR(200)
)

DECLARE
	cursor_InterID CURSOR 
FOR 
	SELECT
		RTRIM(INTERID)
	FROM
		SY01500
	UNION
		SELECT DB_NAME()
	
	OPEN cursor_InterID

	DECLARE @INTERID VARCHAR(100)

	FETCH NEXT FROM
		cursor_InterID
	INTO
		@INTERID
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
			IF (@@FETCH_STATUS <> -2)
				BEGIN
					SET @SQL_Statement = 'INSERT INTO #Scripts (COMMAND) (SELECT ''DROP TABLE ' + @INTERID + '..'' + name FROM ' + RTRIM(@INTERID) + '.sys.tables WHERE name LIKE ''' + @TablePrefix + '%'')'
					EXEC (@SQL_Statement)
				END
			FETCH NEXT FROM
				cursor_InterID
			INTO
				@INTERID
		END
	CLOSE cursor_InterID
DEALLOCATE cursor_InterID

INSERT INTO #Scripts (COMMAND) (SELECT 'DELETE FROM DB_Upgrade WHERE PRODID = ' + CAST(@PRODID AS VARCHAR(5)))
INSERT INTO #Scripts (COMMAND) (SELECT 'DELETE FROM DU000010 WHERE PRODID = ' + CAST(@PRODID AS VARCHAR(5)))
INSERT INTO #Scripts (COMMAND) (SELECT 'DELETE FROM DU000020 WHERE PRODID = ' + CAST(@PRODID AS VARCHAR(5)))
INSERT INTO #Scripts (COMMAND) (SELECT 'DELETE FROM DU000030 WHERE PRODID = ' + CAST(@PRODID AS VARCHAR(5)))
GO

SELECT COMMAND + CHAR(10) + 'GO' FROM #Scripts
GO

DROP TABLE #Scripts
GO
● Categories: Dynamics, Fixed Asset Management, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2016 R2 Feature of the Day: POP to FA Link to Include Taxes

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2016 R2; as the most recent versions have been, these posts are in the form of PowerPoint slides; I am reposting them here so they can be read more easily.

The series index for this series of posts is here.

The thirteenth Feature of the Day is POP to FA link to include taxes.

An option to include tax as part of the acquisition cost when adding a capital item in Fixed Assets from a purchase receipt.

The first change is to the Fixed Assets Company Setup window which sets the default value:

Fixed Assets Company Setup

Continue reading → MDGP 2016 R2 Feature of the Day: POP to FA Link to Include Taxes

● Categories: Dynamics, Fixed Asset Management, GP, Microsoft ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2016 R2 Feature of the Day: Bank Rec Tracks History

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2016 R2; as the most recent versions have been, these posts are in the form of PowerPoint slides; I am reposting them here so they can be read more easily.

The series index for this series of posts is here.

The tenth Feature of the Day is Bank Rec Tracks History.

Thre is a new process for Reconciled Transaction on the Routines menu in Financials which moves reconciled transactions to history.

Reconciled Transaction Maintenance

With data moved to history, the performance of the Bank Reconciliation processes will improve and anything which improves performance is to be welcomed.

Click to show/hide the MDGP 2016 R2 Feature of the Day Series Index

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2016 R2 Feature of the Day: Link Credit Card Invoices to Original Invoice

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2016 R2; as the most recent versions have been, these posts are in the form of PowerPoint slides; I am reposting them here so they can be read more easily.

The series index for this series of posts is here.

The eighth Feature of the Day is Link Credit Card Invoices to Original Invoice.

This feature of the day, updates the transaction description on the credit card vendor invoice to easily track back to the originating voucher:

Payables Transaction Inquiry

Continue reading → MDGP 2016 R2 Feature of the Day: Link Credit Card Invoices to Original Invoice

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

Microsoft Dynamics GP Core Installation and Configuration Credential Acquired

● Ian Grieve ●  ● 3 Comments   ● 

Association of Dynamics ProfessionalsI mentioned a few posts back that I had sat some exams for the Association of Dynamics Professionals. Well, I passed the Core Financials Setup and Functionality one, and also sat an exam for the Microsoft Dynamics GP Core Installation and Configuration credential.

I heard back yesterday that the Credentialing Council of the Association of Dynamics Professionals (DynamicsPro) has determined that I have met all requirements of the Microsoft Dynamics GP Core Installation and Configuration credential.

GP Core Financials Setup & Functionality

For those not in the know, the Association of Dynamics Professionals (DynamicsPro) is an independent, not-for-profit membership organization devoted to the global Microsoft Dynamics community of partners, customers and Microsoft.

Their mission is to ​establish and maintain professional competency standards and assessments​ for the betterment of the entire community.

I believe I am one of a very small handful of people in the UK to have this credential; nice to have them both too.

● Categories: Association of Dynamics Professionals, Associations, Dynamics, GP, Microsoft ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

Microsoft Dynamics GP Core Financials Setup & Functionality Credential Acquired

● Ian Grieve ●  ● 2 Comments   ● 
Association of Dynamics Professionals

I mentioned a few posts back that I had sat some exams for the Association of Dynamics Professionals.

Well, I heard back yesterday that the Credentialing Council of the Association of Dynamics Professionals (DynamicsPro) has determined that I have met all requirements of the Microsoft Dynamics GP Core Financials Setup & Functionality credential.

GP Core Financials Setup & Functionality

For those not in the know, the Association of Dynamics Professionals (DynamicsPro) is an independent, not-for-profit membership organization devoted to the global Microsoft Dynamics community of partners, customers and Microsoft.

Their mission is to ​establish and maintain professional competency standards and assessments​ for the betterment of the entire community.

I believe I am one of a very small handful of people in the UK to have this credential.

● Categories: Association of Dynamics Professionals, Associations, Dynamics, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2016 R1 Feature of the Day: Budget Import Duplicate Accounts Import Exception Report

● Ian Grieve ●  ● 1 Comment   ● 

I was a little surprised to see Microsoft announce Display Duplicate G/L Accounts on Budget Exception Report as a separate feature of the day as it looks to be part of the Budget Import Exception Report which is a new feature in Dynamics GP 2016 R1 itself:

Budget Import Exception Report

The original feature of showing accounts the budget which don’t exist in Dynamics GP is going to be fabulously well received; this one is a very good addition which I think may not be fully understood by some.

I have had to explain to a few clients that having the same account on the file more than once will result in only the final value ending up in Dynamics GP as it will overwrite the previous entries. If you want to load values in multiple times and have the cumulative values, you need to load multiple budgets and then use the Consolidate Budgets feature.

Click to show/hide the MDGP 2016 R1 Feature of the Day Series Index

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●