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/