Month: August 2017

This post is part of the Hands On With the GP Power Tools (GPPT) – Administrator Tools series in which I am taking a hands on look at the various tools offered by GPPT.

The Security Profiler (GP Power Tools area page >> Reports >> Security Profiler) window is one of the most useful parts of GPPT. To use this function, all you need to do is open the window.

One the window is open, whenever a form or report is opened, the application-level security is checked to confirm that the current user has access. Security is also checked to find out whether a customized version (modified, alternate or modified alternate) of the form or report is to be used.

When a report is opened, access is checked for all of the tables linked to the report. To be able to print the report, access must be permitted for the report itself and all the tables linked to the report.

The Security Profiler will also track access to non-dictionary resource Security Objects, such as Customization Tools, Document Access, Letters, Microsoft Dynamics GP Import, Navigation Lists, Series Posting Permissions, and SmartList Objects. If the products are installed, the following objects are also supported, SmartList Builder Permissions and Extender Resources. Security objects from other 3rd party products will show as Unknown Objects.

The Security Profiler window displays each of the queries to the application-level security system and displays the results with all the relevant details of the resources involved:

Continue reading “Hands On With the GP Power Tools – Administrator Tools: Security Profiler”

This post is part of the Hands On With the GP Power Tools (GPPT) – Administrator Tools series in which I am taking a hands on look at the various tools offered by GPPT.

The Resource Information (GP Power Tools area page >> Reports >> Resource Information) window can be used to lookup information on a number of resource types. In this post, I’m going to look at the information available on tables and fields.

To do this, launch the window and change the Resource Type to Tables & Fields:

Continue reading “Hands On With the GP Power Tools – Administrator Tools: Resource Information – Tables & Fields”

This post is part of the Hands On With the GP Power Tools (GPPT) – Administrator Tools series in which I am taking a hands on look at the various tools offered by GPPT.

The Resource Information window (GP Power Tools area page >> Reports >> Resource Information) can best be described as a souped up version of the standard Microsoft Dynamics GP Resource Descriptions windows (Microsoft Dynamics GP menu >> Tools >> Resource Descriptions).

The Resource Information window will display technical, display, and physical names and resource IDs for any dictionary currently installed in the Microsoft Dynamics GP application. It includes information for any:

• Form
• Window
• Field
• Table
• Table group
• Report
• Script (procedure or function, global or form level)

It can also provide information about non-dictionary resource Security Objects, such as Customization Tools, Document Access, Letters, Microsoft Dynamics GP Import, Navigation Lists, Series Posting Permissions, and SmartList Objects.

If the eOne SmartList Builder and Extender products are installed, the SmartList Builder Permissions and Extender Resources are supported; security objects from other 3rd party products will show as Unknown Objects.

This post is part of the Hands On With the GP Power Tools (GPPT) – Administrator Tools series in which I am taking a hands on look at the various tools offered by GPPT.

The Administrator Tools are one of the three modules of GPPT which need to be purchased to be used. As a consultant, this is the module which is probably of most use to me and my clients.

The key features of the Administrator Tools are:

• Avoid data entry errors; company based colour schemes provide an immediate visual cue to which company is being used.
• Gain full administrative and audit control of your security system, including Deny-Based Security.
• Roll out Dex.ini setting changes to all workstations.
• Simplify troubleshooting by disabling third party products and customizations (including Visual Studio Addins and VBA).
• Users can customize window positions and sizes based on the how they use the system.

Continue reading “Hands On With the GP Power Tools – Administrator Tools: Series Index”

The question occasionally comes up from clients as to when the end of support for particular versions of Microsoft Dynamics GP are, and every time I have to go away and do a search for the information (sometimes not very successfully).

Therefore I am posting the link here for future reference: this page lists all versions of Dynamics GP from 9 onwards along with the launch date, end of mainstream and extended support dates .

A client recently had some hardware issues causing problems with the submission of purchases requisitions into a Workflow approval process. The hardware issues were causing some speed issues which resulted in the workflow timing out and the approval status becoming corrupt.

Instead of the PR being approved and going to a status of Final Approved, it was going to Approved, even though there was only one level of approval required by the workflow.

