Using SPFilterDropdown to Reorder Options in a Dropdown on a SharePoint Form
Many times when you have a dropdown column in your lists, you’d like to show the values sorted in a different order than alphabetically. Sure, you can use a Choice column and order the choices in any way you’d like. However, in the cases where you want the dopdown to be based on a Site Column which is a Lookup into a list, you can’t specify the sort order. I prefer using a Lookup column in almost all cases because it means that an admin (or any user with permission, really) can update the values in a list context rather than a column settings context. We also often end up storing other information about the values in the list, which we can’t do with a Choice column.
Here’s an example. I have a Site Column called Recommendation. It’s a pretty common idea: what’s our view on a particular stock or bond? We may want to recommend to our clients that the stock should be a Strong Buy, Buy, Hold, or Sell. If we add those values to a list as the source for a Lookup column, they will be ordered like this: [Buy, Hold, Sell, Strong Buy]. That’s not how we logically think of things, so it’s not great.
SPServices to the rescue! If we add a column to the source list which contains the sort order we want, we can make things look the way we need them to work. In my list below, I have a column called, logically enough, SortOrder. Note that it contains values which will sort the recommendation options the way we want.
Then, in the forms where I use the Recommendation column, I can add a call to the SPFilterDropdown function which takes care of things for me. No, the function name really doesn’t indicate that it could help with this – I built it to let you filter the options in a dropdown based on a column in the source list – but there’s a little trick you can use. If you specify a condition in your CAMLquery which always evaluates to true and specify a relationshipListSortColumn, the function will sort the values the way you want.
For my example above, I specify SortOrder as the relationshipListSortColumn, and set the CAMLQuery to give me all items where the Title does not equal an empty string. That will return all of the items, they will be sorted by SortOrder, and the dropdown will look just the way we want it to.
// If the Recommendation column exists, sort it as indicated in the source list
$().SPServices.SPFilterDropdown({
relationshipWebURL: "/", // The source list for the Recommendation site Column is in the root site
relationshipList: "Recommendations",
relationshipListColumn: "Title",
relationshipListSortColumn: "SortOrder",
columnName: "Recommendation",
CAMLQuery: "<Neq><FieldRef Name='Title' /><Value Type='Text'></Value></Neq>", // Get all values by specifying a non-blank Title
debug: true // Debug mode on while we're developing
});
Thanks for the reminder, I need to implement this frequently
One issue I encountered with this: the (None) option appeared in my dropdown even though it’s a required field. Is there something I need to do to avoid that?
Carlos:
It shouldn’t. What versions of jQuery and SPServices are you using? If they are current, please report it as a bug on the Codeplex site.
M.
jQuery 1.7.1 (the currently approved version at my company) and SPServices 0.7.1a. If that’s close enough to current I’ll go over and file the bug report.
File it. If nothing else, it’ll ensure that I look into it.
Thanks,
M.
Hi Marc, SPFilterdopdown is working fine but the drop down field has a “(none)” option. How can we remove the “(none)” option from the drop down list?
The fields is a “data not required field”
Srikanth:
I show the “(None)” option is the column is not required, just as SharePoint does. If you don’t want to see the “(None)” value, then make the column required.
M.
That is the problem. That column is not necessarily require data some times.
If we mark the column required and When editing in data sheet view, giving the message to fill the column.
We already have a ‘None’ option to show that the field has no data.
Parent Column : None (one of the drop down fields)
Child Column: 1. “None” 2. “(None)” (drop down data when None is selected in parent column)
In child column, I see “(None)” and also “None”.
any work around, which actually makes the field not required and eliminates the “(None)” option.
Thank you for your time.
Srikanth
The correct answer is to allow SharePoint to provide you with the “(None)” option rather than creating your own. Note that SPServices will have no effect in the Datasheet view.
M.
Good one but (none) option does not show “None” on the item field.
Yes,I understand SPServices has no effect in datasheet view.
Guess, I have to live with it now. I appreciate your time and thanks for replying.
Srikanth
You’re right, of course, but if you have the “None” value, your users will have trouble filtering in views and such. Just my $.02.
M.
I have not used SPServices before. Is there a way with this function to also filter out choices from the list? For example, if I added a column to my lookup list that indicates if a value is Active or Inactive. Can I only show those values that are Active?
Brendan:
Absolutely. If you look at the documentation for SPFilterDropdown, I give exactly that example.
M.
Hi Marc,
Thanks for all your help!
I’ve been using to put two conditions in the CAML Query for SPFilterDropdown and its not working. Am I supposed to use only one condition? Please help!!
When I use one of those conditions its working perfectly. Below is the code.
CAMLQuery: “NoActive”,
Thanks,
Srikanth
Srikanth:
Please see my response on the Codeplex Discussions here:
https://spservices.codeplex.com/discussions/517587
M.
Hi Marc,
Could I use the Created Date as the sort column?
Thanks!
Lana
Lana:
I can’t think of a reason why not. Have you tried it?
M.
I just need the Lookup to sort in descending order….what would be the best way for that?
Thanks!
Lana
Jana:
Just use this:
BTW, in the future, the best place to ask questions about SPServices in in the discussions on the Codeplex site.
M.