Category: GP (Great Plains)

As I mentioned in the series index, on a recent Workflow project I had a few users comment to me that the text on the emails was a little too small to read. My initial reaction was that it was not possible to change the font size. However, while driving a little later, I had a thought of how I could tackle this.

In the first post of this series, I covered increasing the font-size of the email by wrapping a span test formatting tag around the text, but I found when doing this with the task assignment email which had the Document Line Fields mail merge on it, the formatting didn’t work correctly.

On this email I wrapped the span tags around all of the message body:

Continue reading “Customising Dynamics GP Workflow Emails: Increasing the Font Size in the Document Line Fields”

As I mentioned in the series index, on a recent Workflow project I had a few users comment to me that the text on the emails was a little too small to read. My initial reaction was that it was not possible to change the font size. However, while driving a little later, I had a thought of how I could tackle this.

The emails sent from Workflow are HTML emails, and HTML emails can have text formatting tags embedded within them.

This is a standard Workflow task assignment email which shows the same font size used on all of the emails sent by Workflow:

Continue reading “Customising Dynamics GP Workflow Emails: Increasing the Font Size”

As I’m sure you know by now, I do quite a lot of work with the Microsoft Dynamics GP Workflow module. On one of the recent projects I worked on, it was commented upon by a few users that the font size on the email was a little too small. My initial reponse was that it isn’t possible to change the font size. But, when I was on a loing drive a couple of days later, it occurred to me that it might be possible.

In this small series of posts, I am going to show how the font size on the emails can be changed, and the different ways this can be done, depending on the result you want.

Following on from my recent script to return the functional currency for all companies I hve revisited an old script which gets the size of database and updated it to work in the same way.

This script needs to be run against your system database (called DYNAMICS by default) and it will return the size of the data and log files alongside the total size of the database for all companies.

/*
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 @SQL NVARCHAR(MAX)

CREATE TABLE #DatabaseSizes(
	INTERID VARCHAR(5)
	,CMPNYNAM VARCHAR(65)
	,DataSize VARCHAR(20)
	,LogSize VARCHAR(20)
	,TotalSize VARCHAR(20)
)

SELECT
	@SQL = STUFF((
			SELECT 
				CHAR(13) + '
				SELECT
					''' + INTERID + '''
					,''' + CMPNYNAM + '''
					,LTRIM(STR(CONVERT(DEC (15,2),SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 = 0 THEN SIZE ELSE 0 END))) * 8192 / 1048576,15,2) + '' MB'')
					,LTRIM(STR(CONVERT(DEC (15,2),SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 <> 0 THEN SIZE ELSE 0 END))) * 8192 / 1048576,15,2) + '' MB'')
					,LTRIM(STR(CONVERT(DEC (15,2),SUM(CONVERT(BIGINT,SIZE))) * 8192 / 1048576,15,2) + '' MB'')
				FROM
					' + INTERID + '.dbo.sysfiles'
			FROM
				SY01500
			ORDER BY
				CMPNYNAM
			FOR
				XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

INSERT INTO #DatabaseSizes
	EXEC sys.sp_executesql @SQL
GO

SELECT
	*
FROM
	#DatabaseSizes
GO

DROP TABLE #DatabaseSizes
GO

I am working with a client at the moment on a large scale roll-out of Purchase Order Processing with a large, complex Workflow approval process. The project started towards the end of the last financial year and into the current one. While users were performing UAT, an issue suddenly arose where a goods receipt notes could not be entered in Receivings Transaction Entry (test). When the user tried, they received the following error:

Continue reading ““You cannot receive against unauthorised purchase orders.””

I have a few clients who have quite a few company databases in Microsoft Dynamics GP. One of them has well over a hundred live companies. This can make deploying reports somewhat long winded when you need to deploy an SQL view to all of the databases.

Fortunately, Microsoft SQL Server has ways and means which you can use to make the process a lot easier. In this case, I am using a SQL cursor to select all of the databases from the Company Master (SY01500) and loop through them to deploy the view; the deployment is in three phases:

Delete any existing view with the same name (this allows for an easy redeployment).

Create the view.

Grant the SELECT permission to DYNGRP.

The script is posted below with a simplified PO report being created; the view name is set in the highlighted parameter near the top of the script.

The large highlighted section is where you please the content of the view which is to be deployed.
Continue reading “Deploy SQL View to All Databases”

The budget functionality in Microsoft Dynamics GP isn’t the strongest with reporting being particularly weak. The ability to report on budgets in Management Reporter does somewhat redeem this area of functionality.

However, the absence of a SmartList Object for budgets is quite a big issue, as SmartList is a very nice flexible reporting tool which the majority of my clients know well. For those with SmartList Builder, it was easy enough to create a SmartList Object for them.

With the introduction of SmartList Designer, we were able to roll out the SmartList budget report to all of the clients who wanted it.

The script is below and returns the budget information with the beginning balance, 12 hard-coded periods and total horizontally across the page.

Continue reading “SQL View to Return Budgets By Month”