Sometimes it is handy to have a deeper look on the HR.Net Picklists.
Picklists are set values of text data. Opposing to plain text fields, picklists can have only set number of values and can not be easily changed by end user. On user interface picklists can be presented as a drop-down menu or as a separate window. I prefer to have picklists to be as a select menus – this is more user friendly, but if the picklist has many options, better have it as a separate window.
Today, one of my tasks was to merge one picklist with the other one: Picklist A and picklist B, A had few options that are not in B. So I had to find all missing options and add them to B, then depricate A.
First I tried to use inbuilt tool to compare picklists, but that was not easy and I got lost easily (there were too many options to keep track off). So I made up a little sql script to find all the options belonging to one picklist.
DECLARE @txt varchar(50);
SET @txt= '%'+RTRIM('Picklist Name') + '%'; -- Put Picklist name here
select lang.shortdescription as [Picklist name], val.Storevalue as [Store Value], val.displayvalue as [Display Value]
from OC_PicklistValues val
inner join OC_PICKLISTLANGUAGES lang on lang.picklistid=val.picklistid
where lang.shortdescription like @txt
This would give you a list of stored values and display values of the required picklist.
Then you can save the resulting set into Excel, have another picklist info displayed and saved into Excel again.. and having 2 picklists together on one page it is easy to identify what changes are required to the resulting set.