SQL Scripts for Microsoft Dynamics GP: Select Workflow Comments for Work Status Batches

Microsoft Dynamics GPThis 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 will return all unposted batches and the workflow approval comments which have been entered; it was used within a customisation of workflow in Dynamics GP which allowed the returned comment to be added to the workflow approval notification emails.

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). */
WITH COMMENTS AS ( SELECT ['Posting Definitions Master'].BACHNUMB ,['Posting Definitions Master'].BCHSOURC ,CASE WHEN LEFT(CAST(['Workflow History'].Workflow_Comments AS VARCHAR(2000)), 54) = 'The workflow was automatically rejected by the system.' THEN 'SYSTEM' ELSE LTRIM(RTRIM(SUBSTRING(REPLACE(['Workflow History'].Workflow_History_User, '\', REPLICATE(' ', LEN(['Workflow History'].Workflow_History_User))), (1) * LEN(['Workflow History'].Workflow_History_User)+1, LEN(['Workflow History'].Workflow_History_User)))) END + ', ' + FORMAT(['Workflow History'].Workflow_Completion_Date, 'dd/MM/yyyy') + ' ' + FORMAT(['Workflow History'].Workflow_Completion_Time, 'T', 'en-US') + ': ' + CASE ['Workflow History'].Workflow_Action WHEN 1 THEN 'SUBMITTED' WHEN 2 THEN 'RESUBMITTED' WHEN 3 THEN 'APPROVED' WHEN 4 THEN 'TASK COMPLETED' WHEN 5 THEN 'REJECTED' WHEN 6 THEN 'DELEGATED' WHEN 7 THEN 'RECALLED' WHEN 8 THEN 'ESCALATED' WHEN 9 THEN 'EDITED' WHEN 10 THEN 'FINAL APPROVED' END + ' ' + RTRIM(['Workflow Instance Master'].WfBusObjKey) + '. ' + RTRIM(CAST(ISNULL(['Workflow History'].Workflow_Comments,'') AS VARCHAR(8000))) AS Comment ,ROW_NUMBER() OVER (PARTITION BY ['Posting Definitions Master'].BACHNUMB, ['Posting Definitions Master'].BCHSOURC ORDER BY Workflow_Completion_Date DESC,Workflow_Completion_Time DESC) AS RowNumber FROM SY00500 AS ['Posting Definitions Master'] INNER JOIN WFI10002 AS ['Workflow Instance Master'] ON ['Workflow Instance Master'].WfBusObjKey = RTRIM(['Posting Definitions Master'].BACHNUMB) + '~' + RTRIM(['Posting Definitions Master'].BCHSOURC) + '~' + FORMAT(['Posting Definitions Master'].CREATDDT, 'yyyy/M/dd') + '~' + FORMAT(['Posting Definitions Master'].TIME1, 'HH:mm:ss') LEFT JOIN WF30100 AS ['Workflow History'] ON ['Workflow History'].WorkflowInstanceID = ['Workflow Instance Master'].WorkflowInstanceID WHERE ['Workflow Instance Master'].WfBusObjKey = RTRIM(BACHNUMB) + '~' + RTRIM(BCHSOURC) + '~' + FORMAT(CREATDDT, 'yyyy/M/dd') + '~' + FORMAT(TIME1, 'HH:mm:ss') AND CAST(['Workflow History'].Workflow_Comments AS VARCHAR(2000)) <> 'No approval is required for this step.' AND CAST(['Workflow History'].Workflow_Comments AS VARCHAR(2000)) <> 'No action is required for this step.' AND CAST(['Workflow History'].Workflow_Comments AS VARCHAR(2000)) <> '' ) SELECT ['Posting Definitions Master'].BCHSOURC ,['Posting Definitions Master'].BACHNUMB ,(STUFF(( SELECT CHAR(10) + COMMENTS.Comment FROM COMMENTS WHERE COMMENTS.BACHNUMB = ['Posting Definitions Master'].BACHNUMB AND COMMENTS.BCHSOURC = ['Posting Definitions Master'].BCHSOURC ORDER BY COMMENTS.RowNumber DESC FOR XML PATH('') ), 1, 1, '') ) AS Comments ,'' AS CMMTTEXT FROM SY00500 AS ['Posting Definitions Master']

In Microsoft Dynamics 365 Business Central (Administration), how do I… Understand the FactBox

Microsoft Dynamics 365 Business CentralThis post is part of the In Microsoft Dynamics 365 Business Central (Administration), how do I… series and of the wider In Microsoft Dynamics 365 Business Central, how do I… series which I am posting as I familiarise myself with Microsoft Dynamics 365 Business Central.

On list and card pages there is a FactBox which displays at the side of the page; by default the FactBox is usually visible, but can be hidden by clicking the i circle to the top right. FactBoxes are available on the following types of page:

  • List
  • Card
  • Document
  • Worksheet

A FactBox is divided into sections and is used to display content including other pages, charts, and system parts such as Notes, and Links. Typically, a FactBox is used to display information that is related to an item on the main content page. For example, on a page that shows a vendor card, the FactBox shows a picture, some statistics and interactive tiles showing the Buy–from Vendor History.

There may also be a second tab on the FactBox; on the vendor card this is for attachments; you can see existing ones and add new ones.

Vendor card page FactBox

FactBoxes are unique to each page, but will often have a similar structure.

Continue reading “In Microsoft Dynamics 365 Business Central (Administration), how do I… Understand the FactBox”