Handling The Broken ClearCompanies Script

Microsoft Dynamics GPAs I covered in this post there is a problem in the Clear Companies script available from Microsoft which breaks the User Smart List Master (ADH00100) table.

The issue is that the clear companies script deletes rows from the ADH00100 table when the relevant database doesn’t exist. This will only be a problem for sites which have SmartList objects created with SmartList Designer, which explains why I haven’t seen the problem more often.

I posted the code which can be used to update the clear companies script, but if you don’t want to maintaina custom version of this script, there are two actions you can take.

If you know of this issue in advance of running the clear companies script, you can build into your process the runing of a script against the table to change all of the CMPANYID entries in the table to the number of a company which does exist:

/*
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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
UPDATE
	['User Smart List Master']
SET
	CMPANYID = (SELECT TOP 1 CMPANYID FROM SY01500 ORDER BY CMPANYID DESC)
FROM
	ADH00100 AS ['User Smart List Master']
WHERE
	CMPANYID <> 0
AND
	(SELECT COUNT(database_id) FROM sys.databases AS ['System Databases'] WHERE ['User Smart List Master'].CMPANYID = ['System Databases'].database_id) = 0
GO

This script is configured to only update the CMPANYID field when the relevant database doesn’t exist or the field is set to 0.

Continue reading “Handling The Broken ClearCompanies Script”

Deleting A Company Breaks SmartList

Microsoft Dynamics GPA client recently replicated their live system over to a standalone test system, but, when they did so, they did not migrate all of the databases over. They took all of the live and test over, but did not take the historical databases. After copying the databases over, they ran the Clear Companies script available in KB855361.

Everything looked fine, until they tried to open SmartList when they received an unhandled script exception error; I don’t have a screenshot of the error, but the text is reproduced below:

Microsoft Dynamics GP

Unhandled script exception:
Index 0 of local array is out of range in script 'ASI_Initialize_Explorer_Tree'. Script terminated.

I did some checking around (both reviewing data using SQL Profiler, but also searching online where I found this thread on the Dynamics Community forum.) and determined that the error is related to the User Smart List Master (ADH00100)User Smart List Master (ADH00100) table.

Continue reading “Deleting A Company Breaks SmartList”

SQL Stored Procedure to Remove Prior Day Logins

Microsoft Dynamics GPIn the last post, I posted a SQL view which returned a list of users who had logged in before the current date. This post contains a SQL stored procedure which will delete any prior day login; this could be scheduled to run using SQL Server Agent:

CREATE PROCEDURE usp_AZRCRV_RemovePriorDayLogins 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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
	DELETE FROM
		ACTIVITY
	WHERE
		['User Activity'].LOGINDAT <= DATEADD(DAY, -1, GETDATE())
GO
GRANT EXECUTE ONusp_AZRCRV_RemovePriorDayLogins TO DYNGRP
GO

Before using this script on a live system, I’d recommend testing it on a standalone test system first.

The Future of Microsoft Dynamics GP, or “Project Green: Revenant”

Microsoft Dynamics GPLike the lich which won’t die and stay dead, Project Green rears it’s head once again. This time from former Dynamics NAV MVP Mark Brummel.


Image courtesy of hyena reality at FreeDigitalPhotos.net

Dynamics GP MVP Belinda Allen has responded with a blog post, The Future of Microsoft Dynamics GP, rebutting Mark’s statement about the future of Dynamics GP.

I’d encourage you to read Belinda’s post in full, but a couple of passages seem worthy of quoting:

Is GP Retired and in Maintenance Only Mode?

Microsoft Dynamics GP is NOT retired, or in maintenance only mode. I’m not sure from where Mark heard this rumor, but it is not anywhere close to being correct. This has been made very clear by the Microsoft team at every customer and partner facing event that has been held over the years, with the sharing of the Roadmap. The GP Roadmap always shows 2-3 versions out, unlike other Microsoft products (which are also not retiring) that show 1-2 versions out.

And the second quote:

Before I began writing this article, I reached out to Jeff Trosen, Engineering Manager at Microsoft, sharing Mark’s post. Jeff quickly replied: GP Engineering is happily working on features for the fall release of GP, we did not go into maintenance mode. If you look at our communications on GP, nothing has changed.

I’ve been working with Microsoft Dynamics GP for almost 15 years now (and have been blogging for 6 and a half years) and this story has come and gone several times in those years and Dynamics GP still has a presence on the roadmap.

It would be nice to see it die a final death, but, unfortunately, I’m sure the same conversations will be had again in future.

SQL View to Return Prior Day Logins

Microsoft Dynamics GPMicrosoft Dynamics GP is licensed, for full users, on a concurrent user basis. This means that you can create more users than can be logged in at the same time; unfortunately, this means that if users don’t log out correctly, that the license remains in use.

The below script can be plugged into a SmartList Designer to give easy visibility of who logged in before the current day.

CREATE VIEW uv_AZRCRV_GetPriorDayLogins 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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT
	['User Activity'].USERID AS 'User ID'
	,ISNULL(['Users Master'].USERNAME, 'User Master record not found') AS 'User Name'
	,ISNULL(['Company Master'].INTERID, 'Company Master record not found') AS 'Inter ID'
	,['User Activity'].CMPNYNAM AS 'Company Name'
	,FORMAT(['User Activity'].LOGINDAT, 'yyyy-MM-dd') AS 'Login Date'
	,FORMAT(['User Activity'].LOGINTIM, 'hh:mm:ss') AS 'Login Time'
FROM
	ACTIVITY AS ['User Activity']
LEFT JOIN
	SY01400 AS ['Users Master']
		ON
			['User Activity'].USERID = ['Users Master'].USERID
LEFT JOIN
	SY01500 AS ['Company Master']
		ON
			['User Activity'].CMPNYNAM = ['Company Master'].CMPNYNAM
WHERE
	['User Activity'].LOGINDAT <= DATEADD(DAY, -1, GETDATE())
GO
GRANT SELECT ON uv_AZRCRV_GetPriorDayLogins TO DYNGRP
GO

Hands On With MDGP 2018 RTM New Features: One Payment Per Vendor/Invoice Setting on Vendor Card

Microsoft Dynamics GPMicrosoft Dynamics GP 2018 RTM has now been released and I have been taking a Hands On look at the installation and configuration of the new version.

I am now going to take a hands on look at the new and enhanced features introduced in Microsoft Dynamics GP. Rather than do this as a continuation of the Hands On series I wrote in December, as originally intended, I am going to split it out into a separate hands On With the features post. The series index is for this new features series, can be found here.

The twenty-third new feature of the day is One Payment Per Vendor/Invoice Setting on Vendor Card. In older versions of Dynamics GP, you could, on the Select Checks window (Purchasing area page >> Transactions >> Select Checks), select to produce one payment per vendor or invoice; in Dynamics GP 2018, the same options exists in the Build Payment Batch window (Purchasing area page >> Transactions >> Build Payment Batch), which is a renamed Select Checks window, along with the option to read the setting from the Vendor Maintenance window.

To set the option on the vendor, open Vendor Maintenance window (Purchasing area page >> Cards >> Vendor Maintenance), select a vendor and click the Options button.

The option is a pair of radio buttons half way down the page which is defaulted to Vendor; this default will apply to all existing vendors when you upgrade to Dynamics GP 2018:

Vendor Maintenance

Continue reading “Hands On With MDGP 2018 RTM New Features: One Payment Per Vendor/Invoice Setting on Vendor Card”

Hands On With MDGP 2018 RTM New Features: Print Purchase Requisition

Microsoft Dynamics GPMicrosoft Dynamics GP 2018 RTM has now been released and I have been taking a Hands On look at the installation and configuration of the new version.

I am now going to take a hands on look at the new and enhanced features introduced in Microsoft Dynamics GP. Rather than do this as a continuation of the Hands On series I wrote in December, as originally intended, I am going to split it out into a separate hands On With the features post. The series index is for this new features series, can be found here.

The twenty-second feature of the day is Print Purchase Requisition. I said in the Feature of the Day series, that my feelings were largely negative towards this feature and my opinion hasn’t changed now that I’ve been hands on with it.

A new Print button has been added to the Purchase Requisition Entry window (Purchasing area page >> Transactions >> Purchase Requisitions):

Purchase Requisition Entry

Continue reading “Hands On With MDGP 2018 RTM New Features: Print Purchase Requisition”

Hands On With MDGP 2018 RTM New Features: Make PO Number Visible to Requisition Originator

Microsoft Dynamics GPMicrosoft Dynamics GP 2018 RTM has now been released and I have been taking a Hands On look at the installation and configuration of the new version.

I am now going to take a hands on look at the new and enhanced features introduced in Microsoft Dynamics GP. Rather than do this as a continuation of the Hands On series I wrote in December, as originally intended, I am going to split it out into a separate hands On With the features post. The series index is for this new features series, can be found here.

The twenty-first feature of the day is Make PO Number Visible to Requisition Originator. This feature of the day makes the PO number for purchased requisitions visible to the originator by adding a PO Number column to the Procurement section of the home page:

Procurement

This feature is a nice addition; it gives the originator confirmation and an easy way of seeing the PO Number for the requisition.

Click to show/hide the Hands On With MDGP 2018 RTM New Features Series Index

Hands On With MDGP 2018 RTM New Features: On PO Generation Show Purchase Order Transactions Navigation List

Microsoft Dynamics GPMicrosoft Dynamics GP 2018 RTM has now been released and I have been taking a Hands On look at the installation and configuration of the new version.

I am now going to take a hands on look at the new and enhanced features introduced in Microsoft Dynamics GP. Rather than do this as a continuation of the Hands On series I wrote in December, as originally intended, I am going to split it out into a separate hands On With the features post. The series index is for this new features series, can be found here.

The twentieth feature of the day is On PO Generation Show Purchase Order Transactions Navigation List. When I heard of this feature, I initially thought it might be quite useful, but since I’ve been hands on, I’ve changed my mind.

The feature works by launching the Purchase Order Transactions navigation list when the purchase requisition is purchased and the purchase order created:

Purchase Order Transactions navigation list

My problem is that navigation lists can be quite slow to load, especially when Reporting Services Reports have been deployed. This feature would have been much better if it had been introduced with an option to enable/disable it (I’ve looked and can’t find one; if anyone knows different please let me know via the comments).

Click to show/hide the Hands On With MDGP 2018 RTM New Features Series Index

Hands On With MDGP 2018 RTM New Features: Print Single Customer Statement

Microsoft Dynamics GPMicrosoft Dynamics GP 2018 RTM has now been released and I have been taking a Hands On look at the installation and configuration of the new version.

I am now going to take a hands on look at the new and enhanced features introduced in Microsoft Dynamics GP. Rather than do this as a continuation of the Hands On series I wrote in December, as originally intended, I am going to split it out into a separate hands On With the features post. The series index is for this new features series, can be found here.

The nineteenth feature of the day, is Email Single Customer Statement from the Customer Maintenance window (Sales >> Cards >> Customer), or at least that is how it was originally billed by Microsoft.

However, now that I’ve had the chance to get hands on with it, it is not the ability to email a statement, but only the ability to print one using the old Standard report. This is very disappointing as this new functionality doesn’t even allow the printing to the Word Template version of the template.

To print the single statement, open the Customer Maintenance window, load a customer, click the Print button to display a small menu, click Statement:

Customer Maintenance

Continue reading “Hands On With MDGP 2018 RTM New Features: Print Single Customer Statement”