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).
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.
Workflow_Type_Name: The ‘Document Type’ from the Message Setup window.
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:
|DDL or List Box||6|
|Check Box or Boolean||7|
After adding the record into the table, the ‘Comment Text’ field will be available to be selected in the ‘Message Setup’ window.
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.
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.
|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.
VOILA! The ‘Comment Text’ field can be seen in the email generated by the Dynamics GP workflow engine.
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.
Viswa Neelakantan, Senior Dynamics GP Consultant