Implementing Fastpath’s Assure Suite: Series Index

FastpathThis series of posts is on the Fastpath Assure Suite of products from Fastpath. I’ve dealt with this product a few times, but have recently implemented it twice, I thought a series of posts would be useful.

Over the course of the series, I will cover the installation and configuration of the Assure Suite, but with a specific focus on use of the Audit Trails module; I will return to the series in future and cover the use of the other modules.

Continue reading “Implementing Fastpath’s Assure Suite: Series Index”

Install Dynamics GP Web Services from the command line

Microsoft Dynamics GPI was giving a colleague a hand with a Microsoft Dynamics GP 2018 R2 upgrade recently (the client didn’t want to upgrade to the very latest version) and we encountered a problem with the installation of the Web Services:

Clr method invocation failed

Setup.exe .NET Framework Error

Clr method invocation failed

Continue reading “Install Dynamics GP Web Services from the command line”

Stored procedure to get next Dynamics GP Payables Voucher Number

Microsoft Dynamics GPI’ve previously posted SQL stored procedures to get the next GL Journal Number and the next PO Receipt number and today it is the turn of a stored procedure to get the next PM Voucher Number.

The stored procedure will call the eConnect stored procedure which gets the next number and increments the stored value. This code was written so I could easily call it from VBA in Integration Manager for an integration which needed to insert some data into a custom table. This allowed me to get the voucher number up front and use it in the VBA.

-- drop stored proc if it exists
IF OBJECT_ID(N'usp_AZRCRV_GetNextPMVoucherNumber', N'P') IS NOT NULL
    DROP PROCEDURE usp_AZRCRV_GetNextPMVoucherNumber
GO
-- create stored proc
CREATE PROCEDURE usp_AZRCRV_GetNextPMVoucherNumber 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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
BEGIN
	DECLARE @return_value AS INT
	DECLARE @O_vCNTRLNUM AS VARCHAR(17)
	DECLARE @I_sCNTRLTYP AS TINYINT = 0
	DECLARE @O_iErrorState AS INT

	EXEC @return_value = taGetPMNextVoucherNumber
				, @I_sCNTRLTYP = @I_sCNTRLTYP
				,  @O_iErrorState = @O_iErrorState OUTPUT
				@O_vCNTRLNUM = @O_vCNTRLNUM OUTPUT
	SELECT @O_vCNTRLNUM AS VCHRNMBR
END
GO

-- grant execute permission on stored proc to DYNGRP
GRANT EXECUTE ON usp_AZRCRV_GetNextPMVoucherNumber TO DYNGRP
GO

The stored procedure can be executed using this command:

-- execute stored proc
EXEC usp_AZRCRV_GetNextPMVoucherNumber
GO

Dynamics GP Reconcile to GL missing the option for Inventory

Microsoft Dynamics GPI helped field a query from a client recently on the Reconcile to GL routine (Financial » Routines » Reconcile to GL) showing Payables Management, Receivales Management and Bank Reconciliation, but not Inventory.

This client has been a long time user of Microsoft Dynamics GP who had recently upgraded. This issue is a known one, addressed on the Dynamics GP Support and Services Blog.

With the support of the helpdesk, the client was able to run through the steps to resolve the issue and perform a reconciliation of the Inventory module to the GL.

Wildcard character in Dynamics GP Workflow conditions

Microsoft Dynamics GPI’ve done a lot of work with the Microsoft Dynamics GP Workflow module over the years since it was first released and it still seems to be growing in popularity with clients. Depending on the workflow type being created, the GL account string can be used in conditions to determine the routing for the approver.

However, commonly clients want to check the value for a single segment rather than the full account string. You can, to an extend, accomplish this using filters such as contains or begins with, but sometimes this isn’t feasible. You can get round this problem be using the _ wildcard in your condition.

For example, if I was setting up a purchase requisition workflow and wanted to configure a routing for all advertising expense codes, which is the codes where the Account segment start 66, to the Marketing Director, I could set up a condition where the account string is ___-66_0-00:

Workflow Condition error with wildcards in the account string

Workflow Condition example with wildcards in the account string

This would use this workflow step for all Divisions (segment 1) for department 00, where the Account (segment 2) starts with 66 and ends with 0. The wildcard character allows an account string with any value in the characters replaced with an underscore to be selected.

“The vendor has an existing purchase order…” error message when using Integration Manager

