Top Categories:
Dynamics BC BC
ClassicPress CP
Dynamics GP GP

In Microsoft Dynamics 365 Business Central, how do I… Series Index

Microsoft Dynamics 365 Business CentralI have worked with and blogged about Microsoft Dynamics GP for the last 19 years, but have now started doing some work with Microoft Dynamics 365 Business Central. I am going to be doing some blogging about the latter system as I learn.

Early posts will be covering the basics of Dynamics BC, but will start to cover more complex topics as I learn more about Dynamics BC.

This series will likely be a long running one which serves as a repository for “how do I” posts on various elements of Dynamics BC, both technical and functional. The series index, below, will automatically update as each post in the series goes live so make sure you bookmark this post so you can see keep up-to-date with my journey into Business Central.

Continue reading “In Microsoft Dynamics 365 Business Central, how do I… Series Index”

Microsoft Dynamics GP Workflow Approval SQL Views: Purchase Orders

Microsoft Dynamics GPThis post is part of the Microsoft Dynamics GP Workflow Approval SQL Views series and contains a SQL view to return the workflow approval status of purchase orders.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_POPOrderApprovalStatus', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_POPOrderApprovalStatus
GO
-- create view
CREATE VIEW uv_AZRCRV_POPOrderApprovalStatus AS
/*
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 POApprovals AS ( SELECT ['Workflow Instance Master'].WfBusObjKey AS PONUMBER ,['Workflow History'].Workflow_History_User ,['Workflow History'].Workflow_Completion_Date ,['Workflow History'].Workflow_Completion_Time ,['Workflow History'].Workflow_Name ,['Workflow History'].Workflow_Step_Name ,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 as Workflow_Approval_Status ,['Workflow History'].Workflow_Comments ,ROW_NUMBER() OVER(PARTITION BY ['Workflow Instance Master'].WfBusObjKey ORDER BY ['Workflow History'].Workflow_Completion_Date DESC, ['Workflow History'].Workflow_Completion_Time DESC) AS ROW_ID FROM WF30100 AS ['Workflow History'] WITH (NOLOCK) INNER JOIN WFI10002 AS ['Workflow Instance Master'] WITH (NOLOCK) ON ['Workflow Instance Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID WHERE ['Workflow Instance Master'].Workflow_Type_Name = 'Purchase Order Approval' AND ['Workflow History'].Workflow_Action <> 11 ) SELECT -- order headers ['Purchase Orders'].PONUMBER ,['Purchase Orders'].DOCDATE ,['Purchase Orders'].VENDORID -- approval information ,POApprovals.Workflow_Name ,POApprovals.Workflow_Step_Name ,POApprovals.Workflow_History_User ,POApprovals.Workflow_Approval_Status ,POApprovals.Workflow_Completion_Date ,POApprovals.Workflow_Completion_Time ,POApprovals.Workflow_Comments -- order lines ,['Purchase Order Lines'].ITEMNMBR ,['Purchase Order Lines'].ITEMDESC ,['Purchase Order Lines'].QTYORDER ,['Purchase Order Lines'].UNITCOST ,['Purchase Order Lines'].EXTDCOST ,['PM Vendor Master'].VENDNAME FROM ( SELECT PONUMBER ,DOCDATE ,BUYERID ,VENDORID FROM POP10100 AS ['Purchase Order Work'] WITH (NOLOCK) UNION ALL SELECT PONUMBER ,DOCDATE ,BUYERID ,VENDORID FROM POP30100 AS ['Purchase Order History'] WITH (NOLOCK) ) AS ['Purchase Orders'] INNER JOIN ( SELECT PONUMBER ,ITEMNMBR ,ITEMDESC ,QTYORDER ,UNITCOST ,EXTDCOST FROM POP10110 AS ['Purchase Order Lines Work'] WITH (NOLOCK) UNION ALL SELECT PONUMBER ,ITEMNMBR ,ITEMDESC ,QTYORDER ,UNITCOST ,EXTDCOST FROM POP30110 AS ['Purchase Order Lines History'] WITH (NOLOCK) ) AS ['Purchase Order Lines'] ON ['Purchase Order Lines'].PONUMBER = ['Purchase Orders'].PONUMBER LEFT JOIN POApprovals ON POApprovals.PONUMBER = ['Purchase Orders'].PONUMBER AND POApprovals.ROW_ID = 1 LEFT JOIN PM00200 AS ['PM Vendor Master'] WITH (NOLOCK) ON ['PM Vendor Master'].VENDORID = ['Purchase Orders'].VENDORID GO GRANT SELECT ON uv_AZRCRV_POPOrderApprovalStatus TO DYNGRP GO

Microsoft Dynamics GP Workflow Approval SQL Views: Purchase Requisitions

Microsoft Dynamics GPThis post is part of the Microsoft Dynamics GP Workflow Approval SQL Views series and contains a SQL view to return the workflow approval status of purchase requisitions.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_POReqApprovalStatus', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_POReqApprovalStatus
GO
-- create view
CREATE VIEW uv_AZRCRV_POReqApprovalStatus AS
/*
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 PRApprovals AS ( SELECT ['Workflow Instance Master'].WfBusObjKey AS PRRequisitionNumber ,['Workflow History'].Workflow_History_User ,['Workflow History'].Workflow_Completion_Date ,['Workflow History'].Workflow_Completion_Time ,['Workflow History'].Workflow_Name ,['Workflow History'].Workflow_Step_Name ,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 as Workflow_Approval_Status ,['Workflow History'].Workflow_Comments ,ROW_NUMBER() OVER(PARTITION BY ['Workflow Instance Master'].WfBusObjKey ORDER BY ['Workflow History'].Workflow_Completion_Date DESC, ['Workflow History'].Workflow_Completion_Time DESC) AS ROW_ID FROM WF30100 AS ['Workflow History'] WITH (NOLOCK) INNER JOIN WFI10002 AS ['Workflow Instance Master'] WITH (NOLOCK) ON ['Workflow Instance Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID WHERE ['Workflow Instance Master'].Workflow_Type_Name = 'Purchase Requisition Approval' AND ['Workflow History'].Workflow_Action <> 11 ) SELECT -- requisition headers ['Purchase Requisitions'].POPRequisitionNumber ,['Purchase Requisitions'].DOCDATE ,['Purchase Requisitions'].RequisitionDescription ,['Purchase Requisitions'].REQSTDBY -- approval information ,PRApprovals.Workflow_Name ,PRApprovals.Workflow_Step_Name ,PRApprovals.Workflow_History_User ,PRApprovals.Workflow_Approval_Status ,PRApprovals.Workflow_Completion_Date ,PRApprovals.Workflow_Completion_Time ,PRApprovals.Workflow_Comments -- requisition lines ,['Purchase Requisition Lines'].ITEMNMBR ,['Purchase Requisition Lines'].ITEMDESC ,['Purchase Requisition Lines'].QTYORDER ,['Purchase Requisition Lines'].UNITCOST ,['Purchase Requisition Lines'].EXTDCOST ,['Purchase Requisition Lines'].VENDORID ,['PM Vendor Master'].VENDNAME FROM ( SELECT POPRequisitionNumber ,DOCDATE ,RequisitionDescription ,REQSTDBY FROM POP10200 AS ['Purchase Requisition Work'] WITH (NOLOCK) UNION ALL SELECT POPRequisitionNumber ,DOCDATE ,RequisitionDescription ,REQSTDBY FROM POP30200 AS ['Purchase Requisition History'] WITH (NOLOCK) ) AS ['Purchase Requisitions'] INNER JOIN ( SELECT POPRequisitionNumber ,ITEMNMBR ,ITEMDESC ,QTYORDER ,UNITCOST ,EXTDCOST ,VENDORID FROM POP10210 AS ['Purchase Requisition Lines Work'] WITH (NOLOCK) UNION ALL SELECT POPRequisitionNumber ,ITEMNMBR ,ITEMDESC ,QTYORDER ,UNITCOST ,EXTDCOST ,VENDORID FROM POP30210 AS ['Purchase Requisition Lines History'] WITH (NOLOCK) ) AS ['Purchase Requisition Lines'] ON ['Purchase Requisition Lines'].POPRequisitionNumber = ['Purchase Requisitions'].POPRequisitionNumber LEFT JOIN PRApprovals ON PRApprovals.PRRequisitionNumber = ['Purchase Requisitions'].POPRequisitionNumber AND PRApprovals.ROW_ID = 1 LEFT JOIN PM00200 AS ['PM Vendor Master'] WITH (NOLOCK) ON ['PM Vendor Master'].VENDORID = ['Purchase Requisition Lines'].VENDORID GO GRANT SELECT ON uv_AZRCRV_POReqApprovalStatus TO DYNGRP GO

Microsoft Dynamics GP Workflow Approval SQL Views: Payables Transactions

Microsoft Dynamics GPThis post is part of the Microsoft Dynamics GP Workflow Approval SQL Views series and contains a SQL view to return the workflow approval status of payables transactions.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_PayablesTransactionApprovalStatus', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_PayablesTransactionApprovalStatus
GO
-- create view
CREATE VIEW uv_AZRCRV_PayablesTransactionApprovalStatus AS
/*
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 PMTrxApprovals AS ( SELECT ['Workflow Instance Master'].WfBusObjKey ,['Workflow History'].Workflow_History_User ,['Workflow History'].Workflow_Completion_Date ,['Workflow History'].Workflow_Completion_Time ,['Workflow History'].Workflow_Name ,['Workflow History'].Workflow_Step_Name ,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 as Workflow_Approval_Status ,['Workflow History'].Workflow_Comments ,ROW_NUMBER() OVER(PARTITION BY ['Workflow Instance Master'].WfBusObjKey ORDER BY ['Workflow History'].Workflow_Completion_Date DESC, ['Workflow History'].Workflow_Completion_Time DESC) AS ROW_ID FROM WF30100 AS ['Workflow History'] WITH (NOLOCK) INNER JOIN WFI10002 AS ['Workflow Instance Master'] WITH (NOLOCK) ON ['Workflow Instance Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID WHERE ['Workflow Instance Master'].Workflow_Type_Name = 'Payables Transaction Approval' AND ['Workflow History'].Workflow_Action <> 11 ) SELECT -- transaction information ['PM Key Master File'].CNTRLNUM AS 'PO Number' ,['Payables Document Types'].DOCTYNAM AS 'Document Type' ,['PM Key Master File'].VENDORID AS 'Vendor ID' ,['PM Vendor Master'].VENDNAME AS 'Vendor Name' ,['PM Key Master File'].DOCDATE AS 'Document Date' -- approval information ,PMTrxApprovals.Workflow_Name AS 'Workflow Name' ,PMTrxApprovals.Workflow_Step_Name AS 'Workflow Step Name' ,PMTrxApprovals.Workflow_History_User AS 'Workflow User' ,PMTrxApprovals.Workflow_Approval_Status AS 'Workflow Approval Status' ,PMTrxApprovals.Workflow_Completion_Date AS 'Workflow Completion Date' ,PMTrxApprovals.Workflow_Completion_Time AS 'Workflow Completion Time' ,PMTrxApprovals.Workflow_Comments AS 'Workflow_Comments' FROM PM00400 AS ['PM Key Master File'] WITH (NOLOCK) INNER JOIN PM00200 AS ['PM Vendor Master'] WITH (NOLOCK) ON ['PM Vendor Master'].VENDORID = ['PM Key Master File'].VENDORID INNER JOIN PM40102 AS ['Payables Document Types'] WITH (NOLOCK) ON ['Payables Document Types'].DOCTYPE = ['PM Key Master File'].DOCTYPE LEFT JOIN PMTrxApprovals ON PMTrxApprovals.WfBusObjKey = CAST(RTRIM(['PM Key Master File'].CNTRLNUM) AS VARCHAR(20)) + '~PM_Trxent' AND PMTrxApprovals.ROW_ID = 1 GO -- grant permissions GRANT SELECT ON uv_AZRCRV_PayablesTransactionApprovalStatus TO DYNGRP GO

Microsoft Dynamics GP Workflow Approval SQL Views: Series Index

Microsoft Dynamics GPA couple of years ago, I posted a SQL view which returned the status of general ledger batches.

I’ve recently been working with a few clients with workflow in Purchasing and a common request was being able to report on the status of documents.

I have three views which were created and which I will post over the next few days. If I create additional ones in future, I’ll tag them into this post.

Microsoft Dynamics GP Workflow Approval SQL Views
Show Workflow Approval Status of posted Microsoft Dynamics GP General Ledger batches
Payables Transactions
Purchase Requisitions
Purchase Orders

Git Command Line Snippets: Get Release Information

GitHubThis post is part of the series on Git Command Line Snippets where I am taking a look at performing actions on GitHub using the git command line.

In the last post, of this series, I covered the command to create a release and mentioned that uploading a release zip (extra asset) had to be handled separately. While the command to do this isn’t too complex, there is one problem; to upload the asset you need the tag d of the release. This is not the tag that you supply to create the release with, but the internal GitHub id which is automatically assigned.

The command below can be used to get the release information, in the form of json, which includes the tag id:

curl -H "Accept: application/vnd.github+json" -H "Authorization: token ghp_authorizationtoken" https://api.github.com/repos/username/repository name/releases/tags/required tag

Git Command Line Snippets: Create Release

GitHubThis post is part of the series on Git Command Line Snippets where I am taking a look at performing actions on GitHub using the git command line.

Once changes have been added, committed and pushed to the repository, we can create a release.

When developing for ClassicPress, there is also a need to upload a release zip which is used for deployment to a ClassicPress site. Unfortunately, this cannot be done while creating a release; this post is just on creating the release and a later post will cover uploading the release zip.

The following command can be used to create a release using command line:

curl -X POST -H "Accept: application/vnd.github+json" -H "Authorization: token ghp_authorizationtoken" https://api.github.com/repos/username/repository name/releases -d "{\"tag_name\":\"tagname\",\"target_commitish\":\"main\",\"name\":\"name\",\"body\":\"release comment\",\"draft\":false,\"prerelease\":false,\"generate_release_notes\":false}"

The highlighted sections need to be replaced with the parameters for your GitHub account and repo.

In Microsoft Dynamics 365 Business Central, What is a Company and What is an Environment

Microsoft Dynamics 365 Business CentralSometimes terminology can be benefit from being explained. There are two concepts for Dynamics BC which, in the short time I’ve worked with Dynamics BC, I have had to discuss with a client.

These terms are company and environment.

In simplistic terms, a company is a container which holds all information (setup, master and transactional data) for a legal entity. If we have a company called Cronus which has operations in England and Scotland, they might create two companies as there are two legal entities which operate under different legal jurisdictions.

These two companies would exist in the same environment. Environments are used to separate production systems from development or testing systems. While you can have a test company on the production system, there is a risk that emails could be sent from the test company to a live client.

To mitigate this risk, Dynamics BC allows the creation of a sandbox environment which allows for testin of new processes or features without risking the live system.

Multiple environments might also be used if you have mutiple companies operating in multiple countries as an environment is liked to a country (England and Scotland, which I mentioned earlier, are a special case as they are both part of the UK, which Dynamics BC would regard as one country).

When you sign up to Dynamics BC for a trial there is one environment and two companies automatically created; a Cronus sample company with sample data and a new one called My Company which is a blank company.

If you have more than one environment you need to specify the environment you want to access during login.

Get Serial Number Count and Length in Microsoft Dynamics GP

Microsoft Dynamics GPI needed to check how many serial numbers were assigned to sales transactions and the cumulative length of them. I’m not aware of any function in Microsoft Dynamics GP which would give me this information, so I wrote the below SQL statement to return both values along with the transaction type and document number.

It selects from the Sales Serial/Lot Work and History (SOP10201) table which, allowing for a simple statement, includes both the work and history data (SOP transactions don’t have a status of open).

/*
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). */
SELECT SOP.SOPNUMBE AS 'SOP Number' ,SOP.SOPTYPE AS 'SOP Type' ,SOP.LNITMSEQ AS 'Line Item Sequence' ,COUNT(1) AS 'Serial Number Count' ,SUM(LEN(SOP.SERLTNUM)) AS 'Serial Number Length' FROM SOP10201 AS SOP -- Sales Serial/Lot Work and History (SOP10201) GROUP BY SOP.SOPNUMBE ,SOP.SOPTYPE ,SOP.LNITMSEQ ORDER BY SUM(LEN(SOP.SERLTNUM)) DESC

Show Bookmarks in Word

Microsoft OfficeBookmarks are not shown in Microsoft Word by default; this minimises the risk of someone accidentally deleting or changing a bookmark. However, sometimes you do need to make them visible.

I had to do this recently, but really had difficulty finding the setting.

To enable the display of bookmarks, click on File and select Options.

In the Word Options window, select Advanced and scroll down to Show document content; the fifth setting down is Show bookmarks. Mark this setting and click OK:

Word options