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”

MDGP 2018 RTM Feature of the Day: SmartList Favorite Password Protection

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 won commentary.

The series index for this series of posts is here.

The sixteenth Feature of the Day is SmartList Favorite Password Protection. This new feature is in response to the reaction to the Microsoft Dynamics GP 2016 R2 new feature of SmartList Favorite Protection.

The problem with this feature, was that the security was at the global SmartList level so all SmartLists would be protected by the same password. The new feature being introduced in Dynamics GP 2018, is password protection at the SmartList Favourite level:

Add or Remove Favorites

I’m bery happy to see the password available at the SmartList Favourite level as this will make it useful to quite a few clients who have asked about how to protect their favourites. It’s also nice to see it for another reason; it shows that Microsoft is responsive to the requests of the Dynamics GP community.

You can influence the development of Microsoft Dynamics GP by submitting, and voting on, suggestions on the Microsoft Connect website.

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

Security Views For Use In SmartList Designer: Group Based Company Access In Management Reporter

Microsoft Dynamics GPA while ago, I did a series of views on the Microsoft Dynamics GP security model. Well, a little after that I wrote a couple of scripts to allow the security configuration of Management Reporter to easily be enquired upon.

This, the second Management Reporter security script, shows security for users as granted by their Group membership. the previous post, on Friday, showed the user based company access.

The view is configured to read the security from a database called ManagementReporter and assumes the user who runs the report has select permissions on this database and relevant tables.

