Setting the Default Group/Default Layout Group for All Users in Infor CRM

In the Infor CRM web client, each user can define for each Entity level what the default group is, as well as the group used in the Lookups. This is normally set by each user from within the web client under the Tools…Options menu item, and then accessing the Groups tab.

If you want to globally set a default group for all users, you can edit the USEROPTIONS table. I thought I would provide a sample script showing how you can do this for all users. This script uses the T-SQL function shown here to generate a unique table ID. The first step is to update all users that currently have a Default Group and Default Layout setting. Finally, it then inserts rows for all non-retired users that do not currently have a setting for the Default Group and Default Layout Group.

In this script I am using SQL variable to declare the Entity level to use (ACCOUNT), and then the name of both the Default Group and Default Layout Group. In theory you would need to run this script after changing it for each level you want to pre-set groups for (like ACCOUNT, CONTACT, OPPORTUNITY, QUOTES, etc.) Remember that the group you are setting must also be assigned to all the users and be a favorite group (if using the latest level of Infor CRM).

-------------------------------------------------------------
--Functions to generate a Unique TableID
-------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sysdba].[FXintToBase]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [sysdba].[FXintToBase]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sysdba].[FXGetID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [sysdba].[FXGetID]
GO




Create Function sysdba.FXintToBase
(
@n bigint,
@base varchar(1000) -- you could hardcode this instead
)
Returns varchar(1000) As
Begin
 
Declare @baseNum int
Set @baseNum = len(@base)
 
Declare @code varchar(1000)
 
while (@n>0)
Select @code=substring(@base,@n%@baseNum+1,1)+isnull(@code,''), @n=@n/@baseNum
 
Return @code
End
Go

CREATE Function sysdba.FXGetID()
Returns varchar(1000) As
Begin
Declare @n bigint
Declare @base varchar(1000)
Set @base = '0123456789ABCDFGHJKLMNOPQRSTUVWXYZ'
 
set @n =
cast(datepart(mm,getdate()) as varchar(2)) +
cast(datepart(dd,getdate()) as varchar(2)) +
right(cast(datepart(yy,getdate()) as varchar(4))  ,2) +
cast(DATEDIFF(ms, convert(varchar(10),getdate(),101), getdate()) as varchar(10));
 
Return sysdba.FXintToBase(@n,@base)
End
GO

-------------------------------------------------------------
--End Functions to generate a Unique TableID
-------------------------------------------------------------




Declare @defaultLayoutValue varchar(96)
Declare @defaultGroupValue varchar(96)
Declare @entityLevel varchar(64)

set @defaultLayoutValue = 'Account:All My Accounts'
set @defaultGroupValue = 'Account:All accts'
set @entityLevel = 'ACCOUNT'

--Updates Account DefaultGroup entry for all users with an existing option 
UPDATE USEROPTIONS 
SET OPTIONVALUE = @defaultGroupValue, 
LOCKED = 'F',
MODIFYDATE=getutcdate(),
MODIFYUSER='ADMIN' 
WHERE 
UPPER(NAME) = @entityLevel AND 
UPPER(CATEGORY) = 'DEFAULTGROUP'

--Updates Account LookupLayoutGroup entry for all users with an existing option 
UPDATE USEROPTIONS 
SET OPTIONVALUE = @defaultLayoutValue, 
LOCKED = 'F',
MODIFYDATE=getutcdate(),
MODIFYUSER='ADMIN' 
WHERE 
UPPER(NAME) = @entityLevel AND 
UPPER(CATEGORY) = 'LOOKUPLAYOUTGROUP'



--Insert Account DefaultGroup entry for all users without an option yet.
Insert into sysdba.USEROPTIONS (OPTIONID, NAME, CATEGORY, USERID, OPTIONVALUE,
LOCKED, MODIFYDATE, MODIFYUSER, CREATEDATE, CREATEUSER)
select 
right('000000000000' +
sysdba.FXGetID() +
cast(rank() OVER (ORDER BY a.userid) as varchar(10))
,12) as OPTIONID,
@entityLevel,
'DEFAULTGROUP',
a.userid,
@defaultGroupValue,
'F' LOCKED,
getutcdate() Modifydate,
'ADMIN' modifyuser,
getutcdate() createdate,
'ADMIN' createuser
from
usersecurity a left join
sysdba.useroptions b on a.userid = b.userid and b.name='account' and b.category='DEFAULTGROUP'
where 
a.TYPE not in ('R','W') and
b.userid is null

--Insert Account LookupLayoutGroup entry for all users without an option yet.
Insert into sysdba.USEROPTIONS (OPTIONID, NAME, CATEGORY, USERID, OPTIONVALUE,
LOCKED, MODIFYDATE, MODIFYUSER, CREATEDATE, CREATEUSER)
select
right('000000000000' +
sysdba.FXGetID() +
cast(rank() OVER (ORDER BY a.userid) as varchar(10))
,12) as OPTIONID,
@entityLevel,
'LOOKUPLAYOUTGROUP',
a.userid,
@defaultLayoutValue,
'F' LOCKED,
getutcdate() Modifydate,
'ADMIN' modifyuser,
getutcdate() createdate,
'ADMIN' createuser
from
usersecurity a left join
sysdba.useroptions b on a.userid = b.userid and b.name='account' and b.category='LOOKUPLAYOUTGROUP'
where 
a.TYPE not in ('R','W') and
b.userid is null