Microsoft has come a decent ways to allow each user to customize their home login page. For a long time there has been the feature for the first time that a user connects, they are prompted to select their “Role” that determines how their out of the box homepage will look & feel.
With later versions of GP, there also came the ability to copy a user’s homepage settings from 1 user to another (along with every other setting from the original user).
However, Microsoft still hasn’t made it simple just to take an existing user’s quick links and apply them to another user. From using the interface, you’d have to copy ALL user settings from one user to another using the “Users Maintenance” window OR you’d have to login to a certain user’s account and set their quick links OR provide them with instructions on setting them themselves.
NO MORE!
Using the script below…
you can define a copy from user (TestUser1) & a copy to user (TestUser2) and either replace the existing quicklinks or add to.
--Declare Variables
DECLARE
@CopyFromUserID CHAR(15), @CopyToUserID CHAR(15), @Count INT, @Delete CHAR(1)
--Provide Values for Variables
--This is the section that MUST BE UPDATE
SELECT
@CopyFromUserID = 'TestUser1', @CopyToUserID = 'TestUser2', @Delete = 'N'
--Delete = Y means delete any existing quicklinks for copy to user, N = Append
Only
--This script will delete existing favorites if @Delete = Y
DELETE FROM
dbo.SY08140 WHERE userid = @CopyToUserID AND @Delete = 'Y'
--Start copy from one user to another
SELECT * INTO
#TempQuickLinks FROM sy08140 WHERE userid = @CopyFromUserID AND dsplname NOT IN
(SELECT DSPLNAME FROM dbo.SY08140 WHERE userid = @CopyToUserID) AND
@CopyToUserID IN (SELECT userid FROM SY01400)
SET @Count = 1
WHILE @Count <=
(SELECT COUNT(seqnumbr) FROM #TempQuickLinks)
begin
UPDATE
#TempQuickLinks SET SEQNUMBR = ISNULL((SELECT MAX(seqnumbr) + 1 FROM sy08140
WHERE USERID = @CopyToUserID),1)
INSERT
INTO SY08140 (USERID, SEQNUMBR, TYPEID, CmdID, CmdFormID, CmdDictID, DSPLNAME,
ScbTargetStringOne, ScbTargetStringTwo, ScbTargetStringThree, ScbTargetLongOne
,ScbTargetLongTwo,
ScbTargetLongThree, ScbTargetLongFour, ScbTargetLongFive)
SELECT
TOP 1 @CopyToUserID, SEQNUMBR, TYPEID, CmdID, CmdFormID, CmdDictID, DSPLNAME,
ScbTargetStringOne, ScbTargetStringTwo, ScbTargetStringThree, ScbTargetLongOne
,ScbTargetLongTwo,
ScbTargetLongThree, ScbTargetLongFour, ScbTargetLongFive FROM #TempQuickLinks
WHERE DSPLNAME NOT IN (SELECT dsplname FROM sy08140 WHERE USERID =
@CopyToUserID)
SET @Count = @Count
+ 1
END
DROP TABLE
#TempQuickLinks
--End copy from one user to another
--View ending results
SELECT * FROM
dbo.SY08140 WHERE userid = @CopyToUserID
Note: The script has only been used in limited GP 2015 R2 environments. As always, it is recommended to run the script in a development environment before deploying to production.
This script might be useful when…
– Setting up a new user and wanting to setup their quicklinks based off another user in a similar role
– Setting up a new module and wanting to add quicklinks to all users based on your model user (say when setting up Fixed Assets & wanting to add an external link to the new GAAP Financial Leases requirements)
– Setting up a new SmartView favorite for certain users but also wanting to add the quicklink to their homepage
Have feedback or have you expanded the user of this script? Let us know!
