GP Power Tools Build 26 & 27 New Features: Changes Since Last Series

Winthrop DCThis post is part of the GP Power Tools Build 26 & 27 New Features series in which I’m taking a look at the changes and new features since I last blogged about GP Power Tools.

One of the key changes to GP Power Tools, is that it is now directly distributed by WinthropDC rather than by a third party. The third party route was taken, I believe, as a quick way to market after David Musgrave licensed the code for the Support Debugging Tool in order to create GP Power Tools when he left Microsoft and founded WinthropDC.

If you purchased GP Poer Tools while it was being distributed by WinthropDC you should already have upgraded build 24, or later, to avoid licensing issues.

There have been multiple new features introduced across the two major builds of GP Power Tools. I’ve broken these new features down across two sub-series, which will be posted over the coming days.

Continue reading “GP Power Tools Build 26 & 27 New Features: Changes Since Last Series”

GP Power Tools Build 26 & 27 New Features: Series Index

Winthrop DCThere are a large number of ISV products available to extend Microsoft Dynamics GP, and I’ve taken a look at a very few of them recently. One I looked at last year was the David Musgrave.

I took a Hands On look at implementing the GP Power Tools product followed by going hands on with two of the modules:

I did mean to take a look at more of the modules, but got sidetracked. Since then, there have been a number of changes to the GP Power Tools, which I will be taking a look at in this series.

Continue reading “GP Power Tools Build 26 & 27 New Features: Series Index”

Run Application Error Generating Remittances

Microsoft Dynamics GPI was onsite with a client a while ago helping them create new Citrix XenApp Gold Build Servers. When users were testing, they encountered the following error when generating remittances:

Run application error message


"C:UsersTEMP~1.FLAppDataLocalTempCheck Remittance~2.docx" '
returned 5

Continue reading “Run Application Error Generating Remittances”

Add Company Access Back to sa User

Microsoft Dynamics GPWe’re busy doing some work for a client for whom we’ve recently taken over the support of their Microsoft Dynamics GP implementation. For the initial set of projects, we’re assisting them in the creation of a standalone test systemm. When you do this, the first thing you need to do is log in using the sa account and reset passwords.

However, we found that at some point in the past, all company access had been removed from the sa user account leaving us unable to log into Dynamics GP.

Fortunately, company access is only stored within one table in the system database: User-Company Access (SY60100).

The SQL below will add company access back to the sa user for all company databases:

Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
		SY01500 AS ['Company Master']
			SY60100 AS ['User-Company Access']
			['User-Company Access'].CMPANYID = ['Company Master'].CMPANYID
			['User-Company Access'].USERID = 'sa'
		) = 0

After you’ve run the above to add company access back, you also need to run the SQL insert statement in this post to add POWERUSER access as well.

With the two scripts run, the sa account can be used to reset the DYNSA user and other user accounts.

Find SQL Function In All Databases

Microsoft SQL ServerI did a few posts back in 2016 on finding objects in SQL (a column in all tables, all custom sql objects in a database and trigger in all databases).

I needed to find if a function existed in all databases, so I produced the following script which searches in all Microsoft Dynamics GP databases:

-- drop stored proc if it exists
IF OBJECT_ID (N'usp_AZRCRV_FindFunctionInAllDatabases', N'P') IS NOT NULL
    DROP PROCEDURE usp_AZRCRV_FindFunctionInAllDatabases

