Back in April 2017 I posted an SQL function which can be used to return the workflow status of a transaction or card. This function is now complimented by another one which can be used to return the last comment recorded. This was created for use on a customisation of the Purchase Requisition Entry window which added a Rejection Reason field so users could see at a glance why a purchase requisition had been rejected.
IF OBJECT_ID (N'uf_AZRCRV_GetWorkflowApprovalComments', N'FN') IS NOT NULL DROP FUNCTION uf_AZRCRV_GetWorkflowApprovalComments GO CREATE FUNCTION dbo.uf_AZRCRV_GetWorkflowApprovalComments(@WorkflowTypeName CHAR(50),@WfBusObjKey CHAR(20)) RETURNS VARCHAR(14) AS /* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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 WF30100 AS ['Workflow History'] 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_GetWorkflowApprovalComments TO DYNGRP GO
This function can easily be included in a view or other query used in a variety of reporting tools.