
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
Subscribe To Our Newsletter
Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!
You have Successfully Subscribed!