Creating interactive filter on string fields??

[jcmings]

Hi, I'm trying to use interactive filters to allow users to filter by the Value field stored in a metric_instance record. I have already created a database view that joins my case table, metric_instance, and metric_definition. I have set up interactive filters on fields like Assignment Group and Status, but these only point to the current value of the case record, and not to the historical value that is stored in Value on metric_instance.

I would like to create a custom interactive filter where I choose an Assignment Group from a reference field, and it searches the Value field on my db view for those values. From my research, it looks like I may have to make a content block... am I going down the right path with this? Has anyone already attempted to create what I'm looking for?

Thanks!

[Clarence Warren]

Hello,

I'm still new to ServiceNow and forgive me if my answer is off but I'll take a crack at it. Looking at the way you explained the situation, the issue may lie in how the interactive filter is setup for the Assigment Group and Status and then the "Value" information is being pulled from. Is the actual "Value" pointing to the correct reference record.

[jcmings · Accepted solution]

Hey Clarence,

Unfortunately the "Value" field on the metric_instance table is a string, so I can't point my Assignment Group reference value to it. I was able to create a custom filter that can grab that Assignment Group name and compare that value with the string in the "Value" field. You'll notice this in the publishCustomFilter() function where I have "min_value" (min_ is required to indicate which joined table is being pointed to).

I grabbed this code from this ServiceNow doc and modified a few bits to handle what I needed. This gives me a multi-select box like you would see on any other interactive filter. However, there are a few limitations to it currently, like not being able to click into the "results box" and search (you have to click the dropdown). Custom filters are also pretty janky; sometimes I have to refresh the page to even use the filter. A few other limitations are listed here on the official SN docs site. 

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
	<script>  
		var arrItemSysId            = [];
		var arrItemName             = [];
		var dashboardMessageHandler = new DashboardMessageHandler(
		"custom_asmt_group_filter", 
		function() {
		resetCustomFilter()
		});


		// transform HTML selectbox to ServiceNow selection
		$j(document).ready(function() {
		$j("#myCustomInteractiveFilterChoice").select2();
		});  

		function rebuildCustomFilterUsers() {
		var arrOut = [];

		for (var numItemIndex = 0; numItemIndex &lt; arrItemSysId.length; numItemIndex++) {
		arrOut.push('&lt;li class="select2-search-choice"&gt;');
		arrOut.push('&lt;div&gt;');
		arrOut.push(arrItemName[numItemIndex]);
		arrOut.push('&lt;/div&gt;');
		arrOut.push('&lt;a href="#" onClick="removeCustomFilterItem(\'')
		arrOut.push(arrItemSysId[numItemIndex])
		arrOut.push('\');return(false)" role="button" class="select2-search-choice-close"&gt;&lt;/a&gt;');
		arrOut.push('&lt;/li&gt;');
		}

		$j("#myCustomInteractiveFilterSelectedItems ul:first").html(arrOut.join(''));
		$j('#myCustomInteractiveFilter span.select2-chosen').text(arrItemSysId.length == 0 ? 'All' : '');
		}

		function publishCustomFilter() {  
		var objFilter = {
		id     : 'custom_asmt_group_filter',
		table  : '___________', <--- my custom database view table name
		filter : arrItemName.length > 0 ? 'min_valueIN' + arrItemName : ''
		};

		SNC.canvas.interactiveFilters.setDefaultValue({
		id      : objFilter.id,
		filters : [objFilter]
		}, false);

		if (arrItemSysId.length > 0) {
		dashboardMessageHandler.publishFilter(objFilter.table, objFilter.filter);
		}
		else {
		dashboardMessageHandler.removeFilter();
		}
		}  

		function removeCustomFilterItem(strSysId) {
		if (typeof strSysId == 'string') {
		var numArrayIndex = arrItemSysId.indexOf(strSysId);

		if (numArrayIndex != -1) {
		arrItemSysId.splice(numArrayIndex, 1);
		arrItemName.splice(numArrayIndex, 1);
		rebuildCustomFilterUsers();
		publishCustomFilter();
		}
		}
		}

		function resetCustomFilter() {
		arrItemSysId = [];
		arrItemName  = [];

		rebuildCustomFilterUsers();
		publishCustomFilter();
		}

		function addCustomFilterItem(){
		var strSelectedValue = $j('#myCustomInteractiveFilterChoice option:selected').val();
		var strSelectedLabel = $j('#myCustomInteractiveFilterChoice option:selected').text();

		if (strSelectedValue == "all") {
		resetCustomFilter();
		}
		else {
		if (!arrItemSysId.includes(strSelectedValue)) {
		arrItemSysId.push(strSelectedValue);
		arrItemName.push(strSelectedLabel);
		}

		rebuildCustomFilterUsers();
		publishCustomFilter();  
		}
		}
	</script>
	<g:evaluate>
	
		var arrAsmtGroupList = [];
		var grGroup = new GlideRecord('sys_user_group');
		grGroup.addEncodedQuery('____________'); <--- enter whatever encoded query here
		grGroup.orderBy('name');
		grGroup.query();

		while(grGroup.next()) {
		arrAsmtGroupList.push({
		name:  grGroup.getValue('name'),
		sysId: grGroup.getUniqueValue()
		}); 
		} 
	</g:evaluate>   
	<div class="widget-content" style="padding:10px" id='myCustomInteractiveFilter'>
		<select style="width:100%;" id="myCustomInteractiveFilterChoice" class="select2-search" onchange="addCustomFilterItem();">
			<div class="form-horizontal container-fluid">
				<option value="all">All</option>
				<j:forEach var="jvar_type" items="${arrAsmtGroupList}">
					<g:evaluate var="jvar_type" jelly="true">
						var strName  = jelly.jvar_type.name;
						var strSysId = jelly.jvar_type.sysId;
					</g:evaluate>
					<option value="${strSysId}" label="${strName}">${strName}</option>
				</j:forEach>
			</div>
		</select>
		<div id="myCustomInteractiveFilterSelectedItems" class="select2-container select2-container-multi interactive-filter__widget-content form_control" style="width: 100%;">
			<ul class="select2-choices">
			</ul>
		</div>
	</div>
</j:jelly>