Tips and Tricks from R.J., the SalesLogix Consultant

The Down and Dirty of SalesLogix CRM Picklist Lists

Tweet

 

We often get requests for a list of values in the SalesLogix CRM Picklists. There are some neat Crystal Reports and dump-to-Excel-workbooks kinds of utilities out there, but here’s how to do it the old fashioned way: via SQL Statement and T-SQL.

SQL Statement (from R.J. Ledger):

--Query Picklist

Select p1.TEXT as PickListName,p2.TEXT as PickListText,
p1.USERID as PickListOwner,p2.userid as PickListRecordOwner,UI.USERNAME,p2.ITEMID
From sysdba.PICKLIST p1
Inner Join sysdba.PICKLIST p2 ON (p1.ITEMID = p2.PICKLISTID)
Left Join sysdba.UserInfo UI ON (p2.UserId = UI.USERID)
-- Where p2.userid <> 'ADMIN' -- restrict to User entries only (the bad ones!).
Group By p1.TEXT,p2.TEXT,p1.USERID,p2.userid,p2.ITEMID, UI.USERNAME
ORDER BY P1.TEXT, p2.TEXT
 
T-SQL (Stored Proc if needed)

SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [sysdba].[usp_GetPicklistsByPicklist]
AS
BEGIN
--- run as SQL Only start HERE!
--- We need to make containers for the Cursor Info
DECLARE @PicklistID char(12), @PicklistName varchar(64)

DECLARE PKL_CURSOR Cursor FAST_FORWARD --- We need to name the cursor!
FOR – and run the SQL to populate the cursor.
Select ITEMID as PICKLISTID, TEXT
From PICKLIST
WHERE PICKLISTID = 'PICKLISTLIST' – did you know that’s the master picklist?
ORDER BY TEXT

OPEN PKL_CURSOR

Fetch NEXT FROM PKL_CURSOR INTO @PicklistID, @PicklistName
While @@FETCH_STATUS = 0
BEGIN –get the picklist values for each picklist.
SELECT @PicklistName as "PickList", ID, TEXT, SHORTTEXT
FROM sysdba.PICKLIST
WHERE PICKLISTID = @PicklistID
ORDER BY ID, TEXT

Fetch NEXT FROM PKL_CURSOR INTO @PicklistID, @PicklistName
END
--- housekeeping.
CLOSE PKL_CURSOR
DEALLOCATE PKL_CURSOR
--- run as SQL Only then END HERE!

END

I didn’t include the output from either statement as it’s several thousand lines long.
Try it, you’ll like it.