When the hardware issues were resolved, the error stopped occurring, but a number of corrupt PRs were remaining.

The way to correct the data is to reset the workflow status. I produced a small script which does this by resetting the Workflow_Status on the Purchasing Requisition Work (POP10200) table and removing the entries from the following tables:

1. Workflow Step Instance Table (WFI10003)
2. Workflow Tasks Table (WFI10004)
3. Workflow Instance Master (WFI10002)

The list of PRs to be reset are entered in the highlighted part of the script:

/*
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).
*/
CREATE TABLE #POPRequisitions(
	POPRequisitionNumber VARCHAR(21)
)
GO

INSERT INTO
	#POPRequisitions
VALUES
	('REQ00000000000017')
GO

UPDATE POP10200 SET Workflow_Status = 1 WHERE POPRequisitionNumber IN (SELECT POPRequisitionNumber FROM #POPRequisitions)

DELETE FROM WFI10003 WHERE WorkflowInstanceID IN 
	(SELECT WorkflowInstanceID FROM WFI10002 WHERE Workflow_Type_Name='Purchase Requisition Approval' AND WfBusObjKey IN (SELECT POPRequisitionNumber FROM #POPRequisitions))

DELETE FROM WFI10004 WHERE WorkflowInstanceID IN 
	(SELECT WorkflowInstanceID FROM WFI10002 WHERE Workflow_Type_Name='Purchase Requisition Approval' AND WfBusObjKey IN (SELECT POPRequisitionNumber FROM #POPRequisitions))

DELETE FROM WFI10002 WHERE Workflow_Type_Name='Purchase Requisition Approval' AND WfBusObjKey IN
	(SELECT POPRequisitionNumber FROM #POPRequisitions)
GO

DROP TABLE #POPRequisitions
GO

As always before running a script on live, make sure you have a good backup and test the script in a test company.

I wrote the first version of this script a long time ago, but recently had cause to return to it. A client I am working with has configured the posting reports in one company and wanted to copy the same configuration to the other companies.

This script allows you to do this, on a one by one basis. Set the first highlighted parameter to the source company, and the second to the destination company.

/*
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 @SQLStatement VARCHAR(8000)
DECLARE @SourceCompany VARCHAR(5) = 'T16R2'
DECLARE @DestinationCompany VARCHAR(5) = 'P16R2'

SET @SQLStatement = 'UPDATE
				[''''Destination Posting Journal Destinations'''']
			SET
				PRNTJRNL = [''''Source Posting Journal Destinations''''].PRNTJRNL
				,ASECTMNT = [''''Source Posting Journal Destinations''''].ASECTMNT
				,PRTOPRNT = [''''Source Posting Journal Destinations''''].PRTOPRNT
				,PRTOFLNT = [''''Source Posting Journal Destinations''''].PRTOFLNT
			FROM
				' + @DestinationCompany + '.dbo.SY02200 AS [''''Destination Posting Journal Destinations'''']
			INNER JOIN
				' + @SourceCompany + '.dbo.SY02200 AS [''''Source Posting Journal Destinations''''] ON [''''Source Posting Journal Destinations''''].SERIES = [''''Destination Posting Journal Destinations''''].SERIES
					AND [''''Source Posting Journal Destinations''''].TRXSOURC = [''''Destination Posting Journal Destinations''''].TRXSOURC
					AND [''''Source Posting Journal Destinations''''].PTGRPTNM = [''''Destination Posting Journal Destinations''''].PTGRPTNM'
EXEC (@SQLStatement)

As always with an SQL script, please make sure you have a good backup before running it.

The last post was a SQL view to return purchases by vendor by year; in this one I am posting a similar view, but for the Sales series.

/*
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).
*/
IF OBJECT_ID (N'uv_AZRCRV_SalesByCustomerByYear', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesByCustomerByYear
GO
CREATE VIEW uv_AZRCRV_SalesByCustomerByYear AS
SELECT
	['Receivables Transactions'].CUSTNMBR AS 'Customer ID'
	,['RM Debtor MSTR'].CUSTNAME AS 'Customer Name'
	,['RM Debtor MSTR'].CUSTCLAS AS 'Class ID'
	,FORMAT(['Receivables Transactions'].DOCDATE, 'yyyy') AS 'Year'
	,CASE WHEN ['RM Debtor MSTR'].INACTIVE = 1 THEN
		'Inactive'
	ELSE
		'Active'
	END AS 'Status'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].SLSAMNT ELSE ['Receivables Transactions'].SLSAMNT * -1 END) AS 'Purchases Amount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].TRDISAMT * -1 ELSE ['Receivables Transactions'].TRDISAMT END) AS 'Trade Discount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].FRTAMNT ELSE ['Receivables Transactions'].FRTAMNT * -1 END) AS 'Freight'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].MISCAMNT ELSE ['Receivables Transactions'].MISCAMNT * -1 END) AS 'Miscalleneous'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].TAXAMNT ELSE ['Receivables Transactions'].TAXAMNT * -1 END) AS 'Tax Amount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].DOCAMNT ELSE ['Receivables Transactions'].DOCAMNT * -1 END) AS 'Total Amount'
