Full Text Search in SharePoint on multiple document libraries using SPServices

A while ago, I needed a way to do a full text search of several SharePoint document libraries and display it in one page in a friendly way. I decided to use SPServices to do my querying and output the results using Javascript.

This script does a full text search on the keywords passed into an input box on the current scope of the site. After getting the query results, it then iterates through each search result and displays only the results where the path matches the list names passed in.

Documents in 2 different libraries:

Search output:

Refined search output:

Steps:

1. Ensure search is configured and working on your site

2. Add this script to a content editor web part on your page (View Code)

3. Update the script tags with the location of SPServices and JQuery on your site

4. Make sure to update the listUrl div with the correct list paths that you would like to restrict the output to

<div id="divSearch">
	<div>
		<b>Search Keyword: </b><br/><input type="text" id="tbSearch" size="50%" />
		<input type="button" id="btnSearch" value="Search Policies" onclick="RunSearch();" />
	</div>
</div>

<div id="divErrorMsg"></div>
<div id="divSearchResults"></div> 
<div id="divTestQueryResult"></div>
<div id="divTestQuery"></div>

<script language="javascript" type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script type="text/javascript" language="javascript" src="/policy/SiteAssets/js/jquery.SPServices-0.7.2.min.js"></script>

<script type="text/javascript"> 

//author: sparsee
//DCSharePointchick.blogspot.com
/**configurable**/
var m_searchCap = 5000;
var m_listUrls = "https://site/policy/Procedure;https://site/policy/Policy;https://site/policy/Supplement;";
var m_displayQueryResults = false;
/**************/

var m_searchQueryForList = "";
var m_arrSearchResults = new Array();
var m_searchResultCount = 0;
var m_output = "";

var imgTxt = '<img src="_layouts/images/ictxt.gif" BORDER=0>';
var imgDoc = '<img src="/_layouts/images/icdocx.gif" BORDER=0>';
var imgPdf = '<img src="_layouts/images/pdf16.gif" BORDER=0>';
var imgDefault = '<img src="_layouts/images/STS_ListItem16.gif" BORDER=0>';

$(document).ready( function() 
{
	//get document properties
	var listUrls = m_listUrls;
	listUrls = listUrls.slice(1,listUrls.length);  
	listUrls = listUrls.substring(0, listUrls.length-1); 
	var arrlistUrls = listUrls.split(';');

	for( var y = 0; y < arrlistUrls.length; y++)
	{
		var curUrl = arrlistUrls[y];
		
		if( curUrl != null && curUrl != "" )
		{
			var url = curUrl.toLowerCase();
			if( m_searchQueryForList != "" )
			{
				m_searchQueryForList += "OR CONTAINS(Path, '\""+ url +"*\"')";
			}
			else
			{
				m_searchQueryForList += "CONTAINS(Path, '\""+ url +"*\"')";
			}
		}
	} 
	
	$("#tbSearch").keyup(function(){
		RunSearch();
	});
});


