remove duplicates from the gr records retrieved in ui page

hemali1 · ‎06-08-2017

I am trying to remove the duplicates from the glide records I retrieve in the UI page. Following is the script:

<?xml version="1.0" encoding="utf-8" ?>

<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">

<html>

<body>

<j:set var="jvar_user_group" value="" />

<g:evaluate var="jvar_pm_project_record" object="true" jelly="true">

var grp = new GlideRecord('sys_user_group');

grp.addQuery('name', '');

grp.query();

</g:evaluate>

<j:if test="">

<g:evaluate object="true">

var wval = new GlideRecord('sys_variable_value');

wval.addQuery('value','');

wval.query();

</g:evaluate>

<h1><span style="color: #808080;">Group: <br /></span></h1>

<tbody>

<td style="width: 430px; height: 40px; font-size:20px"><B>Group Name</B></td>

<td style="width: 500px; height: 40px; font-size:20px"><B>Workflow/Catalog Item</B></td>

</tr>

<j:while test="">

<g:evaluate object="true">

var wact = new GlideRecord('wf_activity');

wact.addQuery('sys_id','');

wact.query();

</g:evaluate>

<j:while test="">

</tr>

</j:while>

</tbody>

</table>

</j:if>

</body>

</html>

</j:jelly>

andrewpilachows · ‎06-08-2017

You have a groupBy in the second example, unnecessary with array building. In theory, the "gr" parts would work in a script. I'm not sure how to debug this, but as long as ${wval.document_key} is actually evaluating correctly to a wf_activity sys_id, this should be about what you need. Do you have read permission on the wf_activity table? Where is wval coming from?

You need to add a variable name to your evaluate block.

Jelly Tags - ServiceNow Wiki

<g:evaluate var="jvar_gr" object="true">

var gr = new GlideRecord('wf_activity');

gr.addQuery('sys_id','${wval.document_key}');

gr.groupBy('workflow_version');

gr.query();

gr;

</g:evaluate>

<j:while test="${jvar_gr.next()}">

<td style="width: 430px; height: 43px;">Workflow Name:</td>

<td style="width: 500px; height: 43px;">${jvar_gr.workflow_version.name}</td>

</tr>

</j:while>

View solution in original post

andrewpilachows · ‎06-08-2017

Use the GlideAggregate group by to return a distinct record set, but you will need to know which field makes a record a duplicate.

GlideAggregate - ServiceNow Wiki

hemali1 · ‎06-08-2017

I am doing a glide record query on wf_activity table. It retrieves all the record with a particular sys_id I provide like the following:

<g:evaluate object="true">

var gr = new GlideRecord('wf_activity');

gr.addQuery('sys_id','I enter sys_id here');

gr.query();

</g:evaluate

Then I have while tag that runs through the retrieved records to get the associated workflow version name:

<j:while test="${gr.next()}">

<td style="width: 430px; height: 43px;">Workflow Name:</td>

<td style="width: 500px; height: 43px;">${gr.workflow_version.name}</td>

</tr>

</j:while>

In the above list, what I get is duplicate workflow version names since there would be more than one workflow activity pointing to a workflow. I don't know how would I use GlideAggregate here to help me remove the duplicates and display a workflow name only once. Can you please guide me on this

andrewpilachows · ‎06-08-2017

Instead of GlideRecord, use GlideAggregate. After addQuery, put in a groupBy.

GlideAggregate - ServiceNow Wiki

A longer alternative would be to do array manipulation with the returned query.

ServiceNow Admin 101: You Too Can Do DISTINCT Queries Using GlideRecord

hemali1 · ‎06-08-2017

I tried following but none of it works:

(1)

var wact = new GlideAggregate('wf_activity');

wact.addQuery('sys_id','${wval.document_key}');

wact.groupBy('workflow_version');

wact.query();

Try to display : <td style="width: 500px; height: 43px;">${wact.workflow_version.getDisplayValue()}</td>

(2)

var workflow = [];

var wact = new GlideAggregate('wf_activity');

wact.addQuery('sys_id','${wval.document_key}');

wact.groupBy('workflow_version.name');

wact.query();

while(wact.next()){workflow.push(wact.workflow_version.name.trim());}

var arrayUtil = new ArrayUtil();

workflow = arrayUtil.unique(workflow);

Try to display : <td style="width: 500px; height: 43px;">${workflow[i])}</td>

None of the above methods is working.