-- create stored proc
CREATE PROCEDURE usp_AZRCRV_FindFunctionInAllDatabases
	@Function VARCHAR(50) = 'uf_AZRCRV'
	Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (
	This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
	DECLARE @command nvarchar(max)

	SELECT @command = 'IF EXISTS ( SELECT 1 FROM SY01500 AS [''Company Master''] INNER JOIN sys.databases [''Sys Databases''] ON [''Company Master''].INTERID = [''Sys Databases''].name WHERE name = ''?'')   
							USE [?];
							DECLARE @stmt nvarchar(max) DECLARE @n char(1) SET @n = char(10)
							SELECT DB_NAME() AS ''DB_NAME'', FROM sys.objects AS t
							WHERE t.type_desc LIKE ''%FUNCTION%'' 
							AND LIKE '''+ @Function + '%''

	EXEC sp_MSforeachdb @command

-- grant execute permission on stored proc to DYNGRP

-- execute stored proc
EXEC usp_AZRCRV_FindFunctionInAllDatabases 'dgpp'

Is there a POP Historical Received Not Invoiced Report Available?

Microsoft Dynamics GPI’ve had a couple of people recently ask if it is possible to get a historical version of the Shipments Received but not invoiced* SmartList favourite (located under Purchasing » Receivings Line Items).

Within SmartList, this is not possible as the point-in-time calculations required for a historical report are too complex for a SmartList, even one driven by a view.

While historically this report wasn’t available in Microsoft Dynamics GP, this changed in the Microsoft Dynamics GP 2015 R2 release when a Reporting Services Report version of a Historical Received Not Invoiced Report was introduced. It seems though that many people are remain unaware of this new Historical RNI report.

As long as you have the Reporting Services Reports from the Reporting Tools Setup window deployed you can launch the report from the Purchasing series Reporting Services Reports navigation pane:

Historical Received Not Invoiced

This report allows you to enter criteria including a Date as of.

Currency ID Missing From Batches Integrated Via eConnect

Microsoft Dynamics GPWe’ve been doing some work with a client recently creating a number of integrations in SmartConnect. One of these was creating General Journals into a consolidation company from other Microsoft Dynamics GP databases.

Everything looked fine from a SmartConnect perspective; success reported for the integration and no warnings or errors. However, when we looked at the resulting journal, there was no Currency ID:

General Transaction Entry showing blank Currency ID field

The problem here isn’t actually a SmartConnect one, but an eConnect one which has been known for a long time. There are a number of reports of this, but the one we found when researching was on the Dynamics Blogger.

The solution is to create a chequebook (checkbook) for the Functional Currency.

The company we were importing into, didn’t have one as only the GL was going to be used; once the chequebook was created, the integration worked fine.

Implementing SmartList Builder: Review

eOne SolutionsThis post is part of the series on Implementing SmartList Builder from eOne Solutions.

Of all of the ISV products I have used with Microsoft Dynamics GP, SmartList Builder is the one I have worked with the longest, and is the one I like the most (looking back I’m surprised it’s taken me this long to do a series like this).

It is easy to use, flexible and far better at creating new SmartLists than the in-built SmartList Designer product which Micro created when they passed SmartList Builder back to eOne Solutions.

If you like SmartLists, then I would encourage you to take a look at SmartList Builder; it does take a somewhat technical user to create and maintain SmartLists through it, but they are easy to create and much more flexible than those possible through SmartList Designer. The hardest job is sometimes discovering how the data hangs together.

There are resources such as the GP Tables section of Victoria Yudin’s website or my azurecurve|Microsoft Dynamics GP Table Reference which you can use to find the tables you need.

Implementing SmartList Builder: Security

eOne SolutionsThis post is part of the series on Implementing SmartList Builder from eOne Solutions.

The final element to readying SmartList Builder for use is to configure security so that users without the POWERUSER* role can access it. While this step isn’t absolutely necessary, I would encourage people not to over use the POWERUSER* role.

A better approach would be to create a Super User role instead. Both this role and standard user roles will need to have access to the SmartList Builder windows granting.

This is easy to do, as SmartList Builder ships with some default security tasks and a role:

Security Role Setup

Assign this role to the relevant user or assign the tasks to existing roles and user will, next time they login, be able to access the SmartList Builder window.

Implementing SmartList Builder: Register

eOne SolutionsThis post is part of the series on Implementing SmartList Builder from eOne Solutions.

If you’re testing SmartList Builder, you can use it in the Famrikam Sample company without entering a registration key. However, yo use it on a normal company, even a test one, you will need to input the key.

To do this, log into Microsoft Dynamics GP as a user with the POWERUSER* role and open the SmartList Builder Register window (Administration area page » Setup » SmartList Builder » Register SmartList Builder) and enter the key in the Registration Key field:

SmartList Builder Register

Click Save to sdave the key and close the window.