SalesLogix Picklist management and tips

All the picklists and the picklist items are stored in sysdba.Picklist table. To get a list of all picklists:

select * from sysdba.picklist where picklistid='PICKLISTLIST'

The ShortText field seems to store settings code for each picklist. ItemID is the identifier for picklist. To get all items of a particular picklist:

select * from sysdba.picklist where picklistid='k6UJ9A00032L'

Worth noting is the 'USERID' field. If the picklist's setting is to allow user edit (Users cannot edit items is unchecked), the USERID field will have the user ID of the user who inserts the item, and this item will be visible for that particular user only. Only USERID='ADMIN' are shared by all users. Additionally, non-admin user can only edit/delete their own items.

There was a request that a business user wanted to be able to modify a picklist herself without asking ADMIN to do it and her change should be made available to all users. What I did is to insert a INSERT Trigger on Picklist table:

Declare @userID char(12), @itemID char(12)
SELECT @userID = USERID, @itemID=itemID FROM INSERTED

IF @userID = 'U6UJ9A00000Q'
BEGIN
UPDATE sysdba.PickList SET USERID='ADMIN' WHERE ITEMID=@itemID
END

The outcome is that any picklist item this user ('U6UJ9A00000Q') adds will be available to all users (after a refresh of course). However, since the USERID is changed to ADMIN, this user can't change/delete afterward.

The forms to manage the picklist are not listed in Plugin window. I assume that is the part that Sage doesn't allow you to customize. To give total control of picklists to a non-admin user, customized forms are needed. Basically you have to duplicate what SalesLogix already has, then add your function on top of that.