I’ve heard from a couple customers who would like to include amounts from originating currencies on the notification e-mail messages coming from the Workflow module. Since those fields are not exposed by default, I wanted to share an example showing how to add them for the Purchase Order Approval document type.
The fields in the message Setup table are held in the Workflow Template Fields (WF40202) table in each company database. Here’s a quick description of the columns in that table:
Workflow_Type_Name – The Document Type. Corresponds to that field on the Message Setup window
Email_Message_Type – 1 = Standard, 2 = Workflow Assignment, 3 = Workflow Action Completed
SEQNUMBR – A unique number for each combination of Workflow_Type_Name and Email_Message_Type
Ord_Line – This has a value of 0 for all defaults
WF_Template_Field_Type – 18 = Normal Field, 19 = a collection of fields (e.g. Document Line Fields)
TableSeries – 1 = Financial, 2 = Sales, 4 = Purchasing, 6 = Payroll, 7 = Project Accounting, 8 = Company, 16 = SmartList
TableDictID – Dictionary ID of the table
RSRCID – Resource ID
TablePhysicalName – Physical name of the table where the field exists
FieldPhysicalName – Physical name of the field
FieldName – Display name you want for the field in the Message Setup table
FieldDataType – 1 = Integer, 2 = Long Integer, 3 = Date, 4 = Currency, 5 = String, 6 = Small Integer, 7 = Boolean, 10 = Domain\Alias, 12 = Account Index
TableRelationship – 1 = Primary Table, 2 = One to One, 3 = One to Many, 4 = Many to Many
So, if you want to make Originating Unit and Extended Cost available for the Purchase Order Approval message you would insert the following two lines:
NOTE: Before making any change to a table in a production database you will want to at least be sure you have a good backup. I’d also recommend that you run through this in a test or sample database before implementing in production.
Insert into WF40202 values ('Purchase Order Approval',2,78,0,18,4,0,'0','POP10110','ORUNTCST','Orig Unit Cost',4,3,0)
Insert into WF40202 values ('Purchase Order Approval',2,79,0,18,4,0,'0','POP10110','OREXTCST','Orig Extended Cost',4,3,0)
The only thing I had to do was find the highest SEQNUMBR for that document type by running the following query against that same company database:
SELECT Workflow_Type_Name, SEQNUMBR FROM WF40202
WHERE Workflow_Type_Name = 'Purchase Order Approval'
AND Email_Message_Type = 2
ORDER BY SEQNUMBR
This could potentially be different values in each environment, so be sure you check this and adjust your INSERT statement as necessary,
Then I told it to pull the ORUNTCST and OREXTCST fields from the POP10110 and gave them unique names.
Now, when I go to the Message Setup window, pull up the WF ASSIGN PO APPROVAL* Message ID and look at the Available Fields under the Document Lines tab I see these two new fields:
You then just need to add the Document Line Fields field to the body of your Message ID:
Hopefully this helps add some flexibility to your workflow notification e-mails in order to meet your specific needs.
Lucas, Were you able to add those fields because the POP10110 was already used by the PO Workflow module?
Would you be able to add other tables and fields to the Workflow Template Fields (WF40202) email using this same technique? What are the possibilities and limitations?
Mike, I haven't really been asked to look into linking new tables, so I took a quick look. I believe it would involve setting up a new table link in the CO00122 table, then adding the necessary field(s) from that newly joined table into the WF40202.
I have setup a Payables Transaction workflow.
It works as expected. email notifications are going to approvers.
However, this company uses Multicurrency and the out of the box field in the workflow for document amount is in the functional currency which doesn't help the approver as they need to see the amount in the source currency.
I have used your instructions above to add fields to the WF40202 field to bring in the Currency ID and the originating document amount from the MC020103 table.
The notification email prints the currency id but no dollar amount appears.
I then tried adding a relationship in the CO00122 table between the PM10000 table and the MC020103 table using the VCHNUMWK field in the PM10000 table and the VCHRNMBR field in the MC020103.
When I submit the workflow I get a multipart identifier cannot be bound error on MC020103.VCHRNMBR, The voucher number is the only thing that I believe can be used to join the records. This should also be a one to one relationship.
Any thoughts on how to make this work?