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.

Recommendations Datasheet View

Recommendations Datasheet View

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.

Recommendation Dropdown

Recommendation Dropdown

// 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
});

SPServices v0.7.1ALPHA13 Available for Testing

Faster and more furious, that’s what I always aim for.

SPServices

I’ve been working hard on this next release of SPServices. I’ve been lucky to have some down time between projects to do it. Yes, I’m happy to have the gap so that I can do more and do it better; I’ve been able to make even more significant improvements because I’ve has the focused time. It’s a great feeling to look at my old code and know that I can do a lot better now. This version of SPServices is going to be leaner and faster than ever before. As I tweeted the other day:

Alpha13, which I just posted is, I think, a good one to think of as RC1. Before I get too busy again with client work, I want to get it out there for some of you to test. If you are an SPServices user and can do some testing, especially regression testing, I’d really appreciate it.

There’s also some new stuff that I’ve built into the last few alphas:

  • Better error messages for SPCascadeDropdowns, SPDisplayRelatedInfo, and SPFilterDropdown. If you have debug mode on and the listName is incorrect, you’ll get a clearer message.
  • Improvements to the new SPXmlToJson function. All of the documented options now work as advertised and I’ve optimized the code quite a bit.
  • SPCascadeDropdowns and SPDisplayRelatedInfo now have a matchOnId option. If matchOnId is set to true, the ID of the Lookup column is used rather than the text value. This can be handy where the same text value means different things, like the place-name of Springfield, which can be found in 34 of the US States. By default the behavior of the function remains the same, of course.
  • New operations: Lists.ApplyContentTypeToList, Lists.CreateContentType, Lists.DeleteContentType, Lists.GetListItemChanges, Lists.UndoCheckOut, Lists.UpdateContentType, SiteData.GetWeb
  • Numerous (and I do mean numerous) improvements for efficiency.

Enjoy, and let me know if you have a chance to try it out.

SPServices v0.6.1ALPHA1 Released with New SPFilterDropdown Function

I wanted to do a quick post about SPServices v0.6.1ALPHA1. The one change in this ALPHA is to add a new function called SPFilterDropdown. I’ve had a lot of requests for this functionality lately, though it has come up on and off in the past as well.

Note that this is an ALPHA release, and I don’t recommend using it in any production environment yet. I wanted to let everyone know that SPFilterDropdown was available because of the requests for it and also in hopes of having some real world testing.

As described in the preliminary documentation, the SPFilterDropdown function allows you to filter the values available in a Lookup column using CAML against the Lookup column’s source list.  This function works with all three types of “dropdown”: <20 options (simple select), 20+ options (complex select), and multi-select.

<UPDATE date=”2011-04-03″>I just made some edits and posted v0.6.1ALPHA2. It’s embarassing to admit, but ALPHA1 had some significant bugs in it. Usually I think I do much better. Luckily only two of you downloaded it, so sorry @ToniFrankola and whomever the other person is. ALPHA2 should be good to rock.</UPDATE>

Here’s an example:

We’ll filter the lookup column called “Country” so that we only include those Countries where the Active column is set to ‘Yes’. In the screenshot below, you can see that Canada is not currently ‘Active’.

image

In the Sales Opportunities list, we have a lookup column called Country, which gets its values from the Title column in the Countries list.

image

If we add the call to SPFilterDropdown below…

$().SPServices.SPFilterDropdown({
  relationshipList: "Countries",
  relationshipListColumn: "Title",
  columnName: "Country",
  CAMLQuery: "<Eq><FieldRef Name='Active' /><Value Type='Boolean'>1</Value></Eq>",
  completefunc: null,
  debug: false
});

…then ‘Canada’ is filtered out of the available values because it is not ‘Active’.

image

Give it a try and report any issues in the Discussions on the Codeplex site.