Incorrect Microsoft Dynamics GP Purchase Orders Workflow Status

Microsoft Dynamics GPI recently had an error reported to me in Microsoft Dynamics GP where some final approved purchase orders were not working correctly when opened in the Purchase Order Entry window (Purchasing area page » Transactions » Purchase Order Entry).

The PO is shown as not submitted in the workflow status bar, but the button on the tool is a Recall button rather than a Submit one showing that the document has been submitted:

PO Entry window showing workflow status bar and action pane

Continue reading “Incorrect Microsoft Dynamics GP Purchase Orders Workflow Status”

SQL View to return most recent five purchase orders by vendor

Microsoft Dynamics GPI don’t recall when I wrote this SQL view, but I stumbled across it when I was looking for something else and thought it worth posting.

It uses a common table expression to return the most recent five purchase orders for each vendor.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_MostRecentFivePurchaseOrdersByVendor ', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_MostRecentFivePurchaseOrdersByVendor
GO
-- create view
CREATE VIEW uv_AZRCRV_MostRecentFivePurchaseOrdersByVendor 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). */
WITH PurchaseOrders ( PONUMBER ,DOCDATE ,VENDORID ,SUBTOTAL ,TAXAMNT ,POSTATUS ,RowNumber ) AS ( SELECT PONUMBER ,DOCDATE ,VENDORID ,SUBTOTAL ,TAXAMNT ,POSTATUS ,ROW_NUMBER() OVER (PARTITION BY VENDORID ORDER BY DOCDATE DESC) AS RowNumber FROM ( SELECT PONUMBER ,DOCDATE ,VENDORID ,SUBTOTAL ,TAXAMNT ,POSTATUS FROM POP10100 UNION ALL SELECT PONUMBER ,DOCDATE ,VENDORID ,SUBTOTAL ,TAXAMNT ,POSTATUS FROM POP30100 ) AS PurchaseOrders ) SELECT PONUMBER ,DOCDATE ,VENDORID ,SUBTOTAL ,TAXAMNT ,CASE POSTATUS WHEN 1 THEN 'New' WHEN 2 THEN 'Released' WHEN 3 THEN 'Change Order' WHEN 4 THEN 'Received' WHEN 5 THEN 'Closed' WHEN 6 THEN 'Cancelled' END AS POSTATUS FROM PurchaseOrders WHERE RowNumber <= 5 GO GRANT SELECT ON uv_AZRCRV_MostRecentFivePurchaseOrdersByVendor TO DYNGRP GO

As it is written as a view, it can easily be used in SmartList Designer, SmartList Builder, Refreshable Excel Reports, SSRS or other reporting tools.

Update on 6/4/2020: Correct corrupt SQL

TLS causing a Microsoft Dynamics GP Workflow approval error

Microsoft Dynamics GPI have recently been doing some work with a client to enable the Web Services for Microsoft Dynamics GP for external approval. This requires a steps to configure them for secure external access; when it was configured we tested on afew devices including Android smart phones and iPhones.

We found that everything worked fine, except for the Android devices which returned the following error:

Approval error on Android

ERR_HTTP2_INADEQUATE_TRANSPORT_SECURITY

When I investigated the issue I recalled that I had seen references to this issue previously, but hadn’t realised the meaning. The problem is that the web services use an older version of TLS which has started to be deprecated and actually retired; this looks like it will be a much bigger problem starting very soon as the major browsers also remove support.

Continue reading “TLS causing a Microsoft Dynamics GP Workflow approval error”

Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Save setting for how to show new POs

Microsoft Dynamics GPThis post is part of the Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests series where I am going hands on with the top feature requests of the new version of Microsoft Dynamics GP; it is part of the larger Hands On with Microsoft Dynamics GP October 2019 Release New Features series.

The fifth new feature based on the top requested features, is a new setting to control whether the displaying of the PO is skipped when a purchase requisition is purchased. Microsoft Dynamics GP 2018 RTM saw a new feature introduced whereby, when a PR was purchased, the purchase order transactions navigation list was automatically displayed. This turned out to be very disruptive for some clients due to the time it took to load the navigation list (especially when the Reporting Services Reports were deployed and the fact pane visible) that it was amended in Dynamics GP 2018 R2 to ask the user if they wanted to skip showing the navigation list.

The enhancement now is the addition of a drop down list in Purchase Order Setup (Purchasing » Setup » Purchase Order Processing) where the administrator can choose what happens:

Purchase Order Processing Setup

The default option remains Show the message which is the 2018 R2 behaviour.

I like being able to set the option to not show the question let alone the navigation list. Most of the clients I work with always click the option to skip displaying the navigation list, so this option to allow them to set it to ,em>Don’t show the message and don’t open the new PO is going to be very welcome.

Hands On with Microsoft Dynamics GP October 2019 Release New Features

