Dynamics Community Forum Thread Details

Purchase Requisition Email Configuration

Has anyone had any success with adding Vendor Name as a Document Line Field when setting up the email message configuration?

Thank you!

Hi,

First, you should make a BACKUP the company Database. Then try these steps

1) You should add vendor name column using alter query in POP10210 table.

ALTER TABLE POP10210 ADD VENDNAME CHAR(65)

2) Then you should create a insert trigger between two tables( POP10210 and PM00200)

CREATE TRIGGER [DBO].[POP10210INSERT] ON [DBO].[POP10210]
AFTER INSERT
AS
BEGIN

UPDATE POP10210 SET POP10210.VENDNAME = PM00200.VENDNAME
FROM POP10210
JOIN INSERTED ON POP10210.POPREQUISITIONNUMBER= INSERTED.POPREQUISITIONNUMBER
JOIN PM00200 ON POP10210.VENDORID = PM10000.VENDORID
WHERE PM00200.VENDORID = POP10210.VENDORID AND POP10210.POPREQUISITIONNUMBER= INSERTED.POPREQUISITIONNUMBER
END

3) Then you should run this query for adding VendorName into WF40202 for Document Line Field

DECLARE @SEQNUMBR INT;

SELECT @SEQNUMBR = MAX(SEQNUMBR) + 1 FROM WF40202 WHERE WORKFLOW_TYPE_NAME = 'PURCHASE REQUISITION APPROVAL ';

INSERT INTO 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, @SEQNUMBR , 0, 18, 4, 0, 0, 'POP10210', 'VENDNAME', 'VENDORNAME', 5, 4, 0);

INSERT INTO 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 ', 3, @SEQNUMBR , 0, 18, 4, 0, 0, 'POP10210', 'VENDNAME', 'VENDORNAME', 5, 4, 0);

Thaks,

Sing.

Quick Links

Leaderboard