We've had a few questions on how to setup or find information in regard to Workflow functionality in Microsoft Dynamics GP in order to put together various reports holding information such as the originator of the document or batch, approvers, final approvers and like information. In this blog, we're going to go through each table, what information it holds, and what information is written to the tables along the workflow process.
The Workflow tables are as follows:
WF00100 - Workflow Setup
WF00104 - Workflow User Security
WF100001 - Workflow Type Table
WF100002 - Workflow Master
WF100003 - Workflow Step Table
WF30100 - Workflow History
WF40200 - Workflow Users
WF40201 - Workflow Roles
WF40202 - Workflow Template Fields
WF40300 - Workflow Calendar
WF40310 - Workflow Calendar Down Days
WF40400 - Workflow Email Notifications
WF40500 - Workflow User Delegation Master
WF40510 - Workflow User Delegation Line
WFI10002 - Workflow Instance Master
WFI10003 - Workflow Step Instance Table
WFI10004 - Workflow Tasks Table
WFI10005 - Workflow Step Instance Users
By default, using Dynamics GP as an example, before Workflow is setup at all, only these tables will have any records in them:
-- WF100001 = One record for each of the 15 possible types of Workflow that are available. (This could be different depending on what features and version of Dynamics GP you are on.)
--WF40201 = Each of the 3 to 5 roles that are available for each of the 15 Workflow types that are available, including Workflow originator, Workflow Approver, Alternate final approver, Requested by, and other roles specific to the workflow types, such as Employee.
--WF40202 = Each field that is available for each of the Workflow email notifications that are available for the 15 different types of workflows.
After setting up SMTP email information and Web Services information in the Workflow Setup window, the following tables get populated:
--WF00100 = A record is written to include the Web Services and SMTP email setup information from the Workflow Setup window.
After saving a new workflow, for example, the Purchase Order Approval workflow, with a name and description:
--WF00104 = 1 record holding the Purchase Order Approval workflow name and user account that saved the changes, such as 'sa'.
--WF100002 = 1 record holding the Purchase Order Approval workflow name and setup information such as whether it is active or not. Settings are specified as either 0 (not setup) or 1 (setup).
--WF40300 = One record for each of the 7 week days, indicating whether they are a work day or not, as setup in the Workflow Calendar by default.
After setting up and saving a new workflow step for the Purchase Order Approval workflow:
--WF100003 = One record with the workflow step's name, description, approver GUID for whom the step is assigning to for approval and other setup information.
--WF40200 = One record for the approver's GUID, name, display name, alias, domain, login and other information related to this Active Directory approver account.
After adding a manager to the Purchase Order Approval workflow type and saving changes:
--WF40200 = Another record gets added with the manager's GUID, name, display name, alias, domain, login and other information for this Active Directory manager account.
After activating the Purchase Order Approval workflow type and saving changes:
--WF100002 = record for the Purchase Order Approval workflow type gets updated to show the workflow is active for this company database.
After submitting a purchase order for approval through Workflow and verifying the PO shows as pending approval from the approver we setup:
--WF30100 = One record is created for the PO being submitted for approval (Workflow_Action = 1), who submitted the PO, the name of the workflow and the step, as well as due date the approver has to approve by.
--WFI10002 =One record is created for the workflow, the purchase order number submitted for approval, Workflow_Status (4=Pending User Action), the originator of the PO, the corresponding table (POP10100 in this case) and Document Drill Down information.
--WFI10003 = One record with the name of the workflow and step again, the Workflow_Step_Status value (2 = Pending User Action) and the Workflow_Step_Assigned_To GUID, indicating the user from WF40200, that the PO was assigned to for approval.
--WFI10004 =This record also holds the workflow name and step name, as well as who the workflow task was assigned to, and the due date for when the approver has to approve it.
Lastly, I final approve the purchase order so it is 'completed' and not pending any further approval information:
--WF30100 = Another record gets written, this time with the Workflow_Action value of 10 (final approved) and the name of the approver in the Workflow_History_User column.
--WFI10002 = The one record gets updated to show a Workflow_Status of 6, meaning 'Completed'.
--WFI10003 = The one record gets updated to have a Workflow_Step_Status = 4, meaning 'Completed'.
The Workflow_Action values for the WF30100 table are:
1- Submit
2- Resubmit
3- Approve
4- Task Complete
5- Reject
6- Delegate
7- Recall
8- Escalate
9- Edit
10- Final Approve
The Workflow_Status values for the WFI10002 table are:
1- Not Submitted / Saved
2- Submitted
3- No Action Needed
4- Pending User Action
5- Recalled
6- Completed
7- Rejected
8- Workflow Ended
9- Not Activated
10- Deactivated
The Workflow_Step_Status values for the WFI10003 table are:
1- No Action Needed
2- Pending User Action
3- Rejected
4- Completed
5- Recalled
6- Failed
As you can see, all of the information for Workflow is available, it is just found in multiple tables that you would need to link together to create, for example, a SQL View, that could then be used in a custom report pulling this type of information.
For example, here is a quick example script:
SELECT a.Workflow_Name, a.WorkflowInstanceID, a.WorkflowTaskAssignedTo, b.Workflow_Step_Status, b.Workflow_Step_Assign_To, c.Workflow_Status, c.Workflow_Originator
FROM <companydb>.dbo.WFI10004 a
JOIN
<companydb>.dbo.WFI10003 b
ON a.WorkflowInstanceID=b.WorkflowInstanceID
JOIN
<companydb>.dbo.WFI10002 c
ON a.WorkflowInstanceID=c.WorkflowInstanceID
This script will give the workflow name, who the task is assigned to, the workflow step and workflow status, as well as the workflow originator. Using this example, you could potentially filter it to pull in the specific information you want/need for a custom report pulling information in regards to Workflow activity.
Hopefully this blog will help give information as to where to find specific Workflow information that can be used in troubleshooting issues, as well as what tables can be used to find information to use for reporting purposes as needed, to go along with the navigation list reports that are already in Microsoft Dynamics GP.
Other workflow articles of interest:
Workflow history report is pretty nice, and many customers use it for auditors, and it seems to suffice, was a large request of why we did this feature in the first place.
(+) Microsoft Dynamics GP 2018: Reporting for Workflow, I need that!! - Microsoft Dynamics GP Community
If you import with eConnect for vendors, Microsoft Idea (dynamics.com)
Additional information to help with auditor reporting and workflow Help! My Auditor wants a report for Workflow approval information in Microsoft Dynamics GP
Thanks
Terry Heley
Microsoft
