Sort Values in Pick Lists

2008-10-07T16:42:00Z

Sometimes, it is the little things that make a difference. If you talk to our friends at Microsoft, they are pretty adamant about it…Pick lists in CRM should not be too big. Admittedly, they are a little vague about how big is “too big” but the message is pretty consistent. Pick lists with too many values can degrade performance, create maintenance issues, and the make the user experience less than satisfying if they have to scroll through dozens or even hundreds of items to find the values they need.

However, in real life there are some legitimate reasons why pick lists get pretty big and that won’t change in the near future. In working with pick lists recently, we encountered a situation that seemed like an error in the system and it took some investigating (and frustration) to find the way out. In this case we had added a large number of values to a pick list and sorted the valued in ascending order (aphabetically).

We saved the list, added the field to the form, published the entity, and everything worked as expected. We then went to create an Advance Find view to allow users to query the field.

However, in building the query, there seemed to be numerous missing choices in the Available Values list. In this case we were looking for “e-commerce” but it seemed not to be one of the available choices.

After some investigation, it turns out that the values are sorted differently in the look up field than they are in the attribute field.

The Look Up field sort is case sensitive so the sort order is as follows:

First - Values starting with non-alpha characters.
Second - Values that start with upper case letters.
Third - Lower case letters are at the bottom of the list.

When we had entered the values in the attribute pick list, some of the values were entered starting with lower case letters, some legitimately (e-Commerce) and some unintentionally.

This is not a big deal when your pick lists contains a relatively few number of values and you can visually search the choices. However when the number of values gets larger you tend to rely on the sort to help you find the values you are seeking. In that case it is helpful to know that some of choices may be relegated to the bottom of the list depending on how they were entered into the attribute.

As we said when we started…sometimes it is the little things.

Bill Goergen

Sort Values in Pick Lists