function RunSearch()
{
	$("#divSearchResults").html("Searching...");
	$("#divErrorMsg").html("");
	m_arrSearchResults = new Array();

	var tbSearch = document.getElementById("tbSearch").value; 

	var myQuery = "<QueryPacket xmlns='urn:Microsoft.Search.Query' Revision='1000'>" + 
		"<Query>" + 
		"<Range><Count>" + m_searchCap + "</Count></Range>" +
		"<Context>" +
		"<QueryText language='en-US' type='MSSQLFT'>" +
		"SELECT Title, Rank, Size, Author, HitHighlightedSummary, Description, Path, Write FROM Scope() WHERE FREETEXT('" + tbSearch + "') AND (" + m_searchQueryForList + ") ORDER BY \"Rank\" DESC" +
		"</QueryText>" +
		"</Context>" +
		"</Query>" +
		"</QueryPacket>";


	$().SPServices({
		operation: "Query",
		async: true,
		queryXml: myQuery,
		//debug: true,
		completefunc: function (xData, Status) {

			$("#divSearchResults").html("Searching...");
			$("#divErrorMsg").html("");
			m_arrSearchResults = new Array();


			if (Status != "success") 
			{
				DisplayErrorMesssage(Status);
				return;
			}

			var queryResult = $(xData.responseXML).find("QueryResult").text();

			if( m_displayQueryResults )
			{
				$("#divTestQueryResult").text(queryResult);
				$("#divTestQuery").text("<br/>" + myQuery);
			}
			else
			{
				$("#divTestQueryResult").text("");
				$("#divTestQuery").text("");
			}

			$(xData.responseXML).find("QueryResult").each(function() 
			{  
				var xml = $("<xml>" + $(this).text() + "</xml>");  
				xml.find("Document").each(function() 
				{  
					var curPath = $("Action>LinkUrl", $(this)).text().toLowerCase();  

					var curTitle = "";  
					$(this).find("Property").each(function() 
					{  
						if ($("Name", $(this)).text() == "TITLE") 
						{  
							curTitle = $("Value", $(this)).text(); 
						}  
					});  

					var curHithighlighted = "";
					$(this).find("Property").each(function() 
					{  
						if ($("Name", $(this)).text() == "HITHIGHLIGHTEDSUMMARY") 
						{  
							curHithighlighted = $("Value", $(this)).text(); 
						}  
					});  

					var curWrite = "";
					$(this).find("Property").each(function() 
					{  
						if ($("Name", $(this)).text() == "WRITE") 
						{  
							curWrite = $("Value", $(this)).text(); 
						}  
					});  
		 
					var curAuthor = "";
					$(this).find("Property").each(function() 
					{  
						if ($("Name", $(this)).text() == "AUTHOR") 
						{  
							curAuthor = $("Value", $(this)).text(); 
						}  
					});   
		
					var arrayRows = new Array();
					arrayRows.push([curTitle, curPath, curHithighlighted, curWrite, curAuthor]);

					m_arrSearchResults.push([ curTitle, arrayRows ]);
				});  
			});
			
			PrintOutput();
		}
	});
}

function PrintOutput()
{ 
	m_searchResultCount = 0;
	m_output = '<table class="section-body">';
	
	var tb = document.getElementById("tbSearch").value; 

	for( var x = 0; x < m_arrSearchResults.length; x++ )
	{
		var searchTitle = m_arrSearchResults[x][0];
		var arrayRows = m_arrSearchResults[x][1];

		if( arrayRows.length > 0 )
		{
			var tableID = '"' + x + 'Table"';
			
			m_output += "<tr><td><table id=" + tableID + " class='tablesorter section-table table_summary' width='800px' style='margin-top:0px !important;'>";

			for( var y = 0; y < arrayRows.length; y++)
			{			
				var row = arrayRows[y];
				
				var title = row[0];
				var path = row[1];
				var highlighted = row[2];
				var write = row[3];
				var author = row[4];

				highlighted = GetFormattedHighlightedText(highlighted);
				title = GetHighlightedTitle(title, tb);
				title = GetFormattedTitle(path, title);

				write = GetFriendlyDate(write);

				var highlightedPath = GetHighlightedPath(path, tb);

				m_output += PrintRow(path, title, highlighted, highlightedPath, write, author);
				m_searchResultCount++;  
			}
			
			m_output += "</table></td></tr>";
		}
	}

	var returnedResults = "<br/><span style='color:red'>Showing Results for: <b>" + document.getElementById("tbSearch").value + "</b></span> (Returned: <font color='green'>" + m_searchResultCount + "</font> results)";

	m_output += "</table>";
	m_output = returnedResults + m_output;

	$("#divSearchResults").html(m_output);
}

function PrintRow(path, title, highlighted, highlightedPath, write, author)
{
	var row = '<tr><td><a href="' + path + '" target="_blank">' + title + '</a></td></tr>' + 
	'<tr><td>' + highlighted + '</td></tr>' +
	'<tr><td><a style="color:green;" href="'+ highlightedPath + '">' + highlightedPath +'</a><font color="grey"> - ' + author + ' - ' +  write + '</font></td></tr>';
	
	return row;
}

function GetFormattedTitle(path, title)
{
	if( path.indexOf(".pdf") >= 0 )
		title = imgPdf + title;
	else if( path.indexOf(".doc") >= 0 )
		title = imgDoc + title;
	else if( path.indexOf(".txt") >= 0 )
		title = imgTxt + title;
	else
		title = imgDefault + title;
		
	return title;
}

function GetHighlightedTitle(title, searchText)
{
	if( searchText != "" )
		return highlight(title, searchText);
	else
		return title;
}

