External List with BCS & Search Filters

Business Connectivity Services let us to show our external data to SharePoint List, that external data may belong to any database or come through any web service, the most easily integrate able is Microsoft SQL Server database which is quite obvious as both are Microsoft technologies…

Moving forward, in this post we will see how can we make an External List and using Content Editor web part implement searching on the External List, so lets get started.

Making an External Content Type:

Using SharePoint Designer we will be making an External Content Type [which will then let us to make an External List], we will also on the way use Filter Parameters to support our searching feature.

1- Go to External Content Type section in SharePoint Designer

ExternalContentType

2- On the top, click New External Content Type

NewExternalContentType

3- Name the External Content Type [highlighted section] and then click on “Click here to discover external data sources and define operations

NewExternalContentType1

4- Now click Add Connection and you see a pop up like this:

image

We are selecting SQL Server in this case, now you will see a pop up like this,

image

Give all the necessary information here, you database server, database name and it will connect to your database showing your schema like this:

DBSchema

We will select our proper view or table we want to make External List for, and right clicking on it will show menu like Create All Operations etc. We will for now just create New Read List Operation and New Read Item Operation, because that will be useful in searching, you can create all operations and see the magic of Business Connectivity Services [BCS] in SharePoint.

5- On clicking New Read List Operation, will show a pop up with something like this:

ReadListOperation

Give proper name and move to Next Step, Filter Parameters. This section is important for searching as we will describe on what parameters the search will be made, click Add Parameters to add as many parameters you want to search on, the parameters actually refer to the fields in the View or Table you want searching on, this goes something like this:

FilterParams

On the right box Properties, select proper field you want the parameter on, in this case it is Access Number [one of the fields from view], now click (Click to Add) button in Filter property, this will open a pop up like this:

image

New Filter, name the filter according to the field – you can put any name [I put it to AccessNumber just for ease]. In Filter Properties, select Filter Type to Wildcard, Filter Field should be auto populated and check Ignore filter if Value is: and select Null. This is really important to make the search work, if the filter parameter value is nothing then it should ignore the filter and show all the results. We can add as many parameters as we like on different fields. I have added 4 parameters for now, the final result should be like this:

FilterParamsFinal

You can also specify whether it should be AND or OR between the parameters. After this click Finish.

6- Make New Read Item Operation, that will open up a pop up, just click Next Next Next three times and the operation will be ready for you. Now Save your External Content Type.

Your External Content Type is ready now, you can make an External List from it.

Making External List:

1- Come to External Content Type section in SharePoint Designer and Right click your External Content Type and click External List

ExternalListMaking

This will open a pop up for you to enter Name and Description for the External List:

image

Quickly you will get the External List ready, now navigate to your SharePoint website and look how this External List shows your items:

EmptyExternalList

You will see that the External List is empty, does that means the View did not return any results, NO! Your view may have results but the External List will show empty, because searching parameters are not set yet, what should you do now, we will update the External List aspx page to get this working with some JavaScript, lets do it Smile

Making the Search work:

1- In SharePoint designer go to External List here:

MoveToExternalList

Select it and you will be inside a detail window, here look into the Views section, you have to customize the default view for the External List, right click on the default view page, and click Edit File in Advanced Mode [or simply select it to go edit this page]:

EditViewListPage

2- Here you need to insert a Content Editor Web Part to allow some JavaScript and HTML to be embedded into the page, click after <ZoneTemplate> and in the Insert Tab you can see, Web Parts section like this:

image

Click on Web Part drop down and select Content Editor in Media and Content:

image

The page should look like this:

image

Now start typing something on “To add content, select this text and replace it by typing…” section and you will get the Content Tag ready.

3- In the content editor web part, insert some HTML Input Text box and an HTML button so that we can take input from user, in the Insert Tab find HTML section and select Input (Text) for textbox and Input (Button) for a button, as shown here:

image

Your content editor web part should look like this:

image

I have put it into a table for formatting, insert as many input textboxes as you have defined the filter parameters. This will generate some HTML in the Content Editor Web Part, go to Code mode to insert JavaScript to it.

