record watcher filter

Sam Ogden · ‎06-07-2017

Hi All,

We have a scripted list widget on our service portal header. I need to change one of the filters that is used on one of the record watchers to add an additional condition but can't get the syntax right. Currently the filter is as below:

var u = gs.getUser().getID(); //Just added to show what var u is.

t.record_watchers.push({'table':'sc_request','filter':'active=true^requested_for=' + u});

I want the filter to check for active records where the logged in user is requested for OR opened by. not sure how to add this into the above filter

Any help is greatly appreciated.

Thanks

Shahed Shah1 · ‎06-08-2017

I'd like to give you a little trick to build up encoded queries.

When you know what kind of filter you need, go to the respective table and filter it accordingly. When trying to use Javascript in the filter, look for the is (dynamic) operator and you can use the pre-existing scripts. Then you can right click on the breadcrumb and select Copy query.

In your clipboard you will have the precise query to put into any mechanism that uses encoded queries, such as the filter you are using GlideRecord.addEncodedQuery, GlideAggregate.addEncodedQuery, etc.

Hope this helps.

Sam Ogden · ‎06-14-2017

Hi Shahid,

Thanks for the above. I've amended my record watcher filter to:

t.record_watchers.push({'table':'sc_request','filter':'active=true^opened_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORrequested_forDYNAMIC90d1921e5f510100a9ad2572f2b477fe'});

However in the scripted list on my menu I am still only seeing the logs where the logged in user is in the requested_by field. I need it to show when the logged in user is in the requested_by field OR the opened_by field.

I've had a look further in the code and it looks like the below query is only showing logs for requested_by. What do I need to amend here in order to display logs for requested_by OR opened_by?

var z = new GlideRecord('sc_request');
z.addActiveQuery();
z.addQuery('requested_for', gs.getUserID());
z.orderByDesc('sys_updated_on');
//z.setLimit(maxlist);
z.query();
while (z.next()) {
var ritm = new GlideRecord('sc_req_item');
ritm.addQuery('request', z.getUniqueValue());
ritm.query();
if (!ritm.next())
continue;

var a = {};
$sp.getRecordValues(a, z, 'sys_id,number,sys_updated_on');
if (ritm.hasNext())
a.short_description = ritm.getRowCount() + ' requested items';
else
a.short_description = ritm.cat_item.getDisplayValue() || ritm.getDisplayValue("short_description");
a.__table = z.getTableName();
a.type = 'cdl_request';
a.sortOrder = z.sys_updated_on.getGlideObject().getNumericValue();
t.items.push(a);
}

Thanks

Shahed Shah1 · ‎06-14-2017

That query you coded will also need to change from:

z.addQuery('requested_for', gs.getUserID());

to:

z.addEncodedQuery("active=true^opened_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORrequested_forDYNAMIC90d1921e5f510100a9ad2572f2b477fe");

Using addEncodedQuery allows you to use the same query strings copied from the breadcrumb

EDIT: Adding reference to the docs

Sam Ogden · ‎06-15-2017

Hi Shahid,

Thanks for the above, that's worked great. I do have one other issue that I'm wondering if you could help me with. I want the counter that is shown in the menu (highlighted in yellow below) to still show the total number of logs, but I would like the list (circled in blue below) to only show the first 10 incidents and the first 10 requests (20 in total).

Below is the current code for the scripted list. If I change the var 'max' then it changes both the number of logs displayed in the list, but also the number shown in the counter. Do you know how I can change this to work for the above requirement?

Any help is greatly appreciated.

// maximum number of entries in this Menu
var max = 300;
var t = data; // shortcut
t.items = [];

var u = gs.getUser().getID();

// use record watchers to tell header when to update dropdown counts
t.record_watchers = [];
t.record_watchers.push({'table':'service_task','filter':'active=true^opened_by=' + u});
t.record_watchers.push({'table':'incident','filter':'caller_idDYNAMIC90d1921e5f510100a9ad2572f2b477fe^active=true^EQ^ORDERBYDESCopened_at'});
t.record_watchers.push({'table':'sc_request','filter':'active=true^opened_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORrequested_forDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORDERBYDESCopened_at'});

var z = new GlideRecord('incident');
z.addActiveQuery();
z.addQuery('caller_id', gs.getUserID());
z.orderByDesc('sys_updated_on');
//z.setLimit(maxlist);
z.query();
while (z.next()) {
var a = {};
$sp.getRecordValues(a, z, 'short_description,sys_id,number,sys_updated_on');
if (z.short_description.nil())
a.short_description = "(No description)";
a.__table = z.getTableName();
a.type = 'cdl_ticket';
a.sortOrder = z.sys_updated_on.getGlideObject().getNumericValue();
t.items.push(a);
}

var z = new GlideRecord('sc_request');
z.addActiveQuery();
z.addEncodedQuery("active=true^opened_byDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORrequested_forDYNAMIC90d1921e5f510100a9ad2572f2b477fe");
z.orderByDesc('sys_updated_on');
//z.setLimit(maxlist);
z.query();
while (z.next()) {
var ritm = new GlideRecord('sc_req_item');
ritm.addQuery('request', z.getUniqueValue());
ritm.query();
if (!ritm.next())
continue;

var a = {};
$sp.getRecordValues(a, z, 'sys_id,number,sys_updated_on');
if (ritm.hasNext())
a.short_description = ritm.getRowCount() + ' requested items';
else
a.short_description = ritm.cat_item.getDisplayValue() || ritm.getDisplayValue("short_description");
a.__table = z.getTableName();
a.type = 'cdl_request';
a.sortOrder = z.sys_updated_on.getGlideObject().getNumericValue();
t.items.push(a);
}
t.items.sort(function(a, b) {
return b.sortOrder - a.sortOrder;
});
t.items = t.items.slice(0, max); // only want first 30
t.count = t.items.length;

var link = {title: gs.getMessage('View all logs'), type: 'link', href: '?id=cdl_requests', items: []};
t.items.unshift(link); // put 'View all logs' first