SQL Script to Prefix Email Message Subjects with Test

Microsoft Dynamics GPI 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 (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).
*/
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.

Customising Dynamics GP Workflow Emails: Adding An Image

Microsoft Dynamics GPI 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:

Message Setup

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

Dynamics GP Tech Conference 2017: New Features In Workflow

Microsoft Dynamics GPThis post is part of the series I am doing on the Dynamics GP Tech Conference 2017.

There have been new features in other areas, which I will come back to, but Workflow is sort of “my thing”, so I’m going to mention the new features which are being added to Workflow 4.0 (I apparently missed the boat somewhere on the name change to Workflow 3.0?).

The coming new features are:

  • Reminder emails for Workflow – this is a feature which has been requested of ym by a couple of clients recently, so nice that I’ll be able to go back and tell them it will be in the next version.
  • Copy Workflow Step – nice to see this one added as it was one of my suggestions on MS Connect.
  • Reporting for Workflow – I’m not sure what is coming in this, but it would be nice to have some default reports showing the history of documents as they passed through the Workflow.
  • Payables Transaction Workflow – additional fields
  • PO Workflow Messages – additional fields – there are never enough fields available for the Workflow, so nice to know more will be available by default.
  • Add EFT/Bank Details to Vendor Approval Workflow – if this one is as it sounds, that will be fantastic as so many clients want this functionality. I think it only took so long as EFT isn’t anywhere near as big in the US as it is in the UK.
  • GL Account Workflow Approval – I have a couple of clients who can ditch some customisations with this one. And a few more who will be delighted so see this added.
  • Purchase Receiving Workflow – I have a few customers waiting for this one to be introduced.
  • Purchase Enter/Match Invoice Workflow – as above, a few clients are waiting for this one.

I know we can’t have everything we want, and the above are good additions to Workflow, but I still want more!

Click to show/hide the Dynamics GP Tech Conference 2017 Series Index

Customising Dynamics GP Workflow Emails: Not Just Workflow Emails

Microsoft Dynamics GPOne point I forgot to make when writing the other posts of this series, which was perhaps obvious to all, is that the formatting I have shown in this series, applies not only to the workflow emails, but also to the standard emails sent by Microsoft Dynamics GP. This would include the emails sent to vendors with a PO or remittance or to a customer with an invoice or statement.

While I haven’t gone much beyond what I have covered in this series, it should be possible to do a lot more in formatting terms to the emails. One thing I have thought of, but not tried yet, is wrapping a table around the fields at the top of the table to align them into a neat layout.

A couple of things I have tried, and which did not work, are:

  1. Adding styles to the approval links – any style I added to these links, such as padding or line-height, was ignored.
  2. Adding a div around an image and setting float: left – the image did not float in Outlook.

The final point I would make around formatting the emails, is that just because you can, doesn’t mean you should. If you do format the emails, keep it simple.

Click to show/hide the Customising Dynamics GP Workflow Emails Series Index

Customising Dynamics GP Workflow Emails: Creating a Hyperlink

Microsoft Dynamics GPI’ve been showing how to format the Microsoft Dynamics GP Workflow notification emails in this series. I’ve covered some formatting to change the font size or weight in the previous posts, but I want to show how you can apply other html tags to a Workflow email.

The example below shows how we could contruct a hyperlink to a web page using the Vendor ID field to make the link unique:

Message Setup

Continue reading “Customising Dynamics GP Workflow Emails: Creating a Hyperlink”

Customising Dynamics GP Workflow Emails: Extending Styles to Other Elements

Microsoft Dynamics GPIn this series of posts, I am showing how the email notifications sent by Microsoft Dynamics GP Workflow can be formatted using HTML text formatting tags. In this post, I’m going to show how we can appyl formats to mutliple elements within the email by building on the embedded stylesheets I covered in the last post.

In Message Setup (Administration area page >> Setup >> Company >> Workflow >> E-mail Message Setup), I added a new span class of title to the stylesheet at the top of the message body (this time setting the font-weight to 900, which is a bold font), and then wrapped all of the field headers in span tags with a class of title:

Message Setup

Continue reading “Customising Dynamics GP Workflow Emails: Extending Styles to Other Elements”

Customising Dynamics GP Workflow Emails: Applying an Embedded Stylesheet

Microsoft Dynamics GPIn this series of posts, I’m taking a look at the formatting of emails sent by the Microsoft Dynamics GP Workflow module. In the first post I showed how the font-size could be changed on the email, and in the second post how an embedded style sheet can be used to format the document line fields.

It is best practice not to use different methods of applying styles within a single page, or in this case email. So with both normal fields and the document line fields needing to be formatted, I revisited the email message and changed the original span tags to use a class and added the format of this to the embedded stylesheet:

Message Setup

Continue reading “Customising Dynamics GP Workflow Emails: Applying an Embedded Stylesheet”

Customising Dynamics GP Workflow Emails: Increasing the Font Size in the Document Line Fields

Microsoft Dynamics GPAs 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:

Message Setup

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

Customising Dynamics GP Workflow Emails: Increasing the Font Size

Microsoft Dynamics GPAs 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:

Workflow task assignment email

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

SQL View For Vendor Email Addresses

Microsoft Dynamics GPI produced this view to return vendor email address for a client a while ago; I don’t typically use this view by itself, but instead combine it with GP tables to produce a larger SmartList report using either SmartList Designer or SmartList Builder.


CREATE VIEW uv_AZRCRV_VendorInternetAddresses
AS
SELECT
	['Internet Addresses'].Master_ID AS 'Vendor ID'
	,['Internet Addresses'].ADRSCODE AS 'Address Code'
	,['Internet Addresses'].EmailToAddress AS 'Email To Address'
	,['Internet Addresses'].EmailCcAddress AS 'Email Cc Address'
	,['Internet Addresses'].EmailBccAddress AS 'Email Bcc Address'
	,['Internet Addresses'].INET1 AS 'Email'
FROM
	SY01200 AS ['Internet Addresses']
WHERE
	['Internet Addresses'].Master_Type = 'VEN'
GO

GRANT SELECT ON uv_AZRCRV_VendorInternetAddresses TO DYNGRP
GO