function GetHighlightedPath(path, searchText)
{
	if( searchText != "" )
		return highlight(path, searchText);
	else
		return path;
}

function highlight( data, search ) { return data.replace( new RegExp( "(" + preg_quote( search ) + ")" , 'gi' ), "<b>$1</b>" ); } 
function preg_quote( str ) {  return (str+'').replace(/([\\\.\+\*\?\[\^\]\$\(\)\{\}\=\!\<\>\|\:])/g, "\\$1"); } 


function GetFormattedHighlightedText(highlighted)
{
	if( highlighted != null )
	{
		highlighted = highlighted.replace("<c0>", "<font color='green'><b>");
		highlighted = highlighted.replace("</c0>", "</b></font>");

		highlighted = highlighted.replace("<c1>", "<font color='green'><b>");
		highlighted = highlighted.replace("</c1>", "</b></font>");

		highlighted = highlighted.replace("<c2>", "<font color='green'><b>");
		highlighted = highlighted.replace("</c2>", "</b></font>");

		highlighted = highlighted.replace("<c3>", "<font color='green'><b>");
		highlighted = highlighted.replace("</c3>", "</b></font>");

		highlighted = highlighted.replace("<c4>", "<font color='green'><b>");
		highlighted = highlighted.replace("</c4>", "</b></font>");

		//highlighted = "<i>" + highlighted + "</i>";

		return highlighted;
	}
	return "";
}

function GetFriendlyDate(dateField)
{
	var datetimeparts = dateField.split('T');
	var dateparts = datetimeparts[0].split('-');
	var month = dateparts[1];
	var date = dateparts[2];
	var yr = dateparts[0]

	var formatteddateandtime = month + "/" + date + "/" +  yr ;
	
	return formatteddateandtime;
}

function DisplayErrorMesssage( errorMsg )
{
	$("#divErrorMsg").html("Error occurred. " + errorMsg );
}

function CheckSubmit() 
{
    if (event.keyCode == 13) 
	{
		$("#btnSearch").focus();
    }
}
</script>

SharePoint Calendar by Fiscal Year with FullCalendar and SPServices

A little background:

Last week at work, I had a requirement to display a calendar view in SharePoint that would display a full fiscal quarter of a year on one page. It would have to be a color coded calendar, filter by Fiscal Year, Fiscal Quarter and by the Department and show a full quarter view (3 months) on one page.

The first thing I thought of was to add 3 calendar web parts on one page (one for each month of the quarter) and then add some javascript that would redirect the calendars to the current quarter's months. Well, that didn't work out too well because the calendars are controls, and I can't pass in multiple parameters in the url for each one.

So next, I started down the path of creating a gannt chart that would be filtered by each quarter. Each quarter and fiscal year would be dynamically set based on calculated column fields. The first bump in the road was that the gannt chart doesn't go past 2 years. The second bump in the road was that reoccuring events would show one line from the first date to the very end date (there were no tick boxes). I had to scratch that plan.

After doing some research, I came across this blog by Josh McCarty. It utilizes the FullCalendar jQuery library with SPServices to query a SharePoint calendar and display it in a friendly way.

I used his code as a base, and expanded it to create multiple calendar views on one page (one calendar view per month (up to 12!)), allow filtering by the fiscal year and fiscal quarter, filter by department, add a legend of colors for the department and enable tooltips for each event. 

The entire color-coded calendar view, with a legend and drop down fields to filter on fiscal year, fiscal quarter and department (by default, it will set the Time Frame to be the current fiscal year and quarter):

Ability to show the calendar by the week:

Ability to filter by Department:

Steps:

1. Create a simple calendar on your SharePoint site. Add a column called "Department" and make it a choice field. Add the choice options. Ex.:

Dept1

Dept1: SubDept

Dept2

Dept2: SubDept

2. Add a calculated column called "Color" and set it to:

=IF(ISNUMBER(FIND("Dept1",Department)),"LightBlue",IF(ISNUMBER(FIND("Dept2",Department)),"Yellow")

Modify this field with the correct department names and colors you would like to use. 

3. Add the javascript/spservices/jquery libraries to a document library on your site.

4. Insert this code into a Content Editor web part on a page and modify the listCalendarName variable to be the name of your calendar and update the script tags. (View Code)

5. Voila! You should be able to see a color coded calendar similar to the one above!