Hands On With MDGP 2018 RTM New Features: DocAttach Available on More Windows

Microsoft Dynamics GPMicrosoft Dynamics GP 2018 RTM has now been released and I have been taking a Hands On look at the installation and configuration of the new version.

I am now going to take a hands on look at the new and enhanced features introduced in Microsoft Dynamics GP. Rather than do this as a continuation of the Hands On series I wrote in December, as originally intended, I am going to split it out into a separate hands On With the features post. The series index is for this new features series, can be found here.

The first of the features announced by Microsoft was the introduction of DocAttach on more windows: the DocAttach button on the action pane makes the function more accessible to users. The windows updated for this are:

  • General Ledger Transaction Entry
  • Receivables Transaction Entry
  • Asset General Information
  • Employee Entry
  • I-9 Form (Payroll)
  • Journal Entry Enquiry
  • Payables Transaction Entry Zoom
  • Receivables Transaction Entry Zoom

I quite like the DocAttach function in Microsoft Dynamics GP, although I am careful to be clear with clients and prospects that is is document attachment and not document management.

While it’s good to see DocAttach getting wider exposure across Microsoft Dynamics GP on the action panes, it’s especially nice to see Fixed Asset Management getting some DocAttach improvements as many of my clients have images of their assets to store:

Fixed Asset General

Click to show/hide the Hands On With MDGP 2018 RTM New Features Series Index

Scripts to Remove Analytical Accounting

Microsoft Dynamics GPBack in February 2017, I posted about a Microsoft Knowledge Base article on removing Analytical Accounting (AA) from Microsoft Dynamics GP. At the time, the KB article was unavailable, but the download links still worked (if you knew what they are).

I was contacted by someone recently asking if I had a copy of the scripts as the download links no longer work (the KB article itself is available).

This was unusual timing as I had just been onsite with a client where we encountered issues upgrading their system from 2016 RTM to 2018 RTM. In that case, the solution was to remove AA from the system as they had the module used, but never implemented it.

To remove AA, I dipped into my script library for the AA removal scripts from KB915903. After being alerted at the scripts were no longer available, I thought it might be useful to others to post them here.

The scripts are available from the following links:

  1. KB915903_AA_Remove_AACompete_DYNAMICS_10 – this script needs to be run against the system database (typically called DYNAMICS).
  2. KB915903_10 – this script should be run against all of the company databases.
  3. KB915903_DYNAMICS_Remove_DB_Upgrade_10 – this script is run against the system database.

Dynamics GP Tech Conference 2017: Comprehensive Doc Attach

Microsoft Dynamics GPMicrosoft Dynamics GPThis post is part of the series I am doing on the Dynamics GP Tech Conference 2017.

The Document Attachment module has been extended into the action pane in quite a few new windows:

  • Document Attachment on Receivables Transaction Entry
  • Document Attachment on General Ledger Entry
  • Document Attachment on Employee Inquiry
  • Document Attachment in I-9 Form
  • Display Vendor notes on Vendor Inquiry
  • Display Customer notes on Customer Inquiry
  • Display Project Notes in Project Inquiry and PA Project Inquiry
  • Document Attachment in Asset General Information
  • Option to attach new documents in Inquiry windows – there is a setting in the Document Attachment Setup winodw to enable this (and to require a password to be entered).

Hands On With Microsoft Dynamics GP 2016 R2: Save Fixed Assets With Suffix

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.

in this post, I am “hands on” with the Save Fixed Assets With Suffix Feature of the Day, which was the fifteenth feature.

When this feature was announced, I said I was puzzled by it as I thought you already could, but when testing found that in GP 2016 R1, you could change the Suffix, but GP wasn’t happy and produced a number of errors.

Mark Polino also thought this was not a new feature, but the return of a , possibly inadvertently, removed feature.

