If you have been paying any sort of attention, you will know that I do quite a lot of work with the Workflow 2.0 module of Microsoft Dynamics GP.
We have created a number of reports in the past which allows the status of transactions or card going through a Workflow Approval process; to simplify matters somewhat, I created a SQL function which can be called with the Workflow Type Name and the Workflow Business Object Key and have the approval status returned.
CREATE FUNCTION dbo.uf_AZRCRV_GetWorkflowApprovalStatus(@WorkflowTypeName CHAR(50),@WfBusObjKey CHAR(20)) RETURNS VARCHAR(14) AS /* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). Returns Workflow Approval status of a specified workflow item. Requires input parameters of WorkflowTypeName and WfBusObjKey Valid Workflow Type Names are (as of Microsoft Dynamics GP 2016 R2): General Ledger Batch Approval Receivables Batch Approval Payables Batch Approval Payables Transaction Approval Purchase Order Approval Purchase Requisition Approval Vendor Approval Employee Profile Approval Employee Skills Approval Payroll Direct Deposit Approval Payroll Timecard Approval Payroll W4 Approval Expense Report Approval Timesheet Approval Smartlist Designer View Approval */ BEGIN RETURN ISNULL(( SELECT TOP 1 CASE WHEN ['Workflow History'].Workflow_Action = 1 THEN 'Submitted' WHEN ['Workflow History'].Workflow_Action = 2 THEN 'Resubmitted' WHEN ['Workflow History'].Workflow_Action = 3 THEN 'Approved' WHEN ['Workflow History'].Workflow_Action = 4 THEN 'Task Complete' WHEN ['Workflow History'].Workflow_Action = 5 THEN 'Rejected' WHEN ['Workflow History'].Workflow_Action = 6 THEN 'Delegated' WHEN ['Workflow History'].Workflow_Action = 7 THEN 'Recalled' WHEN ['Workflow History'].Workflow_Action = 8 THEN 'Escalated' WHEN ['Workflow History'].Workflow_Action = 9 THEN 'Edit' WHEN ['Workflow History'].Workflow_Action = 10 THEN 'Final Approved' END FROM WF30100 AS ['Workflow History'] INNER JOIN WFI10002 AS ['Workflow Master'] ON ['Workflow Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID WHERE ['Workflow Master'].Workflow_Type_Name = @WorkflowTypeName AND ['Workflow Master'].WfBusObjKey = @WfBusObjKey ORDER BY ['Workflow History'].DEX_ROW_ID DESC) ,'Not Submitted') END GO GRANT EXECUTE ON uf_AZRCRV_GetWorkflowApprovalStatus TO DYNGRP GO
This view can be called from a SmartList created using either SmartList Designer, SmartList Builder, via a Reporting Services or Excel Report or any other type of report.
Microsoft Dynamics GP 2015 R2 will soon be released (due late May/early June and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.
The thirteenth Feature of the Day is SmartList Designer Create View .
Within SmartList Designer a user can send a SmartList through workflow to create a SQL view based on the SmartList query.
This SQL view can then be used outside of GP for such purposes as creating reports using SQL reporting services or Power BI reports in Excel.
The view will also allow the user to publish the SmartList as a refreshable excel report from inside of Dynamics GP.