Category: Dynamics

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.

There are a number of reports which I keep getting asked for. One of them is a list of the top X number of creditors (or vendors to my American readers).

Rather than keep on reinventing the wheel, I have created a SQL view which sums up the transactions in each year for all vendors:

/*
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_PurchasesByVendorByYear', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_PurchasesByVendorByYear
GO
CREATE VIEW uv_AZRCRV_PurchasesByVendorByYear AS
SELECT
	['Payables Transactions'].VENDORID AS 'Vendor ID'
	,['PM Vendor Master'].VENDNAME AS 'Vendor Name'
	,['PM Vendor Master'].VNDCLSID AS 'Class ID'
	,FORMAT(['Payables Transactions'].DOCDATE, 'yyyy') AS 'Year'
	,CASE WHEN ['PM Vendor Master'].VENDSTTS >= 1 THEN
		'Active'
	WHEN ['PM Vendor Master'].VENDSTTS >= 2 THEN
		'Inctive'
	ELSE
		'Temporary'
	END AS 'Status'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].PRCHAMNT ELSE ['Payables Transactions'].PRCHAMNT * -1 END) AS 'Purchases Amount'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].TRDISAMT * -1 ELSE ['Payables Transactions'].TRDISAMT END) AS 'Trade Discount'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].FRTAMNT ELSE ['Payables Transactions'].FRTAMNT * -1 END) AS 'Freight'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].MSCCHAMT ELSE ['Payables Transactions'].MSCCHAMT * -1 END) AS 'Miscalleneous'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].TAXAMNT ELSE ['Payables Transactions'].TAXAMNT * -1 END) AS 'Tax Amount'
	,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].DOCAMNT ELSE ['Payables Transactions'].DOCAMNT * -1 END) AS 'Total Amount'
FROM
	(SELECT
		VENDORID
		,DOCDATE
		,PSTGDATE
		,DOCNUMBR
		,DOCTYPE
		,PRCHAMNT
		,TRDISAMT
		,FRTAMNT
		,MSCCHAMT
		,TAXAMNT
		,DOCAMNT
	FROM
		PM20000
	WHERE
		VOIDED >= 0
	AND
		DOCTYPE <= 5
	UNION ALL
		SELECT
			VENDORID
			,DOCDATE
			,PSTGDATE
			,DOCNUMBR
			,DOCTYPE
			,PRCHAMNT
			,TRDISAMT
			,FRTAMNT
			,MSCCHAMT
			,TAXAMNT
			,DOCAMNT
		FROM
			PM30200
		WHERE
			VOIDED >= 0
		AND
			DOCTYPE <= 5
	) AS ['Payables Transactions']
LEFT JOIN
	PM00200 AS ['PM Vendor Master']
		ON
			['Payables Transactions'].VENDORID = ['PM Vendor Master'].VENDORID
GROUP BY
	['Payables Transactions'].VENDORID
	,['PM Vendor Master'].VENDNAME
	,['PM Vendor Master'].VNDCLSID
	,['PM Vendor Master'].VENDSTTS
	,FORMAT(['Payables Transactions'].DocDate, 'yyyy')
GO
GRANT SELECT ON uv_AZRCRV_PurchasesByVendorByYear 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 made a discovery the other day about the Copy Workflow functionality in Workflow Maintenance (Administration area page >> Setup >> Company >> Workflow >> Workflow Maintenance). I have been working with a client to create a new Purchase Order Processing workflow. After the client did UAT on a test system, we needed to transfer the workflow to the live system.

We did this by copying the Workflow from the test company to the live one using the Coopy functionality. After the copy, users did a little testing on live and found that originators were not receiving notifications, but users were receiving the task assignment emails.

This was not the first email issues we had encountered during the project, but turned out to be the simplest to resolve (although it took longer than it should to identify).

To correct the issue, open the Workflow and click the expansion arrow next to the Send notifications for completed actions checkbox and remark the checkboxes for the notifications which should be sent:

I thought I had posted about this a littler while ago as part of my Customising Dynamics GP Workflow Emails series, but it seems I missed it. The reason it came back to mind, was when I was onsite with a client doing some training on Workflow, they asked if it was possible to add a company logo to a Workflow task assignment email.

It is. In fact, you can add any image you want to a workflow email. In this example, though, I am going to stick with using a company logo.

In the Message Setup (Administration area page >> Setup >> Company >> Workflow >> Message Setup) window load the message you want to amend and simply add the HTML image tag:

Continue reading “Customising Dynamics GP Workflow Emails: Adding An Image”