Wednesday, April 21, 2010

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/

3 comments:

CriticalSock said...

I have used this script to copy one users shortcuts to another user with great success, thank you!

I now have a user who only needs one shortcut copied from another user but needs to keep his existing shortcuts. Any advice on how to do this?

Mariano Gomez said...

Yes, take note of the DEX_ROW_ID for the shortcut you want to copy and include the restriction as part of the WHERE clause of the SELECT statement:


... AND DEX_ROW_ID = XXX;

Anonymous said...

I'm running GP2010 and just tried this script and it didn't work , can you confirm that this will work for GP2010 I really could use it to copy shortcuts.

Will this only copy for the navigation bar , what about the menu bar?