<script type="text/javascript">
function ApplySearchFilters()
{
     try
     {
       var accessNumber = document.getElementById("txtAccessNumber").value;
       var url = window.location.href;
         if(url.indexOf("?") > 0)
         {       url = url.split("?")[0];
         }
 
         if(accessNumber.length > 0)
         {       url = url + "?FltAccessNumber=" + accessNumber;
         }
 
         window.location.href = url;
 
     }
     catch(ex)
     {}
 }
 
 function GetQueryStringValue(variable)
 {      var qs = location.search.substring(1, location.search.length);
 
     var args = qs.split("&");
     var vals = new Object();
 
     for (var i=0; i < args.length; i++)      {      var nameVal = args[i].split("=");
         var temp = unescape(nameVal[1]).split('+');
         nameVal[1] = temp.join(' ');
         vals[nameVal[0]] = nameVal[1];
     }
 
     return vals[variable];
  }
 
  function SetQueryStringValues()
  {
      try
      {
          var url = window.location.href;
          if(url.indexOf("FltAccessNumber") > 0)
          {
             var accessNumber = GetQueryStringValue("FltAccessNumber");
             document.getElementById("txtAccessNumber").value = accessNumber;
          }
      }
      catch(ex)
      {}
  }
 
  setTimeout("SetQueryStringValues();",100);
  </script>

These are the three functions that will help us to work our search,

  • ApplySearchFilters() function

In this function we have taken the textbox value and embed it with the current URL of the browser with a Query String parameter set to the value of the TextBox. As expected this function will be called on click of our Search button we gave in HTML.

  • GetQueryStringValue() function

This function will be used when the URL is redirected with all the Query String parameters we need to give, this function will get all the Query String values and add them into kind of HashTable with Key being the Query String variable name and Value being the Query String value. We will use this function to get specific value of a Query String parameter.

  • SetQueryStringValues() function

This function will also be used when the URL is redirected with all the Query String parameters and values, this will set all the textboxes with their respective values that the user had given before pressing Search button.

We have setTimeout and called SetQueryStringValues function every 100 milliseconds.

NOTE: For simplicity I have used only one Parameter that is AccessNumber, you can do the same for all the Parameters you have got.

Update your HTML Search button code and insert onclick attribute and set to ApplySearchFilters(), it should look like this:

<input onclick="ApplySearchFilters();" type="button" value="Search"/>

We have completed all the HTML and JavaScript work, now we will add Parameters and use those filters that we set when we made the External Content Type’s Read List Operation.

4- Select the XsltListViewWebPart which we had already on the page and you should see Options like this:

image

Select Parameters in the above to use the Query String parameters we set through JavaScript, clicking Parameters will open a Pop up like this:

image

Set it to the Query String parameter [variable] name you gave in JavaScript and set Default Value to “**”, it should look like above. We will use this parameter to be dynamic value for the Filter Parameter.

5- As we selected Parameters, select Finder from the top menu Options:

image

This should open a pop up like this:

image

Now click on “Click here to add a new clause…” and in Filter Name column you should see all the Filters that you added in the External Content Type’s Read List Operation. Select AccessNumber from it, and in the Value column you should see all the Parameters that we added just in the previous step.

image

Now replace [FltAccessNumber] to *{FltAccessNumber}* and press Ok, this should like this:

image

We have set all the things to get our search work, lets see the magic on External List Page.

See the magic to work

Now that you navigate to the page, you will see that by default the External List XsltListViewWebPart shows all the results returned by your View or Table, this should look like this:

image

Type in the AccessNumber and see if the search works:

image

The search looks great, and if you see at the URL of the browser after search, this should look like this:

http://server-name/Lists/All%20Students%20Blog/AllStudentsFRead%20List.aspx?FltAccessNumber=E-22001

That’s all Smile, I hope you enjoyed the blog…

Other content to read:

Searching External Data in SharePoint 2010 Using Business Connectivity Services

Happy SharePointing…

Tagged: , , , , , , ,

