Filtering on a Value in a Multi-Select Lookup Column

ByMarc D Anderson

A great way to manage the values for a Site Column in SharePoint is to put the values into a list and then use the Lookup column type to grab the values.  When you allow multiple selections in the lookup column, what is stored looks something like this: 

value1;value2;value3;value4

If you would like to then check in a DVWP to see if a value was selected, it’s not as simple as you’d like.  If your values are truly unique and cannot be nested (e.g., School and Schoolhouse both contain the string School), then you can use contains.  Otherwise, you’ll want to tighten things up a bit by doing some fancier matching.

The possible ways that we can have a match are:

  • The multi-select string matches the value exactly.  This is the case when there has been only one selection made, and it is the one that we want.
  • The multi-select string begins with our value followed by a semicolon (value1;)
  • The multi-select string has the target value in the middle, therefore surrounded by semicolons (;value2;)
  • The multi-select column contains the value at the end, preceded by a semi-colon (;value4)

To test for each of these conditions, you just need a little logic in your XSL.  Here’s an example that works when you want to see if MultiSelectColumn contains DesiredValue.

<xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row[
  @MultiSelectColumn= $DesiredValue or
  starts-with(@MultiSelectColumn, concat($DesiredValue, ';')) or
  contains(@MultiSelectColumn, concat(';', $DesiredValue, ';')) or
  substring(@MultiSelectColumn, string-length(@MultiSelectColumn) -
   string-length($DesiredValue), string-length($DesiredValue) + 1) =
   concat(';', $DesiredValue)
 ]"/>

Depending how you will need to do the test, consider creating a template that you can pass the multi-select string and the desired value, passing back the results of the test.  You can store this template in a separate file and then use the template in many DVWPs by including it with xsl:import.

Technorati tags: , ,

Similar Posts