Hands On with Microsoft Dynamics GP October 2019 Release New Features
Hands On with Microsoft Dynamics GP October 2019 Release New Features – System Enhancements: Sort companies in User Access Setup
Hands On with Microsoft Dynamics GP October 2019 Release New Features – System Enhancements: Filter inactive users in User Access Setup
Hands On with Microsoft Dynamics GP October 2019 Release New Features – System Enhancements: More than 32 report options in Report Group
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Long description for payables transaction entries
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Expand the view of fiscal periods
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Show user who posted for Journal Entry Inquiry
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Add Class ID to Fixed Assets Transfer
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Document Attach available in Bank Reconciliation
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Visual Cue for EFT Vendor on Edit Payment Batch
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Show Check Number in Apply Sales Document window
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: User Workflow
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: User Security Workflow
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: Security Roles Workflow
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: Security Tasks Workflow
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: Export and import workflows
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: Change approver for active workflow tasks
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: Vendor approval enhancements
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Copy Report Option
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Filter Item Stock Inquiry by Date
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Exclude Inactive Items on Item Price List Report
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Minimize transaction when Go To is selected for PO that exists
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Save setting for how to show new POs
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Inactive field added to Item SmartList
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Show the User ID in the Items SmartList
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Paste from Excel in Web Client
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests
Copy Report Option
Filter Item Stock Inquiry by Date
Exclude Inactive Items on Item Price List Report
Minimize transaction when Go To is selected for PO that exists
Save setting for how to show new POs
Inactive field added to Item SmartList
Show the User ID in the Items SmartList
Paste from Excel in Web Client

Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Minimize transaction when Go To is selected for PO that exists

Microsoft Dynamics GPThis post is part of the Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests series where I am going hands on with the top feature requests of the new version of Microsoft Dynamics GP; it is part of the larger Hands On with Microsoft Dynamics GP October 2019 Release New Features series.

The fourth enhancement based on top requested features is the minimisation of the Payables Transaction Entry window when you choose Go To when you receive a warning that a PO exists for that vendor:

Go To message

The purchasing transactions navigation list displays with the Payables Transaction Entry open with the transaction entered, but minimised so it can’t be seen. Once you’re done with the navigation list, you can restore the Payables Transaction Entry window to post or delete the transaction.

this is apparently one of the top requested features, but I don’t really see the point. A lot of the clients I work with have dual monitors so are more likely to want to move the window to a secondary monitor rather than have it minimised. Unfortunately, this new feature doesn’t have any options to control the behaviour so the transaction entry window is minimised if you opt to go to the navigation list.

Hands On with Microsoft Dynamics GP October 2019 Release New Features

Hands On with Microsoft Dynamics GP October 2019 Release New Features
Hands On with Microsoft Dynamics GP October 2019 Release New Features – System Enhancements: Sort companies in User Access Setup
Hands On with Microsoft Dynamics GP October 2019 Release New Features – System Enhancements: Filter inactive users in User Access Setup
Hands On with Microsoft Dynamics GP October 2019 Release New Features – System Enhancements: More than 32 report options in Report Group
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Long description for payables transaction entries
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Expand the view of fiscal periods
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Show user who posted for Journal Entry Inquiry
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Add Class ID to Fixed Assets Transfer
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Document Attach available in Bank Reconciliation
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Visual Cue for EFT Vendor on Edit Payment Batch
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Financial Enhancements: Show Check Number in Apply Sales Document window
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: User Workflow
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: User Security Workflow
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: Security Roles Workflow
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: Security Tasks Workflow
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: Export and import workflows
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: Change approver for active workflow tasks
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Workflow Enhancements: Vendor approval enhancements
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Copy Report Option
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Filter Item Stock Inquiry by Date
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Exclude Inactive Items on Item Price List Report
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Minimize transaction when Go To is selected for PO that exists
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Save setting for how to show new POs
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Inactive field added to Item SmartList
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Show the User ID in the Items SmartList
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests: Paste from Excel in Web Client
Hands On with Microsoft Dynamics GP October 2019 Release New Features – Top Feature Requests
Copy Report Option
Filter Item Stock Inquiry by Date
Exclude Inactive Items on Item Price List Report
Minimize transaction when Go To is selected for PO that exists
Save setting for how to show new POs
Inactive field added to Item SmartList
Show the User ID in the Items SmartList
Paste from Excel in Web Client

Is there a POP Historical Received Not Invoiced Report Available?

Microsoft Dynamics GPI’ve had a couple of people recently ask if it is possible to get a historical version of the Shipments Received but not invoiced* SmartList favourite (located under Purchasing » Receivings Line Items).

Within SmartList, this is not possible as the point-in-time calculations required for a historical report are too complex for a SmartList, even one driven by a view.

While historically this report wasn’t available in Microsoft Dynamics GP, this changed in the Microsoft Dynamics GP 2015 R2 release when a Reporting Services Report version of a Historical Received Not Invoiced Report was introduced. It seems though that many people are remain unaware of this new Historical RNI report.

As long as you have the Reporting Services Reports from the Reporting Tools Setup window deployed you can launch the report from the Purchasing series Reporting Services Reports navigation pane:

Historical Received Not Invoiced