33 thoughts on “External List with BCS & Search Filters

  1. Spguru August 29, 2012 at 5:18 AM Reply

    Great article!
    We are using sandbox solution. Since XsltListViewWebPart is not available in sandbox, how can we built this solution

    • arsalan.khatri October 15, 2012 at 1:09 AM Reply

      Spguru, I guess the solution will not work as a sandbox solution – and I seriously haven’t give a thought to it.. If you have implemented anything, please share this over here!

  2. Seth October 4, 2012 at 4:10 AM Reply

    seems like my java script is not getting called; i put a javascript alert inside the ApplySearchFilters()method and it never pops the alert. where exactly within the WebPartPages:ContentEditorWebPart piece does the java script need to go?

    • arsalan.khatri October 15, 2012 at 1:12 AM Reply

      Seth,
      Make sure you went to the HTML source and put the javascript inside that, javascript should not be visible on the page and must not be treated as content… Also put the script at the start, before any input textbox or button…

  3. Alex October 11, 2012 at 9:14 PM Reply

    Great article! Works great! The only problem I am having is I am getting the error “This coloumn type cannot be filtered”. Do you have the same issue?

    • arsalan.khatri October 15, 2012 at 1:15 AM Reply

      Alex,
      On which column type you are getting this error? I tried with varchar types and it worked fine…

      • Alex October 15, 2012 at 8:07 PM

        My columns are varchar and int. I did some testing and the problem is as soon I add parameters inside the “finder” filtering I the error “This coloumn type cannot be filtered” when I try to filter a column. When I remove the parameters I can filter my columns.

  4. arsalan.khatri October 16, 2012 at 9:35 PM Reply

    Alex, the filtering describe above is also based on parameters… I think it will not be working for int types, can you make sure if you remove int type from filtering, does it works ?

    • Alex October 17, 2012 at 6:18 PM Reply

      I removed the int columns and still no go. No big deal, I added the columns that needed filtering in the search and removed the option to filter in the header. Thank you for this article!

  5. HariOM November 11, 2012 at 10:49 PM Reply

    awesome

  6. xplorer53 January 2, 2013 at 3:04 PM Reply

    Hi thank you very much for this!! This works mighty fine! One question though, wildcard search doesn’t seem to work for me. –I’m using this on normal list, and instead of “Finders” I used “Filters”, I tried each condition –“contains”,”equal”, “begins with” and even used them all at the same time with “OR”. Please help! Thank you in advance! :)

    • arsalan.khatri January 8, 2013 at 2:20 AM Reply

      You are most welcome, for a normal custom list “contain” should work but I will test it myself and possibly make a post for it!

  7. xplorer53 January 3, 2013 at 1:26 PM Reply

    Hi is it possible to use a drop down list instead of text box for the filter? The drop down list should contain the values from another list (lookup).

    • arsalan.khatri January 8, 2013 at 2:22 AM Reply

      Yes you can use drop down instead of textbox but for these kind of requirements we usually go for a Visual Web Part, where dynamically load drop down with another list and on search button through Response.Redirect append the values in Query String! Filters should be configured the same way for List View

  8. Aticus January 23, 2013 at 7:11 PM Reply

    Hi! Real great article! Saved me a lot of time and trouble!
    Just one remark regarding the Java Script:
    You use a “?” to split the Query in ApplySearchFilters() function.
    But in GetQueryStringValue() function you use “&” to look for splits.
    Shouldn’t these two be the same?

    • arsalan.khatri January 26, 2013 at 4:04 PM Reply

      Aticus,
      For understanding JavaScript, you can alert the different variables used. To answer your question: No they shouldn’t be same… Why?

      Because in ApplySearchFilters(), we are removing anything that is already in the query string of the URL, so we are using “?” since any query string will start from “?” for first variable.. And since we don’t want any Query String to appear, we are neglecting the Query String part!

      But in GetQueryStringValue, we are getting all the possible Query String parameters that are in the URL so “&” is used… and that is only searched in the Query String part of the URL not the complete URL… I hope this answers your question!

  9. Greg March 3, 2013 at 1:23 PM Reply

    Hi Arsalan – wow I’ve been try to implement this for a while now! thanks for posting this tutorial. out of curiosity, do you any tutorials on filtering a date column? I have a column called shipped date that im trying to filer using a start date and an end date. I tried playing with the filters and applying as in your guide…but it doesnt seem to work.

    • arsalan.khatri July 31, 2013 at 10:44 PM Reply

      Hello Greg,

      Sorry for replying you late, I have been occupied with other things.. I am afraid, I have only tried the above solution with single line text fields.. I will try to implement for Date column and let you know my experience!

  10. Marilise March 5, 2013 at 10:14 PM Reply

    Thanks for your article even if in my SharePoint site the search button does not run the search function. The Url of the browser after search doesn’t change as you wrote at the end of your article. What could be the problem?

    • arsalan.khatri July 31, 2013 at 10:47 PM Reply

      Marilise,

      Have you put the JavaScript correctly..? Debug the JavaScript using debugger and try to find out the issue..

  11. Sean March 14, 2013 at 1:49 AM Reply

    can you show the code for the table formatting for the input fields?

  12. btjustice March 19, 2013 at 2:43 AM Reply

    I discovered that SharePoint Designer 2010 appeared to not set the id=”txtAccessNumber” value for the input box for the function document.getElementById(“txtAccessNumber”). Make sure that your input box looks something like:

    • arsalan.khatri July 31, 2013 at 10:50 PM Reply

      btjustice,

      Seems like you tried to write HTML in your comment and WordPress cleaned it up..

      Instead of using “<” or “>”, please use the ampersand (&), followed by the letters (either “lt” for Less Than (<), or “gt” for Greater Than (>)), then a semicolon.

  13. ślub May 30, 2013 at 1:17 PM Reply

    What’s Happening i’m new to this, I stumbled upon this I’ve found It positively helpful and it has aided me out loads. I’m hoping to
    give a contribution & aid other customers like its helped me.
    Great job.

  14. Rox July 8, 2013 at 5:53 AM Reply

    BTJustice, it seems that you have left out a diagram or a link or more code…

    Can you please provide more details as I cannot get the Search button working.

    Cheers,

  15. Rox July 9, 2013 at 3:53 AM Reply

    Arsalan,

    would you be able to let me know the code to place along with the Javascript for the Search button. I only have one field box, but the search button nor Javascript that I copied seemed to work.

    Thank you.

    • arsalan.khatri July 31, 2013 at 11:17 PM Reply

      Rox,

      The Search button HTML should be like:
      <input onclick=”ApplySearchFilters();” type=”button” value=”Search”/>

      I will post complete HTML for table since others are looking for that too!

  16. 公司清潔 July 27, 2013 at 6:04 AM Reply

    I’m not sure where you are getting your info, but good topic. I needs to spend some time learning more or understanding more. Thanks for excellent info I was looking for this info for my mission.

  17. YTN August 29, 2013 at 1:12 PM Reply

    This solution prevents the normal filtering on columns to work – as soon as the filter parameter is applied to the view the list column headers will display “This coloumn type cannot be filtered” when trying to filter.

    • arsalan.khatri August 30, 2013 at 4:18 PM Reply

      @YTN, this is normal behaviour when you are adding your custom filters.. Once you add custom filters the xslt list view filtering will not work!

  18. YTN September 2, 2013 at 12:52 PM Reply

    I actually found a solution to this. It seems the column filter functionality adds the token without parsing it. So if you have a filter defined as i.e. “{filtername}”, this token string will literallybe sent to the BCS as the filter value, including the curly braces.
    So you have to take actions for this by checking in your BCS code, or in case of SQL just add extra clause like this (last OR added compared to default generated SQL):
    WHERE ((@filtername IS NULL) OR ((@filtername IS NULL AND [] IS NULL) OR [] LIKE @CprNr) OR (@CprNr LIKE ‘%{filtername}%’ ) )

  19. Nifilo September 5, 2013 at 8:28 PM Reply

    Hi, have you test this code on Sharepoint 2013? It seems that the parameter doesn’t take the querystring from the url.

    • arsalan.khatri September 29, 2013 at 4:34 PM Reply

      I haven’t tested the code on SharePoint 2013.. Once I do, I will make another post on that..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: