GP Power Tools Build 26 & 27 New Features: New in Build 26: System Module Enhancements

Winthrop DCThis post is part of the New Features in GP Power Tools Build 26 sub-series 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.

The first area of new features I’m going to cover, is the System module in which there are a number of new features:

  1. Window Visibility
  2. System Status Report
  3. Send Email Window

Window Visibility/Discoverability

Window visibility/discoverability has been improved by the introduction of Find a Window and Raise All Windows options on the Tools menu:

New options on Tools menu

These functions aim to improve discoverability and visibility of Microsoft Dynamics GP; they should prove useful by making it easier to find windows and also by allowing you to bring all windows in front of the main Dynamics GP window.

System Status Report

The system status report has a number of improvements:

  1. Registered modules have been added.
  2. List of Visual Studio Addin DLLs have been added.
  3. Details maximum users allowed.
  4. Details if Dynamics GP is running on a Terminal Server.

Send Email Window

The Send Email window has been enhanced to word wrap.

The new feature will make it easier for users to create and review the email message being sent.

GP Power Tools Build 26 & 27 New Features: New in Build 26
System Module Enhancements
Administrator Tools Module Enhancements
Developer Tools Module Enhancements
Database Tools Module Enhancements

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

Winthrop DCThis post is the series index for the New in Build 26 sub-series 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.

In this sub-series I am going to take a look at the new features introduced in build 26; the posts are broken down by module for easy reading.

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

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

RUN APPLICATION ERROR

"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 (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).
*/
INSERT INTO SY60100
	(TRKUSER,USERID,CMPANYID,SRBCHSEC_1,SRBCHSEC_2,SRBCHSEC_3,SRBCHSEC_4,SRBCHSEC_5,SRBCHSEC_6,SRBCHSEC_7,SRSFNSEC_1,SRSFNSEC_2,SRSFNSEC_3,SRSFNSEC_4,SRSFNSEC_5,SRSFNSEC_6,SRSFNSEC_7,MSCPRMIS)
--VALUES
	(
	SELECT
		0,'sa',CMPANYID,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF
	FROM
		SY01500 AS ['Company Master']
	WHERE
		(
		SELECT
			COUNT(*)
		FROM
			SY60100 AS ['User-Company Access']
		WHERE
			['User-Company Access'].CMPANYID = ['Company Master'].CMPANYID
		AND
			['User-Company Access'].USERID = 'sa'
		) = 0
	)
GO

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
GO

-- create stored proc
CREATE PROCEDURE usp_AZRCRV_FindFunctionInAllDatabases
	@Function VARCHAR(50) = 'uf_AZRCRV'
AS
	/*
	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 @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 = ''?'')   
						BEGIN
							USE [?];
							DECLARE @stmt nvarchar(max) DECLARE @n char(1) SET @n = char(10)
							SELECT DB_NAME() AS ''DB_NAME'',t.name FROM sys.objects AS t
							WHERE t.type_desc LIKE ''%FUNCTION%'' 
							AND t.name LIKE '''+ @Function + '%''
						END'

	EXEC sp_MSforeachdb @command
GO

-- grant execute permission on stored proc to DYNGRP
GRANT EXECUTE ON usp_AZRCRV_FindFunctionInAllDatabases TO DYNGRP
GO

-- execute stored proc
EXEC usp_AZRCRV_FindFunctionInAllDatabases 'dgpp'
GO

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.