Microsoft Dynamics GPI’ve recently been working with a client to implement Microsoft Dynamics GP and have been using Integration Manager to import the opening data. While importing payables transactions I encountered the following error:

The vendor has an existing purchase order error

The vendor has an existing purchase order. Choose Continue to post or save. Choose Go To to view a purchasing navigation list. Choose Cancel to return to the window...

Continue reading ““The vendor has an existing purchase order…” error message when using Integration Manager”

SQL Stored Procedure to delete old Microsoft Dynamics GP Document Attachments

Microsoft Dynamics GPThe Document Attachment feature was introduced in Microsoft Dynamics GP 2013 RTM and has been enhanced a number of times since. One of the features it does not have is the ability to delete attachments; you can flag them as deleted, but they are not removed from the database.

With GDPR rules, clients have become concerned about the information retained in the system without a means to delete it. To that end I created a SQL stored procedure which could be scheduled to run on a regular basis and delete transactions older than the specified number of years (highlighted value is the number of years).

This allows clients to run this on a scheduled basis and remove old documents; it can also serve as the basis for a customised version which deletes on a more controlled basis.

As with any script, please ensure you perform through testing before deploying to a live system.

IF OBJECT_ID (N'usp_AZRCRV_DeleteDocAttachAttachments', N'P') IS NOT NULL
    DROP PROCEDURE usp_AZRCRV_DeleteDocAttachAttachments
GO

CREATE PROCEDURE dbo.usp_AZRCRV_DeleteDocAttachAttachments
	@iAge INTEGER = 7
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). */
-- CREATE TEMPORARY TABLE CREATE TABLE #AttachmentsToDelete( Attachment_ID CHAR(37) ) -- SELECT ATTACHMENTS OVER n YEARS OLD TO DELETE INSERT INTO #AttachmentsToDelete (Attachment_ID) --VALUES ( SELECT Attachment_ID FROM CO00101 WHERE CREATDDT < DATEADD(yyyy, -@iAge, GETDATE()) ) -- DELETE FROM Document Attachment Master (CO00101) DELETE FROM CO00101 WHERE Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete) -- DELETE FROM Document Attachment Reference (CO00102) DELETE FROM CO00102 WHERE Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete) -- DELETE FROM Document Attachment Properties (CO00103) DELETE FROM CO00103 WHERE Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete) -- DELETE FROM Document Attachment Status (CO00104) DELETE FROM CO00104 WHERE Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete) -- DELETE FROM Document Attachment Email (CO00105) DELETE FROM CO00105 WHERE Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete) -- DELETE FROM COATTACHMENTITEMS DELETE FROM coAttachmentItems WHERE Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete) -- DROP TEMPORARY TABLE DROP TABLE #AttachmentsToDelete GO

SQL View to return Microsoft Dynamics GP Workflow step approvers

Microsoft Dynamics GPI was recently talking to a client who was looking at creating a SQL script which they could run for the auditors which shows the assigned approvers to the steps of a Microsoft Dynamics GP Workflow process.

I’d written similar code for others before so I was able to provide them with this view:

CREATE VIEW uv_AZRCRV_GetWorkflowSetupStepAssignment 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). */
SELECT ['Workflow Step Instance Table'].Workflow_name ,['Workflow Step Instance Table'].WF_Step_Predecessor ,['Workflow Step Instance Table'].Workflow_Step_Name ,['Workflow Step Instance Table'].WF_Step_Description ,['Workflow Step Instance Table'].EmailMessageID ,['Workflow Users'].ADLogin ,['Workflow Users'].ADDisplayName FROM WF100003 AS ['Workflow Step Instance Table'] LEFT JOIN WF40200 AS ['Workflow Users'] ON ['Workflow Step Instance Table'].Workflow_Step_Assign_To = ['Workflow Users'].UsersListGuid GO

This can be deployed to a company database and a SmartList object created using either SmartList Designer or SmartList Builder.

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: Paste from Excel in Web Client

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 eighth, and final, new feature from the top user requests, is copy and paste from Excel in the web client. This has not worked since 2015 R2 with fixes promised always in the next version.

Again it has been included as a feature in the October 2019 release, but while it allows you to get the data from Ececl into the GL Transaction Entry window, but is instead an import. To import the journal lines in the web client, click the Paste button and then click Choose file:

Import from Excel

You can browse to select a file to import as journal lines. The format is the same as when pasting in the desktop client.

It works, but it is not as nice and easy as the copy and paste possible in the desktop client and is not a copy and paste despite the announcement.

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