Glide record query on workflow activity vars

[Kit Sanders2]

I want to run a query on the wf_activity table to return all approval activities where the group variable contains a certain value.

Here is what I tried, but it returns every approval group/user activity and doesn't filter on the group:

var group = 'd39eeb223cf6c584d8a4968c8d20e6de'; //sys_id of group

var wfa = new GlideRecord('wf_activity');

wfa.addQuery('activity_definition','IN','354e911f0a0a029a00e6a0e6ad74206f,35433da80a0a029a0028c639a1e51eb4'); //approval - group and approval - user

wfa.addQuery('vars.groups','CONTAINS', group);

wfa.query();

while(wfa.next()) {

        gs.info(wfa.activity_definition.name + ' - ' + wfa.vars.groups);

}

Maybe it's because wfa.vars.groups is an object not a string? What is the correct syntax for querying vars.groups?

If I run the query like this it returns the workflow activities I need but takes too long to execute:

var group = 'd39eeb223cf6c584d8a4968c8d20e6de'; //sys_id of group

var wfa = new GlideRecord('wf_activity');

wfa.addQuery('activity_definition','IN','354e911f0a0a029a00e6a0e6ad74206f,35433da80a0a029a0028c639a1e51eb4'); //approval - group and approval - user

wfa.query();

while(wfa.next()) {

        if(wfa.vars.groups.toString().indexOf(group) > -1) {

                  gs.info(wfa.activity_definition.name + ' - ' + wfa.vars.groups);

        }

}

Any ideas?

Thanks,

Kate

[Goran WitchDoc · Accepted solution]

Your query is taking a long time since you are dotwalking in the if-statement which means that for each "dotwalk" it needs to go to the server again and fetch that record.

Now, it kind of depends if this is a one time deal or something you want to do on regular basic.

Here is a solution for a one time deal:

all the variables are saved in the table: sys_variable_value. Sad thing here is that it doesn't use reference field here, but a document ID, so you can't dot walk here to the query..

But here is a way to do it. Made to be run in Scrips - background, Fix Script, Xplore (share app) or something similar:

var encQuery = 'workflow_version.published=true^activity_definition=35433da80a0a029a0028c639a1e51eb4^ORactivity_definition=354e911f0a0a029a00e6a0e6ad74206f';// Made the encoded query in the normal listview

var wfAct = new GlideRecord('wf_activity');

wfAct.addEncodedQuery(encQuery);

wfAct.query();

var activitySys = [];//Lets put all the activities we find in a array;

while(wfAct.next()) {

activitySys.push(wfAct.getUniqueValue());

}

//Now lets go into the value and see how many of these has the correct group value

var myGroupSys = 'b85d44954a3623120004689b2d5dd60a';//Here you put your group in

var getValues = new GlideRecord('sys_variable_value');

getValues.addQuery('document_key','IN',activitySys); //Find only the records that has the correct acitivity

getValues.addQuery('value',myGroupSys);//And only those with the group sys_id in value

getValues.addQuery('variable.label','Groups');//Need the label to since there is multiple records with label groups and we only want those where our group value is in the group variable.

getValues.query();

//show how many

gs.debug(getValues.getRowCount());

//Get all the sys_ids of the activites

var getAct = [];

while (getValues.next()) {

getAct.push(getValues.getValue('document_key'));

}

          for (var y in getAct) {

gs.debug(getAct[y]);

}

[Kit Sanders2]

Hi Goran, that works a treat!

Thank you for taking the time to help me out.  

[sunilsafare]

Hi Goran,

Is it possible to update these values from background script? I am able to retrieve the values and update it. But it is not reflecting in the workflow editor for that particular activity.

Thanks,Sunil Safare