15 Comments

  1. Hi Marc,

    Thanks for the post.

    I am looking for a similar solution .After converting a listView to XSLT I grouped thr list view using 3 columns which works great. But out of the 3 group by columns I am using, one of these columns has multiple values.. So what should I do so that I can split the multiple values of the column and then make the item appear under each value when grouped..

    like:

    If list contains the following fields =
    [Title] [Skills]
    Joe Microsoft, Oracle, Lotus
    John Microsoft, Tivoli
    Jack Microsoft, Lotus, Adobe

    then I want to view the same list but as :

    +Adobe (1)
    Jack
    +Lotus (2)
    Joe
    Jack
    + Microsoft (3)
    Joe
    John
    Jack
    + Oracle (1)
    Joe
    +Tivoli (1)
    John

    This is what I did to group by:
    http://techtrainingnotes.blogspot.com/2008/11/sharepoint-group-by-on-more-than-2.html

    After following the above link I was able to group by 3 columns but now I need to split the values of a column.

    Even I tried :

    http://www.endusersharepoint.com/2010/04/15/sharepoint-group-by-a-column-with-multiple-values/

    I think now I have to combine the functionality explained in both the posts and I need to update the xslt something like you explained above.

    Any help would be appreciated.

    Thank you.

    Harish.

    Reply

      1. Hi Marc,
        Thanks for the quick reply. Till now upon your advice I started working on nodesets for the column which has multiple values ( Only 1 column out of three has multiple values) , So if I am not wrong i need to change the nodeset value only for this group isn’t it?

        Do I need to create another list with all the values of that lookup column and create a linked data source or is it possible to use the same column name in the nodeset and split the column value and then group by each value….
        1)

        2)

        3)
        PlantPlant

        I am thinking like these ate the places where I need to make modifications…
        Can you please help me what should I need to change in the nodesets…..

        I apolozise for copying everything here….

        Harish.

        Reply

  3. Hi Marc,

    I have used this approach in one of my Dataform webpart based on different filters. From the same list I was getting 3 different subsets of rows, for 3 different filters. Is there a way that I can combine 3 sets of resultset into one set and show unique values as final result.

    Eg: My filter is 3 different values F1,F2, F3. I need to filter DVWP for all this 3 filters and display a single resultset without duplicates in sorted order.
    Now I was calling the above template thrice so I have 3 resultsets, is there a way to combine them?

    Many thanks in Advance for any ideas on achieving this.

    Regards,
    mswin

    Reply

    1. I think what you’ll need to get to is a very complex compound filter on the rowset. Is this the same question I got from you as Jayakumar this morning?

      M.

      Reply

      1. Hi Marc,

        Yes its similar to the above requirement.

        Let me explain you with some more additional details:

        Here is the structure of my list

        LIST A

        Row : Column 1
        ———————
        1 | Blue;Red;Green
        2 | Red;Blue
        3 | White;Yellow;Green
        4 | White;Violet

        LIST B

        Row : Column 1
        ———————
        1 | Blue;Red
        2 | Red;White;Yellow
        3 | Green;White
        4 | Yellow;Blue;Red
        5 | Blue;Red;Green;White;Yellow

        Now i want to filter this in dataview;

        Currently i am using List A source as a url parameter and List B is the data view as such. I want to filter the url parameter of List A , Eg: Url Parameter : $URLParamater=Blue;Red;Green (It’s my input)

        I want to filter the above input to my LIST B data source it’s my dataview.

        This is my below code which i am using currently

        Legend:

        Code:
        $CheckValue //$URLParamater
        $MultiSelectColumn //List B Column 1

        Code:
        /dsQueryResponse/Rows/Row[($MultiSelectColumn = $CheckValue or starts-with($MultiSelectColumn, concat($CheckValue, ‘;’)) or contains($MultiSelectColumn, concat(‘;’, $CheckValue, ‘;’)) or substring($MultiSelectColumn, string-length($MultiSelectColumn) – string-length(concat(‘;’, $CheckValue)) + 1, string-length(concat(‘;’, $CheckValue))) = concat(‘;’, $CheckValue) or $CheckValue=”)]

        //This code i got it from your blog.

        The above code is working fine if i pass it as a single value e.g : If my URL Parameter is “Blue” then this filter works fine, but if my url parameter has more than one value like e.g “Blue;Green” its not filtering.

        LIST B should be filtered in such a way that, it should display all the values which is relevant to the provided input.

        e.g 1:
        In other words,if the input is (Blue;Green), and List B (Blue;Green) | (Green;Yellow;Red)| (Blue;White) as three rows

        I need the below result set as my output in data view:

        Blue;Green
        Green;Yellow;Red
        Blue;White

        e.g 2:
        In other words,if the input is (White;Violet), and List B (Blue;Green) | (Green;Yellow;Red)| (Blue;White) as three rows

        I need the below result set as my output in data view:

        Blue;White

        Input values will differ, Depends on the list A.

        It might have two color or three colors or more than 6 in some cases.

        I need my LIST B should be filtered accordingly based on the provided url input parameter.

        I need my result should be act as ‘LIKE’ operator. It should filter the value irrespective of the ‘;’ separated colors(Input Parameter).

        as i stated in my above example, the exact matches of each colors in list a (Row1) should be filtered across the values which is present in the list b(should check all the rows in list b).

        i am planning to split this input values and filter it accordingly. but split function is not their in share point designer xslt.

        Let me know if any simple approach or work around to achieve this functionality using share point OOTB.

        If you can provide some code snippet for this it will be more helpful.

        Thanks in advance

        Jay

        Reply

        1. Jay:

          What you’re trying to do is both more complex and different than what I was doing in my example here. You can use my XSL as a starting point, but you’ve got some more work to do to get where you want to be.

          My code works with one value, checking against a set of values. It sounds like you want to check one set of values against another set, getting a ‘hit’ if there is any match at all. Basically a big OR test. You’ll need a template to separate out the first set into its components, use my code with each, and OR the results to determine what to show.

          M.

          Reply

          1. Hi Marc,

            As you stated above, already i have started working on that, but i am stuck up with the split case and i have written my own custom split function and now what ever the input i am passing e.g: blue;green;red

            It will be split like blue and then green and then red. If i pass this split value to your xsl template it will work fine.

            But i stuck up in the middle that i don’t no how to consume those split values and pass that to your template. i need some kind of for each kind of stuff. could you please help me out with a snippet? if possible.

            Thanks

            Jay

            Reply

  4. Hi Marc,

    I have made a bit progress on this. I am splitting my first set of multiple values and for each of that splitted value I am calling the above mentioned template. So if I have 3 values in the first set which I have to filter against another list which has Multi value lookup, I am getting 3 resultsets.
    Now I have 3 subsets of resultsets which has duplicate rows if my second list matches more than one value in the first set filter. So is there a way that I can build a single final resultset with unique rows from this 3 subsets in DVWP XSLT. I would also need to be able to apply sorting and paging for the same resultset.

    Many thanks In Advance for any suggestions on achieving this.

    Reply

    1. Hi mswin,

      I am also doing the similar kind of approach, i can able to split the values using a customize split function, could you help me by providing your code for splitting the values and looping through with the above template. which will help me in my deveoplment.

      Thanks

      Jay

      Reply

      1. Guys:

        As I recommended above, go post your code over at EUSP. You’re going to have a devil of a time doing it here. I’ll watch for it.

        M.

        Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.