How to Customize Microsoft Dynamics GP Workflow Emails

In this blog post I will demonstrate how to add additional fields and customize email messages generated by the workflow engine in Microsoft Dynamics GP Workflow 2.0. Specifically, I’ll cover how to add the ‘Comment Text’ field for each Purchase Order line to the workflow emails generated for Purchase Order approvals.

Important: please make sure to back up the Dynamics GP Company and System databases before making any changes.

Out of the box, the ‘Comment Text’ field is not available in the ‘Available Fields’ list shown under the ‘Document Lines’ tab in the ‘Message Setup’ window (Tools -> Setup -> Company -> Workflow -> E-mail Message Setup).

Dynamics GP Workflow Email Message Setup

In order to add the ‘Comment Text’ field to the email message we need to 1) enable the field in the ‘Available Fields’ list in the ‘Message Setup’ window, and 2) modify the workflow process that generates the email message.

The ‘Message Setup’ window queries the table ‘Workflow Template Fields’ (WF40202) to add the fields to the ‘Available Fields’ list. In order to add the ‘Comment Text’ field to the list we need to add any entry for the ‘Comment Text’ to the ‘WF40202’ table.

Configure Dynamics GP Workflow Emails

 

Workflow_Type_Name: The ‘Document Type’ from the Message Setup window.

Email_Message_Type:

Message Type Value
Standard 1
Workflow Assignment 2
Workflow Action 3

 

SEQNUMBR: A long integer value that indicates the order of the fields in the group. The first field in the group must have the value 1. The sequence number is incremented for each additional field.

TableSeries: 4 (Purchasing Series)

TableDictID: The ID of the dictionary that contains the table from which the fields are being accessed

TablePhysicalName: The physical name of the table that contains the field value being accessed

FieldPhysicalName: The physical name of the field being accessed

FieldDataType: An integer value that specifies the data type of the field being accessed. Use one of the following values:

Data Type Value
Integer 1
Long Integer 2
Date 3
Currency 4
String 5
DDL or List Box 6
Check Box or Boolean 7
Time 8

 

After adding the record into the table, the ‘Comment Text’ field will be available to be selected in the ‘Message Setup’ window.

Edit Dynamics GP Workflow Emails

Next, we need to modify the process that generates the email. The workflow engine in Dynamics GP dynamically generates the email message based on the fields selected in the ‘Message ID’. It queries the field values for the selected fields by building a query based on the relationship defined in the ‘QueryDesigner_Relationships’ (CO00122) table. In order to have the ‘Comment Text’ value retrieved, we have to define a relationship for the POP10550 table in the ‘QueryDesigner_Relationships’ (CO00122) table.

Because the ‘Comment Text’ field values are stored in a separate table, at least two records must be added to the ‘QueryDesigner_Relationships’ (CO00122) table to define the relationship for an additional table. The first record indicates that a relationship exists, and each additional record provides information on how the additional table is related. The below query defines the relationship for ‘POP10550’ with ‘POP10110’ in the QueryDesigner_Relationships’ (CO00122) table.

Modify Email Body Text in Dynamics GP Workflow

FieldsListGuid: The string GUID value that identifies the set of fields. It is the FieldsListGuid column value from the ‘WF100001’ table for the Workflow Type Name.

JoinType:

Value Type
1 Inner Join
2 Cross Join
3 Left Outer Join
4 Full Outer Join

 

All the other fields are self-explanatory. For more information please refer the document ‘IG.pdf’ in the Dexterity manuals.

Now, to test the workflow customization we create a Purchase Order containing a message in the ‘Comment Text’ field and submit for approval.

Dynamics GP Workflow Purchase Order Entry

Dynamics GP Workflow Purchasing Item Detail Entry

VOILA! The ‘Comment Text’ field can be seen in the email generated by the Dynamics GP workflow engine.

Purchase Order Workflow Dynamics GP

Happy Customizing…

Important: please make sure to back up the Company and System databases before making any changes.

For more information or for assistance with Dynamics GP please do not hesitate to contact FMT Consultants by filling out the form below.

 

Written by:
Viswa Neelakantan, Senior Dynamics GP Consultant
FMT Consultants