Data Table widget that supports enhanced search and filtering

[charliesdev]

I had the requirement to provide a widget that displays tabular data, has a base filter, allows additional filtering and supports search.  There are a couple great posts on the Developer Community site, but none of them addressed all of my needs.

I initially developed the solution in an existing, scoped application - but encountered so many problems that I started over with a new, "legacy" Global application. 

The solution was to:

1. Add buttons that apply predefined filters to the data.  This was a major compromise, but there seems to be no way to allow the user access to the Filter control on a Data Table widget without allowing them to select 'All'.
2. Change the way 'Keyword Search' works OOB.  By default, it adds search terms and we needed the new term to replace the existing one.  Also some special characters are not supported, ( and ) are supported but - is not.

I started by cloning the widget: Data Table (naming it 'filtered_searchable_dt') and making the following changes

Server Script:

data.storef = data.filter || $sp.getValue('filter');

HTML:

From this:
<button name="search" class="btn btn-default" type="submit" aria-label="${Search}"><span class="glyphicon glyphicon-search"></span></button>

To this:
<button name="search" class="btn btn-default" type="submit" ng-click="replaceSearch()" aria-label="${Search}"><span class="glyphicon glyphicon-search"></span></button>
				    <button name="clear" class="btn btn-primary btn-sm m-l-xs" ng-click="clearSearchTerm()" aria-label="${Clear Search Term}">${Clear Search}</button>

Client Script (add this):

// replace keyword search term - rather than adding to it:
$scope.replaceSearch = function(){
	// convert special characters in keywords term to safe version
	//$scope.data.keywords = scrubKeywords();
		
	$scope.data.filter = c.data.storef;
	$scope.setSearch(true);
};	

$scope.clearSearchTerm = function(){
	var currentFilter = $scope.data.filter;
	var idx = currentFilter.indexOf('^123TEXTQUERY321');
	if(idx > 0){
		currentFilter = currentFilter.substr(0, idx);
	}
	$scope.data.filter = currentFilter;
	$scope.setSearch(true);
}

Then I cloned the widget: Data Table from URL Definition and made the following changes

Server Script:

data.show_breadcrumbs = false;
data.dataTableWidget = $sp.getWidget('filtered_searchable_dt', data);

HTML: 

Added a couple buttons just above the widget to support applying predefined filters:

<div ng-if="data.dataTableWidget && !data.invalid_table">
  	  <button name="orderable" class="btn btn-primary btn-sm m-l-xs" ng-click="showAllOrderable()" aria-label="${Orderable Assets}">${Orerable Assets}</button>
	    <button name="allActive" class="btn btn-primary btn-sm m-l-xs" ng-click="showAllActive()" aria-label="${Active Assets}">${Active Assets}</button>
  	<sp-widget widget="data.dataTableWidget"></sp-widget>
  </div>​

Client Script (modify URL to contain filter):

$scope.showAllOrderable = function(){
	var currentURL = top.location.href;
	var indexBeginFilter = currentURL.indexOf('filter=');
	if(indexBeginFilter === -1)
		return;
	var indexEndFilter = currentURL.indexOf('&', indexBeginFilter);
	var currentFilter = currentURL.slice(indexBeginFilter, indexEndFilter);
	var newURL = currentURL.replace(currentFilter, 'filter=u_active=true^u_discontinued=false^u_item_groupISNOTEMPTY^u_service_typeISNOTEMPTY');
	window.location.href = newURL;
}

$scope.showAllActive = function(){
	var currentURL = top.location.href;
	var indexBeginFilter = currentURL.indexOf('filter=');
	if(indexBeginFilter === -1)
		return;
	var indexEndFilter = currentURL.indexOf('&', indexBeginFilter);
	var currentFilter = currentURL.slice(indexBeginFilter, indexEndFilter);
	var newURL = currentURL.replace(currentFilter, 'filter=u_active=true^u_discontinued=false');
	window.location.href = newURL;
}

The Table and Filters are hard-coded for now, so the next step is to expand the schema for the widgets to expose those as options.

Update: we are going in a different direction that avoids the buttons representing predefined filters, so this won't make it into production.  Maybe it can still help someone.  Comments welcome.

References:  a post that helped me solve the Keyword Search adding (not replacing) terms:  Add and refine Keyword Search Box...

my question about supported characters in the Keyword Search term: Special Characters in Keyword Search