IF OBJECT_ID (N'uv_AZRCRV_GetManagementReporterGroupBasedSecurity', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GetManagementReporterGroupBasedSecurity
GO
CREATE VIEW uv_AZRCRV_GetManagementReporterGroupBasedSecurity 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
	['Security User'].UserName AS 'Username'
	,['Security User Principal'].Name AS 'Domain Name'
	,['Security User'].LastLoginAttempt AS 'Last Login Attempt'
	,CASE ['Security User'].RoleType
		 WHEN 2 THEN
			'Viewer'
		 WHEN 3 THEN
			'Generator'
		 WHEN 4 THEN
			'Designer'
		 WHEN 5 THEN
			'Administrator'
		ELSE
			'None'
		END AS 'Role'
		,['Security Group Principal'].Name AS 'Group Name'
		,['Security Group Principal'].Description AS 'Group Description'
		,['Control Company'].Code AS 'INTERID'
		,['Control Company'].Name AS 'Company Name'
 FROM 
	Reporting.SecurityUser AS ['Security User'] WITH (NOLOCK)
INNER JOIN
	Reporting.SecurityPrincipal AS ['Security User Principal'] WITH (NOLOCK)
		ON
			['Security User'].UserID = ['Security User Principal'].ID
LEFT JOIN
	Reporting.SecurityGroupUser AS ['Security Group User'] WITH (NOLOCK)
		ON
			['Security User'].UserID = ['Security Group User'].UserID
LEFT JOIN
	Reporting.SecurityPrincipal AS ['Security Group Principal']  WITH (NOLOCK)
		ON
			 ['Security Group User'].GroupID = ['Security Group Principal'].ID
LEFT JOIN
	Reporting.SecurityCompanyPermission AS ['Security Company Group Permission'] WITH (NOLOCK)
		ON
			['Security Group Principal'].ID = ['Security Company Group Permission'].PrincipalID
LEFT JOIN
	Reporting.ControlCompany AS ['Control Company'] WITH (NOLOCK)
		ON
			['Security Company Group Permission'].CompanyID = ['Control Company'].ID
GO
GRANT SELECT ON uv_AZRCRV_GetManagementReporterGroupBasedSecurity TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

Security Views For Use In SmartList Designer: User Based Company Access In Management Reporter

Microsoft Dynamics GPA while ago, I did a series of views on the Microsoft Dynamics GP security model. Well, a little after that I wrote a couple of scripts to allow the security configuration of Management Reporter to easily be enquired upon.

This first script returns the security based on how the user is configured; the view I will post on Monday shows Group based security.

The view is configured to read the security from a database called ManagementReporter and assumes the user who runs the report has select permissions on this database and relevant tables.

IF OBJECT_ID (N'uv_AZRCRV_GetManagementReporterUserBasedSecurity', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GetManagementReporterUserBasedSecurity 
GO
CREATE VIEW uv_AZRCRV_GetManagementReporterUserBasedSecurity 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
	['Security User'].UserName AS 'Username'
	,['Security User Principal'].Name AS 'Domain Name'
	,['Security User'].LastLoginAttempt AS 'Last Login Attempt'
	,CASE ['Security User'].RoleType
		 WHEN 2 THEN
			'Viewer'
		 WHEN 3 THEN
			'Generator'
		 WHEN 4 THEN
			'Designer'
		 WHEN 5 THEN
			'Administrator'
		ELSE
			'None'
		END AS 'Role'
	,['Control Company'].Code AS 'INTERID'
	,['Control Company'].Name AS 'Company Name'
FROM 
	ManagementReporter.Reporting.SecurityUser AS ['Security User'] WITH (NOLOCK)
INNER JOIN
	ManagementReporter.Reporting.SecurityPrincipal AS ['Security User Principal'] WITH (NOLOCK)
		ON
			['Security User'].UserID = ['Security User Principal'].ID
LEFT JOIN
	ManagementReporter.Reporting.SecurityCompanyPermission AS ['Security Company Permission'] WITH (NOLOCK)
		ON
			['Security User Principal'].ID = ['Security Company Permission'].PrincipalID
LEFT JOIN
	ManagementReporter.Reporting.ControlCompany AS ['Control Company'] WITH (NOLOCK)
		ON
			['Security Company Permission'].CompanyID = ['Control Company'].ID
GO
GRANT SELECT ON uv_AZRCRV_GetManagementReporterUserBasedSecurity TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

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”

Hands On With Microsoft Dynamics GP 2016 R2: SmartList Designer Favorites In Advanced Lookups

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.

This is the first of the posts where I will be covering the new functionality introduced to Microsoft Dynamics GP 2016 R2. In this post I am going to cover the addition of SmartList Designer favorites in Advanced Lookups new feature.

To use this feature, create a SmartList Designer object:

SmartList Designer

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: SmartList Designer Favorites In Advanced Lookups”

MDGP 2016 R2 Feature of the Day: SmartList Favorite Protection

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2016 R2; 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.

The series index for this series of posts is here

The second Feature of the Day is SmartList Favorite Protection:

SmartList Options

This new feature allows a password to be set which will protect SmartList Favourites from being modified; when a user tries to modify a SmartList Favorite they will be prompted to enter a password.

The downside of this setting is that it is a global setting for all SmartList Favourites and not a password which can be set differently for each Favourite. This point was mentioned to Microsoft at reIMAGINE 2016.

Click to show/hide the MDGP 2016 R2 Feature of the Day Series Index

MDGP 2016 R2 Feature of the Day: SmartList Designer SmartLists in Advanced Lookups

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP 2016 R2; 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.

The series index for this series of posts is here

The first Feature of the Day is SmartList Designer SmartLists in Advanced Lookups:

SmartList Favorite

Continue reading “MDGP 2016 R2 Feature of the Day: SmartList Designer SmartLists in Advanced Lookups”

MS Connect Suggestion Sunday: Multiple Items To Vote Upon

Microsoft Dynamics GPI occasionally post suggestions from MS Connect and ask people to read the suggestion and cast their votes. Well, today I don’t have a single suggestion, or even two, but three suggestions for you to vote upon.

One Click Access to SmartList in Web Client

In the desktop client you can access SmartList from the area page, from the Microsoft Dynamics GP menu or via a toolbar icon. However, in the web client, only the first of these options are available and most users of Dynamics GP do not generally know of ways othet than the Microsoft Dynamics GP menu to start SmartList. This suggestion is to make SmartList available via one click in the web client.

Chris Dobkins submitted the suggestion and asked if SmartList can be made available by an icon next to the User Date in the web client; I don’t actually mind where the icon is, but agree that a single click method is required to make SmartList more accessible.

Vote here for this suggestion.

Hide Business Analyzer in Navigation Lists for All Users and All Lists

Business Analyzer can be a good way of seeing infromation from Dynamcis GP, but I have never liked BA being added to the navigation lists in Dynamics GP. This is why I added a recipe to the Microsoft Dynamics GP 2013 Cookbook which allowed it to be switched off globally with a SQL trigger. A Chris says, it would be better to have this functionality available via the GP client itself and allow users to switch it on when wanted.

Vote for this suggestion here.

End a corrupted Web Client Session

This is the third suggestion, suggested by Chris.

From time to time, a web client session will become corrupt. When this happens, you can reconnect to the session, but GP does not work (you may just gets lots of errors pop up in the status spinner, for example). When this happens, the user needs the ability to end the session from the screen that lists your active sessions and the tenants to which you have access to create new sessions. Users have the ability to remove their login from the activity table, and have had for many versions. Ending a hung web client session is the web client version of removing your user from the activity table so that you can log back into GP.

Vote for this suggestion here