Handling The Broken ClearCompanies Script

As 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 (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).
*/
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) 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 (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).
*/
	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.

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 (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).
*/
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: System Password Remembered

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 fifteenth feature of the day, is Remember System Password.

The new feature means that the system password now only needs to be entered once per session:

Enter the system password.

If you have ever needed to build a new security model for Dynamics GP, you will have become heartily sick of having to enter the system password multiple times. In fact, every single time a protected window was opened, the password needed to be entered.

This feature is a most welcome one, but has one drawback. Once entered, the system password is remembered for the rest of the session. This means if you enter it for a user, such as, for example, to give them access to the VAT Return window, they can then access any other window which is usually protected with the system password.

This shouldn’t generally be a problem as the security roles would have locked down the security windows so normal users wouldn’t see them, but it is something to be aware of and consider.

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

MDGP 2018 RTM Feature of the Day: System Password Remembered

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2018 RTM; as the most recent versions have been, these posts are in the form of PowerPoint slides; I am reposting them here so they can be read more easily as well as adding my own commentary.

The series index for this series of posts is here.

The thirteenth Feature of the Day is System Password Remembered.

One of the big frustrations for people who maintain security in Microsoft Dynamics GP is having to type the System Password every single time a password protected window is opened.

This feature sees a change whereby the System Password, ocnce entered, is remembered for the rest of the session.

This feature is well overdue and is going to save so much frustration from having to enter the system password so many times when creating security roles and tasks.

Click to show/hide the MDGP 2018 RTM Feature of the Day Series Index

Fixing Reports Not Implemented Error After Upgrade

Microsoft Dynamics GPWe’ve been doing a number of upgrades recently and I encountered an old error when trying to deploy the SQL Server Reporting Services (SSRS) and Excel Reports through the Reporting Tools Setup window (Administration >> Setup >> System >> Reporting Tools).

This particular client is being upgraded from Microsoft Dynamics GP 2013 SP2 to 2016 R2.

The problem is that some of the companies are showing as Not implemented. The companies showing this way are the oldest; TEST2016… is actually the main company renamed for testing.

Reporting Tools Setup

Continue reading “Fixing Reports Not Implemented Error After Upgrade”

Hands On With Microsoft Dynamics GP 2016 R2: Batch Edited Message

Microsoft Dynamics GPWith the release of Microsoft Dynamics GP 2016 R2 it’s time for a series of “hands on” posts where I go through the installation of all of it’s components and also look at the new functionality introduced; the index for this series can be found here.

The feature of the day I am covering in this post, is the Batch Edited Message. If a user tries to open a batch being edited by another user they will receive a warning message containing the users name:

Microsoft Dynamics GP: This batch is being edited by laurab. Please try later.

Microsoft Dynamics GP

This batch is being edited by laurab. Please try later.

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: Batch Edited Message”

Hands On With Microsoft Dynamics GP 2016 R2: SmartList Favourite Protection

Microsoft Dynamics GPWith the release of Microsoft Dynamics GP 2016 R2 it’s time for a series of “hands on” posts where I go through the installation of all of it’s components and also look at the new functionality introduced; the index for this series can be found here.

The second of the new features I’m going to cover is SmartList Favorite Protection.

This feature allows the administrators to define a password which must be entered before a SmartList favorite can be modified. The password is entered in the SmartList Options window (Administration >> Setup >> System >> SmartList Options):

SmartList Options

The password impacts all SmartList favorites. Please do not use ACCESS as the password; I see far too many clients who have this as a password throughout their system. It is in all of the manuals and lots of blog posts so it is pretty much the least secure password you could use.

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: SmartList Favourite Protection”

Microsoft Dynamics GP January Hotfix Released

Microsoft Dynamics GPI don’t usually post about releases, except for major version releases. However, the January Hotfix release caught my eye with a few of the included features in the 2016 R2 one.

As well as having the US Payroll updates there are updates to the following which would be of benefit to clients in the UK:

  • VAT Daybook

    • VAT Daybook Summary reports may not show correct data in boxes 1, 6 and 7 if you voided a transaction.
    • VAT detail report may show incorrect figures if you have 2 or more tax codes on a transaction.
  • General Ledger

    • The Excel copy and paste function may not calculate the credit/debit column correctly when using an allocation account.
  • Fixed Asset Management

    • Tax values may not update properly if a user marks to create multiple fixed assets in the Fixed Asset Purchase Order additional information window.
  • Bank Reconciliation

    • You may experience performance issues with the Checkbook Register Inquiry window after you install GP 2016 R2.
  • Project Accounting

    • Ability to change line distributions for time and material projects in revenue recognition.
    • Fee Accounts not available in line distributions button.
    • Purchase Receiving’s not reflecting WIP account edits from Purchase Order for non-inventory items.
  • System Manager

    • Fixed issues with Uncollated printing.

The hotfixes for Dynamics GP 2013 R2 and 2015 R2 only contain US Payroll fixes.

The post from the Dynamics GP Support and Services blog contains the download links.