‘Queries’ Category

NOTE: This example is not recommended for novice users. You must have access to the SBO SQL Database and be comfortable with updating your production database. Updating your production database directly could void your SBO warranty and/or service agreement. Please consult with a DBA before proceeding. Updating queries cannot be run within the SBO query tool.

If you need to have all of your users have the same screen layouts (e.g., the same columns in the same order within the Purchase Order) you can use this qery to quickly copy the settings from one user to another. Obtain the UserID numbers with this query, run it in SBO or another tool:

SELECT UserID, U_Name FROM OUSR ORDER BY U_Name

I recommend that you create a dummy user name and set your screen formats for a group of people, just like you should when setting authorizations.

Declare @target as smallint
Declare @source as smallint
Declare @form as nvarchar(20)
/* Change this to the Target UserID (number from OUSR) */
Set @target = 2
/* Change this to the Form number obtained from the Sys Info Line at bottom of SBO */
Set @form = 142
/* Change this to the Source UserID */
Set @source = 22
/* Removes the temp table if it already exists. */
/* temp_settings is created and destroyed only for this operation */
if exists (select 1 from sysobjects where name = ‘temp_settings’)
Begin
drop table temp_settings
End
/*Copy from the source ID for a specific form */
Select * into temp_settings from CPRF where usersign = @source and FormID = @form
/* Remove the target info for the form and user */
Delete from CPRF where usersign = @target and FormID = @form
/* Change the UserID in the Temp table to the new user ID */
Update temp_settings Set usersign = @target
/* Put the information back into the SBO forms table */
Insert into CPRF Select * from temp_settings
/* Removes the table just in case! */
Drop Table temp_settings

Thanks to Ed Monk of SBONotes.com