Widget to list both requests and incidents

triciav
Kilo Sage

‎05-10-2019 05:43 PM

Is there a way to create a List widget for multiple tables?

I need 1 widget that shows both incidents and requests for the current logged in user.

 

Labels:
0 Helpfuls
  • 1,491 Views
4 REPLIES 4

Mike Patel
Tera Sage

‎05-10-2019 07:26 PM

1st ticket s page widget

HTML

<sp-panel>
	<uib-tabset>
    <uib-tab index="0" heading="Active Requests" uib-tooltip="View all active requests that you opened or that were opened on your behalf."><sp-widget widget="data.all_active"></sp-widget></uib-tab>
   </uib-tabset>
 </sp-panel>

Server script

(function(){
	// Setup data tables
	var activeFilter = 'sys_class_name=incident^state=1^ORstate=2^ORstate=4^ref_incident.caller_idDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORopened_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe^NQactive=true^sys_class_name=sc_request^ref_sc_request.requested_forDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORopened_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe';
	var all_active = {
		"table": "task",
		"view": "mobile",
		"fields": "number,short_description,sys_created_on,sys_updated_on",
		"show_keywords": true,
		"show_breadcrumbs": false,
		"o": "sys_created_on", // order_by
		"filter": activeFilter,
		"storef": activeFilter,
		"query_filter": activeFilter,
	};
	
	
	data.all_active = $sp.getWidget("my_widget_data_table", all_active);
})();

 

2nd Widget

ID - my_widget_data_table

HTML

<div>
  <div class="panel panel-{{options.color}} b">
    <div class="panel-heading form-inline" ng-hide="options.hide_header">
      <span class="dropdown m-r-xs">
        <span class="dropdown-toggle glyphicon glyphicon-menu-hamburger" style="line-height: 1.4em" id="optionsMenu" data-toggle="dropdown" aria-haspopup="true" aria-expanded="true"></span>
        <ul class="dropdown-menu" aria-labelledby="optionsMenu">
          <li ng-repeat="t in ::exportTypes">
            <a ng-href="/{{data.table}}_list.do?{{::t.value}}&sysparm_query={{data.filter}}&sysparm_view={{data.view}}" target="_new">${Export as} {{::t.label}}</a>
          </li>
        </ul>
      </span>
      <span class="panel-title"><i ng-if="options.glyph" class="fa fa-{{options.glyph}} m-r"></i>{{data.title || data.table_plural}}</span>

      <button name="new" type="button" class="btn btn-primary btn-sm m-l-xs" ng-click="newRecord()" ng-if="options.show_new && data.canCreate && !data.newButtonUnsupported">${New}</button>
      <div class="pull-right" ng-if="options.show_keywords">
        <form ng-submit="setSearch(true)">
          <div class="input-group">
            <input type="text" name="datatable-search" ng-model="data.keywords" class="form-control" placeholder="${Search}">
            <span class="input-group-btn">
              <button name="search" class="btn btn-default" type="submit"><span class="glyphicon glyphicon-search"></span></button>
            </span>
          </div>
          <button name="new" type="button" class="btn btn-primary" ng-click="clearSearch()">${Clear Search}</button>
        </form>
      </div>
      <div class="clearfix"></div>
    </div>
    <!-- body -->
    <div class="panel-body">
      <div ng-if="options.show_breadcrumbs && data.filter" class="filter-breadcrumbs">
        <sp-widget widget="data.filterBreadcrumbs"></sp-widget>
      </div>
      <div class="alert alert-info" ng-if="!data.list.length && !data.num_pages && !data.invalid_table && !loadingData">
        ${No records in {{data.table_label}} <span ng-if="data.filter">using that filter</span>}
      </div>
      <div class="alert alert-info" ng-if="loadingData">
        <fa name="spinner" spin="true"></fa> ${Loading data}...
      </div>
      <table class="table table-striped table-responsive" ng-if="data.list.length">
        <thead>
          <tr>
            <th ng-repeat="field in data.fields_array track by $index" ng-click="setOrderBy(field)">
              <div class="th-title">{{data.column_labels[field]}}</div>
              <i class="fa" ng-if="field == data.o" ng-class="{'asc': 'fa-chevron-up', 'desc': 'fa-chevron-down'}[data.d]"></i>
            </th>
          </tr>
        </thead>
        <tbody>
          <tr ng-repeat="item in data.list track by item.sys_id">
            <td class="pointer" ng-class="{selected: item.selected}" ng-click="go(data.table, item)" ng-repeat="field in data.fields_array" data-field="{{field}}" data-th="{{data.column_labels[field]}}">{{item[field].display_value}}</td>
          </tr>
        </tbody>
      </table>
      <div ng-class="{'pruned-msg-filter-pad': (!options.show_breadcrumbs || !data.filter) && !data.list.length}" class="pruned-msg" ng-if="rowsWerePruned()">
        <span ng-if="rowsPruned == 1">${{{rowsPruned}} row removed by security constraints}</span>
        <span ng-if="rowsPruned > 1">${{{rowsPruned}} rows removed by security constraints}</span>
      </div>
    </div>
    <!-- footer -->
    <div class="panel-footer" ng-hide="options.hide_footer" ng-if="data.row_count">
      <div class="btn-toolbar m-r pull-left">
        <div class="btn-group">
          <a ng-disabled="data.p == 1" href="javascript:void(0)" ng-click="setPageNum(data.p - 1)" class="btn btn-default"><i class="fa fa-chevron-left"></i></a>
        </div>
        <div ng-if="data.num_pages > 1 && data.num_pages < 20" class="btn-group">
          <a ng-repeat="i in getNumber(data.num_pages) track by $index" ng-click="setPageNum($index + 1)" href="javascript:void(0)" ng-class="{active: ($index + 1) == data.p}" type="button" class="btn btn-default">{{$index + 1}}</a>
        </div>
        <div class="btn-group">
          <a ng-disabled="data.p == data.num_pages" href="javascript:void(0)" ng-click="setPageNum(data.p + 1)" class="btn btn-default"><i class="fa fa-chevron-right"></i></a>
        </div>
      </div>
      <div class="m-t-xs panel-title">${Rows {{data.window_start + 1}} - {{ mathMin(data.window_end,data.row_count) }} of {{data.row_count}}}</div>

      <span class="clearfix"></span>
    </div>
  </div>