This report allows you to enter criteria including a Date as of.

Feature Explained: Invoice Receipt Date

Microsoft Dynamics GPBack in 2017, regulations in the UK changed so that business of a certain minimum size, had to start recording the date an invoice was received. To support this new regulation, Microsoft introduced a new Invoice Receipt Date field to a number of windows in the Purchasing series. This new field was introduced in the July 2017 hotfix.

The presence of this field does not seem especially well-known, so I figured I do a quick post covering where it is and how it would be used.

The Invoice Receipt Date has been added to the Date Entry windows for the three types of transaction:

  • Payables Transaction Entry (Purchasing area page » Transactions » Transaction Entry)
  • Receivings Transaction Entry (Purchasing area page » Transactions » Receivings Transaction Entry)
  • Purchasing Invoice Entry (Purchasing area page » Transactions » Enter/Match Invoice)

Taking Payables Transaction Entry as an example, to access the Payables Date Entry field, click the expansion arrow next to the Doc. Date field:

Payables Transaction Entry

Continue reading “Feature Explained: Invoice Receipt Date”

Hands On With Microsoft Dynamics GP 2018 R2 New Features: Skip Displaying New POs When Generating a PO

Microsoft Dynamics GPThis post is part of the Hands On With Microsoft Dynamics GP 2018 R2 New Features series in which I am going hands on with the new features introduced in Microsoft Dynamics GP 2018 R2 (which was released on the 2nd October). I reblogged the new features as Microsoft announced them along with some commentary of how I thought they would be received by both my clients and I. In this series, I will be hands on with them giving feedback of how well they work in reality.

This hands on new feature is, as far as I am aware, an unannounced one which I stumbled across when testing the partial purchase a purchase requisition new feature. When you click the Generate button on the Purchase Order Preview from Purchase Requisition Entry window, a prompt Skip displaying the new purchase orders? will be displayed:

Microsoft Dynamics GP - Skip displaying the new purchase orders?

This is a change of behaviour from the new functionality introduced in Microsoft Dynamics GP 2018 RTM; the purpose of this original new feature was to improve visibility of the created purchase orders.

This was, and remains, a laudable goal, but unfortunately the implementation was somewhat lacking. The new purchase orders were displayed by opening a navigation list, which could be ver slow to open (especially if the Reporting Services Reports had been deployed and the fact pane visible.

The 2018 R2 new feature, to hide Business Analyser for all users would go some way to mitigate this issue, by hiding the fact pane, but this new feature of prompting the user to decide if they want to see the navigation list will go the majority of the remaining way (a global option to disable would have gone all the way).

Click to show/hide the Hands On With Microsoft Dynamics GP 2018 R2 New Features Series Index

Hands On With Microsoft Dynamics GP 2018 R2 New Features: Email POP PO Other Form

Microsoft Dynamics GPThis post is part of the Hands On With Microsoft Dynamics GP 2018 R2 New Features series in which I am going hands on with the new features introduced in Microsoft Dynamics GP 2018 R2 (which was released on the 2nd October). I reblogged the new features as Microsoft announced them along with some commentary of how I thought they would be received by both my clients and I. In this series, I will be hands on with them giving feedback of how well they work in reality.

The twenty-third new feature is Email POP PO Other Form. This feature makes the Purhcase Order Other Form available for email.

My hands on with the feature was somewhat abbreviated when I discovered that there was no default Word Template available. While technically allowing a PO to be emailed using the Other Form, the lack of a default Word Templates means that there is a large amount of effort involved to make it available.

To create a new Word Template, you need to use the Word Template Generator to create a Word Template from the Report Writer XML and then format the resulting template; this is a long, complex and difficult to process to do in order not to break the template so email, or even the Word Template itself, doesn’t get broken.

Click to show/hide the Hands On With Microsoft Dynamics GP 2018 R2 New Features Series Index

Hands On With Microsoft Dynamics GP 2018 R2 New Features: Purchase Requisition Partial Purchase

Microsoft Dynamics GPThis post is part of the Hands On With Microsoft Dynamics GP 2018 R2 New Features series in which I am going hands on with the new features introduced in Microsoft Dynamics GP 2018 R2 (which was released on the 2nd October). I reblogged the new features as Microsoft announced them along with some commentary of how I thought they would be received by both my clients and I. In this series, I will be hands on with them giving feedback of how well they work in reality.

The twenty-second new feature is Purchase Requisition Partial Purchase. You’ve always been able to increase the quatity to purchase, but you were not able to reduce the quantity. This new feature, allows a smaller quantity to purchase to be specified in order to partial purchase the requisition; the remaining quantity will be cancelled:

Microsoft Dynamics GP - The remaining quantity ordered will be cancelled.

The flexibility this offers should be a good thing, but I have concerns around users being able to change the purchasing quantity here if an approval workflow is being used. That said, I already had these concerns about users being able to increase the quantity post approval.

Click to show/hide the Hands On With Microsoft Dynamics GP 2018 R2 New Features Series Index