The Asset General Information window (Financial >> Cards >> Fixed Assets >> General) now allows you to overtype the Suffix field (to the right of the Asset ID field:

Asset General Information

Click to show/hide the Hands On With Microsoft Dynamics GP 2016 R2 Series Index

Hands On With Microsoft Dynamics GP 2016 R2: POP To FA Link Includes Taxes

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 thirteenth Feature of the Day I am going “hands on” with is POP to FA Link to Include Taxes.

This feature will be popular with some of our clients who deal with non-recoeverable VAT (VAT is a UK sales tax charged at a standard rate of 20%) as non-rec VAT means they would need to depreciate the gross value of the asset including VAT where in previous versions the asset would be created net and need to be amended.

To use this feature, the Include Tax in Acquisition Cost checkbox on the Fixed Assets Company Setup window (Financial >> Setup >> Fixed Assets >> Company) under the Purchasing Options:

Fixed Assets Company 	Setup

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: POP To FA Link Includes Taxes”

Removing Analytical Accounting from Microsoft Dynamics GP

Microsoft Dynamics GPI recently posted a script to remove the Fixed Asset Management tables from Microsoft Dynamics GP. I wrote that script for a client who wanted to start using Fixed Asset Management, but it turned out the module had been installed at some point in the past and then removed and the client wanted to reintroduce it.

We’ve just had a similar instance come up with a client upgrading from Microsoft Dynamics GP 2013 SP2 to 2016 R2 where Analytical Accountijng has been installed at some stage and removed and was stopping the upgrade from going through.

In this case though, there was (operative word was) a Knowledge Base article from Microsoft on how to remove Analytical Accounting which is no longer available (hopefully it will be back as I understand Microsoft are moving to a new KB system).

The script was available when we had the issue, but a couple of weeks later, the question was asked by one of the other MVPs. By the time I saw the question, they’d been emailed the scripts by one of the others.

However, when my colleague downloaded the scripts they had also saved a copy of the web page, which meant I could grab the download links for the scripts.

  1. Company Database Script
  2. System Database Script

After running the Microsoft scripts, you’ve downloaded using the above links, run the below script on the system database to remove the Analytical Accounting entries from the upgrade tables:



As always before running scripts, make sure you have a good backup of your databases.

UPDATED 20/01/2018: Download links updated to working ones

Microsoft Dynamics GP January Hotfix Released

Microsoft Dynamics GPI don’t usually post about releases, except for major version releases. However, the January Hotfix release caught my eye with a few of the included features in the 2016 R2 one.

As well as having the US Payroll updates there are updates to the following which would be of benefit to clients in the UK:

  • VAT Daybook

    • VAT Daybook Summary reports may not show correct data in boxes 1, 6 and 7 if you voided a transaction.
    • VAT detail report may show incorrect figures if you have 2 or more tax codes on a transaction.
  • General Ledger

    • The Excel copy and paste function may not calculate the credit/debit column correctly when using an allocation account.
  • Fixed Asset Management

    • Tax values may not update properly if a user marks to create multiple fixed assets in the Fixed Asset Purchase Order additional information window.
  • Bank Reconciliation

    • You may experience performance issues with the Checkbook Register Inquiry window after you install GP 2016 R2.
  • Project Accounting

    • Ability to change line distributions for time and material projects in revenue recognition.
    • Fee Accounts not available in line distributions button.
    • Purchase Receiving’s not reflecting WIP account edits from Purchase Order for non-inventory items.
  • System Manager

    • Fixed issues with Uncollated printing.

The hotfixes for Dynamics GP 2013 R2 and 2015 R2 only contain US Payroll fixes.

The post from the Dynamics GP Support and Services blog contains the download links.

After Removing Fixed Asset Management Tables Add Entry To DB_Upgrade

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).

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

	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)

SQL Script To Remove Fixed Asset Management Tables

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).

DECLARE @TablePrefix VARCHAR(5) = 'FA'

DECLARE @SQL_Statement VARCHAR(1000)


	cursor_InterID CURSOR 
	OPEN cursor_InterID


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




MDGP 2016 R2 Feature of the Day: Save Fixed Asset ID With Suffix

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 fifteenth Feature of the Day is Save Fixed Asset ID with Suffix.

Asset General Information

This feature puzzled me a little, as I thought you could already do this, so I gave it a quick try in 2016 R1. You can do it, but GP doesn’t really want you to.

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