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

2- On the top, click New External Content Type

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

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

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

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

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:

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:

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:

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:

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

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

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

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

Making the Search work:

1- In SharePoint designer go to External List here:

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]:

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:

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

The page should look like this:

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:

Your content editor web part should look like this:

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:

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

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:

This should open a pop up like this:

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.

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

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:

Type in the AccessNumber and see if the search works:

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 , I hope you enjoyed the blog…

Other content to read:

Searching External Data in SharePoint 2010 Using Business Connectivity Services

Happy SharePointing…

Tagged: BCS, Business Connectivity Services, Content Editor WebPart, External Lists, Filters, Finders, Search in BCS, Search in External List