FROM
	(SELECT
		CUSTNMBR
		,DOCDATE
		,GLPOSTDT
		,DOCNUMBR
		,RMDTYPAL
		,SLSAMNT
		,TRDISAMT
		,FRTAMNT
		,MISCAMNT
		,TAXAMNT
		,SLSAMNT - TRDISAMT + FRTAMNT + MISCAMNT + TAXAMNT AS DOCAMNT
	FROM
		RM20101
	WHERE
		VOIDSTTS >= 0
	AND
		RMDTYPAL IN (1,3,4,7,8)
	UNION ALL
		SELECT
			CUSTNMBR
			,DOCDATE
			,GLPOSTDT
			,DOCNUMBR
			,RMDTYPAL
			,SLSAMNT
			,TRDISAMT
			,FRTAMNT
			,MISCAMNT
			,TAXAMNT
			,SLSAMNT - TRDISAMT + FRTAMNT + MISCAMNT + TAXAMNT AS DOCAMNT
		FROM
			RM30101
		WHERE
			VOIDSTTS >= 0
		AND
			RMDTYPAL IN (1,3,4,7,8)
	) AS ['Receivables Transactions']
LEFT JOIN
	RM00101 AS ['RM Debtor MSTR']
		ON
			['Receivables Transactions'].CUSTNMBR = ['RM Debtor MSTR'].CUSTNMBR
GROUP BY
	['Receivables Transactions'].CUSTNMBR
	,['RM Debtor MSTR'].CUSTNAME
	,['RM Debtor MSTR'].CUSTCLAS
	,['RM Debtor MSTR'].INACTIVE
	,FORMAT(['Receivables Transactions'].DocDate, 'yyyy')
GO
GRANT SELECT ON uv_AZRCRV_SalesByCustomerByYear TO DYNGRP
GO

The view can easily be plugged into SmartList Designer, SmartList Builder, a refreshable Excel Report, a SQL Server Reporting Services report or any other type of reporting tool.

I had an issue reported with purchase requisitions the other day. I logged on and did a little testing and then, a short wile later, got an email from the client asking about some workflow approval emails some managers had received.

The email to the approvers did not contain the word “test” anywhere in either the subject or body. This lead to me having a horrible moment when I thought I might have done my testing on live; I was able to confirm quickly that I had not.

After this I decided I had better knock a quick script together which the client could incorporate into their live to test copy.

The script is more engineered than it might otherwise need to be as I have included code to only do the update when it is running against a test company (flagged with <TEST> at the end of the Company Name) and where the email subject isn’t already prefixed with the word TEST:

/*
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).
*/
UPDATE
	['Email Messages']
SET
	EmailMessageSubject = LEFT('TEST: ' + CAST(['Email Messages'].EmailMessageSubject AS CHAR(150)), 150)
FROM
	SY04901 AS ['Email Messages']
INNER JOIN
	DYNAMICS..SY01500 AS ['Company Master']
		ON
			['Company Master'].INTERID = DB_NAME()
WHERE
	['Company Master'].CMPNYNAM LIKE '%<TEST>'
AND
	LEFT(['Email Messages'].EmailMessageSubject, 4) <> 'TEST'
GO

As always, before running a script against a database, make sure you understand the scirpt and have a good backup of the database.