</div>

Server

(function() {
	if (!input) // asynch load list
		return;

	data.title = options.title || input.title;

	/*
	 * data.table = the table
	 * data.p = the current page starting at 1
	 * data.o = the order by column
	 * data.d = the order by direction
	 * data.keywords = the keyword search term
	 * data.list = the table data as an array
	 * data.invalid_table = true if table is invalid or if data was not succesfully fetched
	 * data.table_label = the table's display name. e.g. Incident
	 * data.table_plural = the table's plural display name. e.g. Incidents
	 * data.fields = a comma delimited list of field names to show in the data table
	 * data.column_labels = a map of field name -> display name
	 * data.window_size = the number of rows to show
	 * data.filter = the encoded query
	 * data.query_filter = encoded query template to use when searching this table
	 */
	// copy to data[name] from input[name] || option[name]
	optCopy(['table', 'p', 'o', 'd', 'filter', 'filterACLs', 'fields', 'keywords', 'view']);
	optCopy(['relationship_id', 'apply_to', 'apply_to_sys_id']);
	optCopy(['query_filter']); // Your Custom: Allow custom query
	if (!data.table) {
		data.invalid_table = true;
		data.table_label = "";
		return;
	}

	if (!data.fields) {
		if (data.view)
			data.fields = $sp.getListColumns(data.table, data.view);
		else
			data.fields = $sp.getListColumns(data.table);
	}

	data.view = data.view || 'mobile';
	data.table = data.table || $sp.getValue('table');
	data.filter = data.filter || $sp.getValue('filter');
	data.keywords = data.keywords || $sp.getValue('keywords');
	data.textSearch = data.textSearch || ($sp.getValue('text_search') == 'true');
	data.query_filter = data.query_filter || $sp.getValue('keywords') || '';
	data.p = data.p || $sp.getValue('p') || 1;
	data.p = parseInt(data.p);
	data.o = data.o || $sp.getValue('o') || $sp.getValue('order_by');
	data.d = data.d || $sp.getValue('d') || $sp.getValue('order_direction');
	data.window_size = data.window_size || $sp.getValue('maximum_entries') || 20;
	data.page_index = data.p - 1;
	data.show_new = data.show_new || options.show_new;

	/*
	| Custom data property to store if a session is encrypted
	| Used in navigation decisions to redirect or not
	|
	*/
	data.isEncryptedSession = gs.isEdgeEncryptedSession();

	var gr;
	if (gs.getProperty("glide.security.ui.filter") == "true" || GlideTableDescriptor.get(data.table).getED().hasAttribute("glide.security.ui.filter")) {
		gr = new FilteredGlideRecord(data.table);
		gr.applyRowSecurity();
	} else
		gr = new GlideRecordSecure(data.table);
	if (!gr.isValid()) {
		data.invalid_table = true;
		data.table_label = data.table;
		return;
	}

	data.canCreate = gr.canCreate();
	data.newButtonUnsupported = data.table == "sys_attachment";
	data.table_label = gr.getLabel();
	data.table_plural = gr.getPlural();
	if (data.filter) {
		if (data.filterACLs)
			gr = $sp.addQueryString(gr, data.filter);
		else
			gr.addEncodedQuery(data.filter);
	}

	if (data.keywords){
		gs.log('Executing column search: ' + data.keywords, 'JH');
		data.queryString = data.query_filter.replace(/\{q\}/g, data.keywords);
		gr.addEncodedQuery(data.queryString);
	}

	//data.filter = gr.getEncodedQuery();

	if (data.relationship_id) {
		var rel = GlideRelationship.get(data.relationship_id);
		var target = new GlideRecord(data.table);
		var applyTo = new GlideRecord(data.apply_to);
		applyTo.get("sys_id", data.apply_to_sys_id);
		rel.queryWith(applyTo, target); // put the relationship query into target
		gr.addEncodedQuery(target.getEncodedQuery()); // get the query the relationship made for us
	}

	if (data.o){
		if (data.d == "asc")
			gr.orderBy(data.o);
		else
			gr.orderByDesc(data.o);
	}

	data.window_start = data.page_index * data.window_size;
	data.window_end = (data.page_index + 1) * data.window_size;
	gr.chooseWindow(data.window_start, data.window_end);
	gs.log('Query String: ' + gr.getEncodedQuery(), 'JH');
	gr._query();

	data.row_count = gr.getRowCount();
	data.num_pages = Math.ceil(data.row_count / data.window_size);
	data.column_labels = {};
	data.fields_array = data.fields.split(',');

	// use GlideRecord to get field labels vs. GlideRecordSecure
	var grForLabels = new GlideRecord(data.table);
	for (var i in data.fields_array) {
		var field = data.fields_array[i];
		var ge = grForLabels.getElement(field);
		if (ge == null)
			continue;

		data.column_labels[field] = ge.getLabel();
	}

	data.list = [];
	while (gr._next()) {
		var record = {};
		$sp.getRecordElements(record, gr, data.fields);
		if (gr instanceof FilteredGlideRecord) {
			// FilteredGlideRecord doesn't do field-level
			// security, so take care of that here
			for (var f in data.fields_array) { 
				var fld = data.fields_array[f];
				if (!gr.isValidField(fld))
					continue;

				if (!gr[fld].canRead()) {
					record[fld].value = null;
					record[fld].display_value = null;
				}
			}
		}
		record.sys_id = gr.getValue('sys_id');
		record.table = gr.getValue('sys_class_name');
		data.list.push(record);
	}

	var breadcrumbWidgetParams = { table: data.table, query: data.filter };
	data.filterBreadcrumbs = $sp.getWidget('widget-filter-breadcrumbs', breadcrumbWidgetParams);

	// copy to data from input or options
	function optCopy(names) {
		names.forEach(function(name) {
			data[name] = input[name] || options[name];
		})
	}

})();

