This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.
This script was created for one client but then used for a few. It adds the required table join and the fields to allow line comment text from purchase requisitions to be included on the workflow approval notification emails. Once the script has been run you can add the fields to the email through E-mail Message Setup.
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
-- add table join
INSERT INTO
CO00122 --QueryDesigner_Relationships (CO00122)
(FieldsListGuid,Group_ID,SEQNUMBR,FromDictID,FromTable,FromTablePhysicalName,FromField,FromFieldPhysicalName,ToDictID,ToTable,ToTablePhysicalName,ToField,ToFieldPhysicalName,JoinType)
VALUES
('34635D2B-6C34-4283-87F7-2925445E196D ',5,0,0,'Purchasing Requisition Line','POP10210','','',0,'Requisition Comment','POP10550','','',3)
,('34635D2B-6C34-4283-87F7-2925445E196D ',5,1,0,'Purchasing Requisition Line','POP10210','POP Requisition Number','POPRequisitionNumber',0,'Requisition Comment','POP10550','PO Number','POPNUMBE',3)
,('34635D2B-6C34-4283-87F7-2925445E196D ',5,2,0,'Purchasing Requisition Line','POP10210','ORD','ORD',0,'Requisition Comment','POP10550','ORD','ORD',3)
GO
-- add line comment to email
INSERT INTO WF40202 --Workflow Template Fields (WF40202)
(Workflow_Type_Name,Email_Message_Type,SEQNUMBR,ORD_LINE,WF_Template_Field_Type,TableSeries,TableDictID,RSRCID,TablePhysicalName,FieldPhysicalName,FieldName,FieldDataType,TableRelationship,AdditionalFields)
VALUES
('Purchase Requisition Approval',2,64,0,18,4,0,0,'POP10550','CMMTTEXT','Line Comment Text',5,3,0)
,('Purchase Requisition Approval',3,64,0,18,4,0,0,'POP10550','CMMTTEXT','Line Comment Text',5,3,0)
GO
