Find flows that trigger actions on a table

[Robert Ninness]

Here's the question we will try to answer: Which flows can cause updates to records on the incident table?

The ScriptableFlowAssociation GlideScriptable (which drives the "Flow Designer Flows" UI Action) associates flows to a table through trigger records, we can learn from this scriptable, however we need to look at actions not triggers.

When an action is added to a flow, an action instance (sys_hub_action_instance) record is created. This record is an instance of an Action Type (sys_hub_action_type_definition). Actions have variables. For example: an Update Record action has table, record and field variables. There is a complex schema linking Action Types and Variable Definitions to flows, but all we really need to know is that there is a set of tables that define actions and variables, and another set that stores the instances of those actions and their variables.

Action Instance schema map. Not seen here are references from variables that use document/document_key references.

 Getting back to answering our question, we want a list of flows. The Action Instance table contains a reference to flow, all we need to do is filter this list of Action Instances down to those that have a table variable set to incident.

To do this, we need to join the sys_hub_action_instance and sys_variable_value tables. Unfortunately, due to complexities in the schema, the variables don't directly reference action instances instead they use a document/document_key structure.

Instead of a simple dot walk, we can use the addJoinQuery() GlideRecord function to create the query. Since the action instance holds the reference to flow and we will need the value of it, the sys_hub_action_instance table will be the primary GlideRecord and the sys_variable_value table will be used in the join.

Here is a code snippet.

var actions = new GlideRecord('sys_hub_action_instance');
var action_variables = actions.addJoinQuery('sys_variable_value', 'sys_id', 'document_key');

action_variables.addCondition('value', 'incident');

actions.query();

var flows = [];
while(actions.next()){
    var flow = actions.getValue('flow');
    if(flows.indexOf(flow)>-1) continue;
    flows.push(flow);
}

It's very likely that a flow may have multiple actions updating incidents (particularly those with branching logic) so we want to try and de-duplicate the list. (this code can be much simpler using modern JavaScript features but for compatibilities sake I'll keep it safe for now)

I hope you've enjoyed looking under the hood of flows, there is complexity there, and it's may take a while for it to all make sense, but there are still reasonable ways of finding the information you need if you invest a little bit of curiosity into a problem.

[Maik Skoddow]

Hi @Robert Ninness 

unfortunately, your code is not correct as you are not filtering the referenced "flow" records. You will get many false values as they represent records from table sys_hub_flow_snapshot.

And also storing the Sys IDs in an array to filter out duplicates is inefficient as the same can be achieved via GlideAggregate API

My optimized and correct version, which also distinguishes between active and inactive flows, is:

var grActionInstance  = new GlideAggregate('sys_hub_action_instance');

grActionInstance
    .addJoinQuery('sys_variable_value', 'sys_id', 'document_key')
    .addCondition('value', 'wm_task');
grActionInstance.addAggregate('count'); 
grActionInstance.orderByAggregate('count'); 
grActionInstance.groupBy('flow');
grActionInstance.addQuery('flow.sys_class_name', 'sys_hub_flow')
grActionInstance.query();

while(grActionInstance.next()) {
    gs.info(
        '[{0}]\t{1}', 
        grActionInstance.flow.active ? 'active' : 'inactive', 
        grActionInstance.flow.name
    );
}

And this is how the output looks like:

[Robert Ninness]

@Maik Skoddow, this is a great improvement! I appreciate the extra info about the snapshot records. I'm still learning about the flow schema and missed this nuance.

I think a lot of people forget sometimes (me included) that GlideAggregate extends GlideRecord, so addJoinQuery is possible.

I will add one additional enhancement now that we are using GlideAggregate. I can use the aggregate GROUP_CONCAT_DISTINCT instead of grouping by flow to pull out a list of distinct flow IDs. I can also change the grouping to 'flow.active' to achieve the same split out you did in your example.

var grActionInstance  = new GlideAggregate('sys_hub_action_instance');

grActionInstance
    .addJoinQuery('sys_variable_value', 'sys_id', 'document_key')
    .addCondition('value', 'change_request');
grActionInstance.addAggregate('GROUP_CONCAT_DISTINCT', 'flow.sys_id');
grActionInstance.groupBy('flow.active');
grActionInstance.addQuery('flow.sys_class_name', 'sys_hub_flow')
grActionInstance.query();

while(grActionInstance.next()) {
    gs.info(
        '[{0}]\t{1}', 
        grActionInstance.getValue('flow.active')==='1' ? 'active' : 'inactive', 
        grActionInstance.getAggregate('GROUP_CONCAT_DISTINCT', 'flow.sys_id')
    );
}

If I wanted just one list of all sys_ids (similar to my original example) I can change the group to a field that will give me only one group. In this case groupBy('flow.sys_class_name') will to the trick.

P.s. Appears that grouping by a field will remove the ability to dot walk through it so I had to change to using getValue('flow.active') which returns a string that is always thruthy

[Maik Skoddow]

Hi @Robert Ninness 

Wow! I had never heard of the "GROUP_CONCAT_DISTINCT" option before! So I learned something new today 😊

Maik

[Robert Ninness]

Original script re-written using GlideAggregate and filtering out flow snapshots

var actions = new GlideAggregate('sys_hub_action_instance');
actions.addQuery('flow.sys_class_name', 'sys_hub_flow');

var action_variables = actions.addJoinQuery('sys_variable_value', 'sys_id', 'document_key');

action_variables.addCondition('value', 'incident');

actions.addAggregate('GROUP_CONCAT_DISTINCT', 'flow.sys_id');
actions.groupBy('flow.sys_class_name');

actions.query();

var flows = [];

if(actions.next())
	flows = actions.getAggregate('GROUP_CONCAT_DISTINCT', 'flow.sys_id').split(',');

[Robert Ninness]

@Maik Skoddow We are both learning new things. Love it!

GROUP_CONCAT_DISTINCT is a hidden gem! It can often be much faster to use it rather than looping over a results set from GlideRecord.

[SwarnadeepNandy]

Thanks for the article @Robert Ninness ,
Is there any way where we can find out which flows are running or potentially can run on a table?
For eg: in BR I can simply put filter like "table=incident"
Is there any similar way to figure out flows which will be triggered when a record is inserted or updated in a table?
Thanks,
Swarnadeep Nandy

[Robert Ninness]

@SwarnadeepNandy The simplest way is to go to a list view of the table, open the right click context menu and select Configure -> Flow Designer Flows. 

You can see this context menu item here: /sys_ui_context_menu.do?sys_id=26a64f613718030003a08fb77e41f193

The context menu item uses a script to generate a query string

var query = 'sys_id=javascript:sn_flow.AssociatedFlows.getFlows("'+tableName+'")^active=true';

In the list view of sys_hub_flow you can use part of this scriptlet in combination with a sys_id "is" or "is one of" operators e.g. javascript:sn_flow.AssociatedFlows.getFlows("incident")

Hope this helps.

~Robert