Client

function ($scope, $location, spUtil, amb, $http) {
	var c = this;
	
	/*
	* options:
	* hide_footer (bool) = true to remove the data table footer contents
	* hide_header (bool) = true to remove the data table header contents
	* show_new (bool) = true to show the "New" record button
	* show_keywords (bool) = true to show the keyword search field
	* table (string) = the table name to query
	* filter (string) = the encoded query
	* o (string) = the order by column
	* d (string) = The order by direction: asc or desc
	* p (int) = the page to jump to
	* fields (string) = comma seperated list of fields that become the list columns
	* view (string) = the default view to load for columns, overrides fields
	*/
	
	$scope.clearSearch = function(){
		$scope.data.filter = $scope.data.storef;
		$scope.data.keywords = null;
		$scope.setSearch(true);
	};
	
	$scope.exportTypes = [{label:'PDF', value: 'PDF'}, {label:'Excel', value:'EXCEL'}, {label:'CSV', value:'CSV'}];
	var keys = ['table', 'filter', 'p', 'o', 'd'];

	var eventNames = {
		click: 'data_table.click',
		setFilter: 'data_table.setFilter',
		setKeywords: 'data_table.setKeywords'
	};

	$scope.go = function(table, item) {
		var parms = {};
		parms.table = item.table || table;
		parms.sys_id = item.sys_id;
		parms.record = item;
		parms.isEncryptedSession = c.data.isEncryptedSession;
		$scope.ignoreLocationChange = true;
		for (var x in c.data.list) {
			c.data.list[x].selected = false;
		}
		item.selected = true;
		$scope.$emit(eventNames.click, parms);
	};

	$scope.newRecord = function(){
		var parms = {
			id: 'form',
			table: $scope.data.table,
			sys_id: '-1'
		};
		if ($scope.data.filter != '')
			parms.query = $scope.data.filter;

		$location.search(parms);
	};

	function recoverStateFromUrl() {
		$scope.data.fields = [];
		var s = $location.search();
		for (var x in keys) {
			if (s[keys[x]]) {
				$scope.data[keys[x]] = s[keys[x]];
			}
		}
		$scope.server.update().then(function(data) {
			if (s.sys_id) {
				for (var x in data.list) {
					if (data.list[x].sys_id == s.sys_id) {
						$scope.go(s.table, data.list[x]);
					}
				}
			}
		});
	}

	if ($scope.options.fromUrl) {
		$scope.$on('$locationChangeSuccess', function(e) {
			if ($scope.ignoreLocationChange){
				$scope.ignoreLocationChange = false;
				return;
			}

			// Helps to recover state when using the browser's back button
			recoverStateFromUrl();
		});
	}


	$scope.getNumber = function(num) {
		return new Array(num);
	}

	$scope.mathMin = function(v1,v2) {
		return Math.min(v1,v2);
	}

	function getData(updateUrl) {
		var f = $scope.data;
		spUtil.update($scope).then(function(data) {
			f.view = data.view;
			if ($scope.options.fromUrl && updateUrl)
				setPermalink(f.table, f.filter, f.o, f.d, f.p);

			if ($scope.options.show_breadcrumbs && data.filterBreadcrumbs)
				$scope.$broadcast('widget-filter-breadcrumbs.setBreadcrumbs', data.filterBreadcrumbs.data);

			initRecordWatcher(f.table, f.filter);
		});
	}

	function setPermalink(table, filter, orderBy, orderDirection, page){
		$scope.ignoreLocationChange = true;
		var search = $location.search();
		angular.extend(search, {
			spa: 1,
			table: table,
			filter: filter,
			p: page,
			o: orderBy,
			d: orderDirection
		});
		$location.search(search);
	}

	var watcher;
	function initRecordWatcher(table, filter){
		if (watcher)
			watcher.unsubscribe();

		if (table && filter) {
			var watcherChannel = amb.getChannelRW(table, filter);
			amb.connect();
			watcher = watcherChannel.subscribe(function() {
				spUtil.update($scope)
			});
		}
	}

	$scope.setPageNum = function(num) {
		$scope.data.p = num;
		getData(true);
	}

	$scope.setOrderBy = function(field) {
		var d = "asc";
		if ($scope.data.o == field) {
			if ($scope.data.d == "asc")
				d = "desc";
			else
				d = "asc";
		}
		$scope.data.o = field;
		$scope.data.d = d;
		$scope.setSearch(true);
	}

	$scope.setSearch = function(updateUrl) {
		$scope.data.p = 1;
		getData(updateUrl);
	}

	$scope.$on(eventNames.setFilter, function(e, newFilter){
		$scope.data.filter = newFilter;
		$scope.setSearch(false);
	});

	$scope.$on(eventNames.setKeywords, function(e, keywords){
		$scope.data.keywords = keywords;
		$scope.setSearch(false);
	});

	$scope.$on('widget-filter-breadcrumbs.queryModified', function(e, newFilter){
		$scope.data.filter = newFilter;
		$scope.setSearch(true);
	});

	$scope.rowsWerePruned = function() {
		if (!$scope.data.list)
			return;

		$scope.rowsPruned = $scope.mathMin($scope.data.window_end,$scope.data.row_count) - $scope.data.window_start - $scope.data.list.length;
		return $scope.rowsPruned > 0;
	}

	$scope.showFilter = function() {
		return !$scope.data.list.length && !$scope.data.num_pages && !$scope.data.invalid_table && !$scope.loadingData;
	}

	c.appendQuery = function(query){
		if ($scope.data.filter.length > 1)
			$scope.data.filter += '^';
		$scope.data.filter += query;
		$scope.setSearch();

	}

	// Makes Widget Async
	var title = $scope.data.title;
	if ($scope.options.use_instance_title == 'true')
		title = $scope.options.title;
	$scope.data = $scope.options;
	$scope.loadingData = true;
	$scope.server.update().then(function() {
		if ($scope.data.newButtonUnsupported)
			console.log("Service Portal: New button not supported for sys_attachment list");
		$scope.loadingData = false;
		$scope.data.title = title;
		initRecordWatcher($scope.data.table, $scope.data.filter);
	});

	function parseQuery(table, queryString){
		return $http.post('/api/now/sp/parsequery/' + table, queryString).then(function(response){
			return response.data.result;
		});
	}

	c.createQueryTerm = function(table, field, sys_id, operator){
		return $http.get('/api/now/sp/getInOutQueryTerm', {
			params: {
				table: table,
				sys_id: sys_id,
				field: field,
				operator: operator
			}
		}).then(function(response){
			if (response && response.data && response.data.result)
				return response.data.result.parts;
		});
	}

	c.showMatching = function(field, newTerm) {
		var queryString = $scope.data.filter;
		var eq = "";
		parseQuery($scope.data.table, queryString).then(function(oldTerms) {
			for(var i=0; i<oldTerms.length; i++){
				var term = oldTerms[i];
				if (isSameField(newTerm, term))
					continue;

				if (eq.length)
					eq += '^';

				eq += getEncodedTerm(term);
			}
			if (eq.length)
				eq += '^';
			eq += getEncodedTerm(newTerm);

			$scope.data.filter = eq;
			$scope.setSearch();
		});
	};

	c.filterOut = function(field, newTerm) {
		var eq = $scope.data.filter;
		if (eq.length)
			eq += '^';

		eq += getEncodedTerm(newTerm);
		$scope.data.filter = eq;
		$scope.setSearch();
	};

	function isSameField(t1, t2) {
		if ('left' in t1 && 'left' in t2)
			return t1.left.field === t2.left.field;
		else if ('left' in t1)
			return t1.left.field === t2.field;
		else if ('left' in t2)
			return t1.field === t2.left.field;
		return t1.field === t2.field;
	}

	function getEncodedTerm(term) {
		var eq;
		if (term.left) {
			eq = getEncodedTerm(term.left);
			eq += '^OR';
			eq += getEncodedTerm(term.right);
		} else {
			eq = term.field;
			eq += term.operator;
			eq += term.value;
		}
		return eq;
	}

}

Tricia3
Kilo Contributor
In response to Mike Patel

‎05-11-2019 05:15 AM

Mike,

What is the first widget?

 

0 Helpfuls

Mike Patel
Tera Sage
In response to Tricia3

‎05-11-2019 11:46 AM

Someone also asked same question so I created article. Please see below (If that's what you are looking for than please mark this as correct answer)

https://community.servicenow.com/community?id=community_article&sys_id=4c639526db5d7b4ce0e80b55ca961...

0 Helpfuls

Mike Patel
Tera Sage

‎05-17-2019 11:17 AM

Please refer to my article 

https://community.servicenow.com/community?id=community_article&sys_id=4c639526db5d7b4ce0e80b55ca961...

and mark answer as correct so it's removed from unanswered list.

0 Helpfuls