SQL – Copying Microsoft Dynamics GP Navigation Bar shortcuts from one user to another

I have seen this question come up multitude of times on public newsgroups and forums. In fairly large Dynamics GP installations, Navigation Bar shortcuts tend to be deployed in a standard fashion for groups of users at a time, this is, to ensure all users have the same set of options to perform their functions, or access to company documents, etc. With this in mind, it is very common to setup one user’s shortcuts and wanting to replicate these across other Dynamics GP user accounts. Unfortunately, there isn’t a facility in GP to allow this.

The following T-SQL script allows you to copy the Navigation bar shortcuts from one user to another:

CopyNavShortcuts.sql



1 : use DYNAMICS;
2 : GO
3 :
4 : declare @sourceUser char(20); set @sourceUser = 'LESSONUSER2';
5 : declare @destntUser char(20); set @destntUser = 'LESSONUSER1';
6 :
7 : if exists(select * from SY01990 where ScbOwnerID = @destntUser)
8 : delete from SY01990 where ScbOwnerID = @destntUser;
9 :
10: insert into SY01990 (
11: ScbGroupType,
12: ScbOwnerID,
13: ScbNodeID,
14: ScbParentNodeID,
15: ScbShortcutType,
16: ScbSubType,
17: ScbDisplayName,
18: ScbShortcutKey,
19: ScbTargetStringOne,
20: ScbTargetStringTwo,
21: ScbTargetStringThree,
22: ScbTargetLongOne,
23: ScbTargetLongTwo,
24: ScbTargetLongThree,
25: ScbTargetLongFour,
26: ScbTargetLongFive,
27: ScbCompanyID)
28: select
29: ScbGroupType,
30: @destntUser,
31: ScbNodeID,
32: ScbParentNodeID,
33: ScbShortcutType,
34: ScbSubType,
35: ScbDisplayName,
36: ScbShortcutKey,
37: ScbTargetStringOne,
38: ScbTargetStringTwo,
39: ScbTargetStringThree,
40: ScbTargetLongOne,
41: ScbTargetLongTwo,
42: ScbTargetLongThree,
43: ScbTargetLongFour,
44: ScbTargetLongFive,
45: ScbCompanyID
46: from SY01990
47: where ScbOwnerID = @sourceUser
48: GO

You can enhance this script by adding your own transaction commit and rollback features or use as a mechanism to ensure users do not change their shortcuts based on a template user ID. The options are many.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: