none
List filter wildcard search on multple columns .. LIKE

    Question

  • RE: List filter wildcard search on multple columns .. LIKE


    I'm not sure if there is a native MOSS 2007 option that will do this, but I'd like to filter a list by a single text filter only I want the filter to return all rows where three specific columns have a particular string in any part of the column not just the an equivalent value for a specific column. 
     
    So for example:

    Say I search for "team"

    It would return a row where column a: "Team Builder"
    or
    Column B had "Purple Team Colors"
    or
    Column C had "Teamster"

    What's the best way to attack this in Sharepoint?

    Thanks for any help or information.
    Friday, October 10, 2008 2:14 AM

Answers

  • Hi,

    The code sample I provided above would be executed somewhere in the context SharePoint, although mostly likely from a custom web part as you have written in your most recent post. 

    Making it dynamic is a little trickier.  You have two options, 1) you can ask for this information above your list view from some text boxes.  You would then have a button which would submit the filter request.  Upon reload, your code above would read these values and construct the query.  2) Using a web part connection, you can enable your web part to connect to a text filter web part.  These values would then be received by your web part.  This second option is much more difficult, so I might suggest option 1 for now.

    Let me lastly add that since you are writing your own web part (i.e. inheriting from the WebPart base class), you would not need SmartPart in this case.  SmartPart is intended to be a web part wrapper around a web user control (.ascx file).  Since you are writing an actual web part and not a user control, it is not needed.


    Randy - http://sharepointhawaii.com/randywilliams
    • Marked as answer by o365spo Saturday, October 11, 2008 12:26 AM
    Friday, October 10, 2008 7:29 PM

All replies

  •  

    <Where><Or><Or><Contains><FieldRef Name="A"/><Value Type="Text">Team</Value></Contains>
    <Contains><FieldRef Name="B"/><Value Type="Text">Team</Value></Contains></Or>
    <Contains><FieldRef Name="C"/><Value Type="Text">Team</Value></Contains></Or></Where>

    Friday, October 10, 2008 6:21 AM
  • Hi,

    You can accomplish this via an SPQuery object and applying this in a GetItems method call.  It would look like this sample code below.  Just substitute in your column names and literal values. 

    SPQuery qry = new SPQuery();   
     
    qry.Query = "<Where><Or><Contains><FieldRef Name='col1' /><Value Type='Text'>abc</Value></Contains><Contains><FieldRef Name='Col2' /><Value Type='Text'>def</Value></Contains></Or></Where>";   
    qry.ViewFields = "<FieldRef Name='ID' /><FieldRef Name='col1' />";   
     
    SPListItemCollection items = list.GetItems(qry);  
     

    Hope this helps.

    Randy - http://sharepointhawaii.com/randywilliams
    Friday, October 10, 2008 6:26 AM

  • Sorry Sharepoint Noob here.

    What I'm not clear on is where exactly to apply this code.

    - a smartpart or a custom webpart?
    - would I still be using text filter webpart on my page and if so how does that connect to the code in new WebPart?

    I've been looking for a complete guide how to do this.

    Thanks.
    Friday, October 10, 2008 11:18 AM
  • Dumb question, could the below code be a user control that could be referenced by Smartpart or it a stand alone WebPart?

    using System;
    using System.Runtime.InteropServices;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Serialization;
    using Microsoft.SharePoint;
    using Microsoft.SharePoint.WebControls;
    using Microsoft.SharePoint.WebPartPages;

    namespace MyNamespace
    {
        public class myWebPart:  System.Web.UI.WebControls.WebParts.WebPart
        {
            public override void RenderControl(HtmlTextWriter writer)
            {
                ListViewByQuery view = new ListViewByQuery();
               
                SPWeb currentweb = SPContext.Current.Web;
                SPList list = currentweb.Lists["Documents"];
                view.List = list;
                SPQuery query = new SPQuery(view.List.DefaultView);
                query.ViewFields = "<FieldRef Name='A'/> <FieldRef Name='B'/> <FieldRef Name='C'/>";
                query.Query = "<Where><Or><Or><Contains><FieldRef Name="A"/><Value Type="Text">Team</Value></Contains>
    <Contains><FieldRef Name="B"/><Value Type="Text">Team</Value></Contains></Or>
    <Contains><FieldRef Name="C"/><Value Type="Text">Team</Value></Contains></Or></Where>";
                view.Query = query;
                EnsureChildControls();
                view.RenderControl(writer);
                RenderChildren(writer);
            }
        }
    }

    And how can I make it dynamic so I can pass/connect the value of my text filter webpart into this Webpart? Or should my text box be in the WebPart I develop. If you have any complete examples or links to good guides that would be great.

    Thank you.

    Friday, October 10, 2008 11:30 AM
  • Hi,

    The code sample I provided above would be executed somewhere in the context SharePoint, although mostly likely from a custom web part as you have written in your most recent post. 

    Making it dynamic is a little trickier.  You have two options, 1) you can ask for this information above your list view from some text boxes.  You would then have a button which would submit the filter request.  Upon reload, your code above would read these values and construct the query.  2) Using a web part connection, you can enable your web part to connect to a text filter web part.  These values would then be received by your web part.  This second option is much more difficult, so I might suggest option 1 for now.

    Let me lastly add that since you are writing your own web part (i.e. inheriting from the WebPart base class), you would not need SmartPart in this case.  SmartPart is intended to be a web part wrapper around a web user control (.ascx file).  Since you are writing an actual web part and not a user control, it is not needed.


    Randy - http://sharepointhawaii.com/randywilliams
    • Marked as answer by o365spo Saturday, October 11, 2008 12:26 AM
    Friday, October 10, 2008 7:29 PM
  • thank you.

    I used U2U to generate the CAML and was testing this very simple piece of code:

     

    using System;  
    using System.Runtime.InteropServices;  
    using System.Web.UI;  
    using System.Web.UI.WebControls;  
    using System.Web.UI.WebControls.WebParts;  
    using System.Xml.Serialization;  
    using Microsoft.SharePoint;  
    using Microsoft.SharePoint.WebControls;  
    using Microsoft.SharePoint.WebPartPages;  
     
    namespace WebPartFilter  
    {  
        public class myWebPart:  System.Web.UI.WebControls.WebParts.WebPart  
        {  
            public override void RenderControl(HtmlTextWriter writer)  
            {  
                ListViewByQuery view = new ListViewByQuery();  
                  
                SPWeb currentweb = SPContext.Current.Web;  
                SPList list = currentweb.Lists["list2"];  
                view.List = list;  
                SPQuery query = new SPQuery(view.List.DefaultView);  
                query.Query = "<Query><Where><Contains><FieldRef Name='_x0066_2' /><Value Type='Text'>abc</Value></Contains></Where></Query>";  
                query.ViewFields = "<FieldRef Name='_x0066_2' />";      
                view.Query = query;  
                EnsureChildControls();  
                view.RenderControl(writer);  
                RenderChildren(writer);  
            }  
        }  


    It displays that one column but for all rows so no filtering.

    Does anybody know what I might be doing wrong?

    Thanks.

    Saturday, October 11, 2008 11:27 AM