Using a Dynamic Filter Option with an Interactive Filter

[JamesLindsay]

I have a table where a row consists of multiple services listed in the cmdb_ci_service in a hierarchical order based on service classification, imagine L1, L2, L3-L6, etc. at each L level, the associated Owner (u_l1_owner) and Lead (u_l1_lead) is identified. So a row may look like:

This is the Service from cmdb_ci_service

Field	u_l1
Type	reference
Reference	cmdb_ci

, This is the service classification from the  cmdb_ci_service CI

Field	u_sc1
Type	string

,

Field	u_l1_owner
Type	reference
Reference	sys_user

,

Field	u_l1_lead
Type	reference
Reference	sys_user

As I said, each row repeats L1-L6 and all rows are unique.
I am trying to provide an interactive filter on a Dashboard that allows someone to pick a name and display all the rows where u_l1_owner is X or u_l2_owner is X or u_l3_owner is X of u_l4_owner is X or u_l5_owner is X or u_l6_owner is X.

I have created a script Include:

var ServiceStackUserFilter = Class.create();

ServiceStackUserFilter.prototype = {

    initialize: function() {},

    getStackByUser: function() {

        var userSysId = this.getParameter('sysparm_user_sys_id');

        if (!userSysId) {

            return '';

        }

        var qc = new GlideRecord('u_service_path');

        qc.addQuery('u_l1_owner', userSysId)

            .addOrCondition('u_l2_owner', userSysId)

            .addOrCondition('u_l3_owner', userSysId)

            .addOrCondition('u_l4_owner', userSysId)

            .addOrCondition('u_l5_owner', userSysId)

            .addOrCondition('u_l6_owner', userSysId);

        qc.query();

        var sysIds = [];

        while (qc.next()) {

            sysIds.push(qc.getValue('sys_id'));

        }

        if (sysIds.length > 0) {

            return 'sys_idIN' + sysIds.join(',');

        } else {

            return 'sys_id=NULL';

        }

    },

    type: 'ServiceStackUserFilter'

};

I have created a Dynamic Filter Option.

And I have created an Interactive Filter

 

What I cannot figure out is how to align/use the dynamic filter option with the interactive filter. Everything I've tried shows the Interactive filter "Related list is empty" in the Dashboard. The only way I get anything is if I add an Interactive Filter Reference with the New UI action on the related list for the Interactive Filter.

However, adding them creates an AND condition for each field instead of an OR which is what the dynamic filter option is supposed to do for me. I just can't figure out how to get the three elements (Script Include, Dynamic Filter Option, Interactive filter) to work together. I have tested the script include and it returns the expected results. Maybe I'm just barking up the wrong tree entirely.
Any help and input is much appreciated.

[G Ponsekar]

Hi @JamesLindsay 

1. Re-check your Dynamic Filter Option

The configuration of your Dynamic Filter Option is the most critical step. Ensure the following settings are exactly as shown:

• Name: Service Stack by User (or your preferred name).
• Table: User [sys_user]. This is the table the Interactive Filter will reference.
• Script: ServiceStackUserFilter.getStackByUser(). This is correct.
• Reference Field: This field needs to be a reference to a User on your Interactive Filter's table. Since your Interactive Filter uses the User [sys_user] table, this is not needed for the Interactive Filter itself, but for the Interactive Filter's Reference on the dashboard.

2. Configure the Interactive Filter

The Interactive Filter itself needs to be of type Reference and configured to use your Dynamic Filter Option.

• Name: Service Stack User (or your preferred name).
• Type: Reference.
• Reference table: User [sys_user].
• Field: This field is less critical for the dynamic filter logic but is often set to Name.
• Filter Condition: The magic happens here. In the condition builder, set the condition to Filter is Service Stack by User. This tells the Interactive Filter to use your Dynamic Filter Option script instead of a standard query.

3. Link the Interactive Filter to your Dashboard Reports

The Interactive Filter on your dashboard needs to be linked to your report widgets. The Interactive Filter Reference records on your Interactive Filter are a different mechanism and should not be used in this dynamic filtering scenario, as they enforce AND conditions.

1. Add the Interactive Filter Widget: Go to your dashboard and add an Interactive Filter widget.
2. Configure the Widget: In the widget's instance options, select your Service Stack User Interactive Filter.
3. Configure Dashboard Reports: For each list or report widget on your dashboard that you want the filter to affect:
   • Click the gear icon or edit the widget.
   • Select the Follow interactive filter checkbox.

4. How the components work together

1. User Selects User: A user selects a name from the Interactive Filter. This name corresponds to a sys_id in the User [sys_user] table.
2. Interactive Filter Calls Dynamic Filter Option: The Interactive Filter runs the ServiceStackUserFilter.getStackByUser() function, passing the selected user's sys_id as the sysparm_user_sys_id parameter.
3. Script Include Generates Query: Your script include runs the multi-OR query on your custom table u_service_path and returns a string like sys_idIN<list of sys_ids>.
4. Dashboard Filter Applies Query: The Interactive Filter applies the filter string returned by the script to all reports and lists on the dashboard that are configured to "Follow interactive filter."

Troubleshooting "Related list is empty"

If you still see "Related list is empty," here are a few things to double-check:

• Cache: Clear your browser and ServiceNow cache (cache.do) after making changes.
• Interactive Filter Field: Ensure your Interactive Filter is using the sys_user table and the filter condition is correctly set to use your dynamic filter option.
• Report Configuration: Make sure every report on the dashboard that you expect to be filtered has the "Follow interactive filter" option enabled.
• Widget Refresh: Sometimes, a report or list widget may require a manual refresh after the filter is applied.

If I could help you with your Query then, please hit the Thumb Icon and mark as Correct !!

Thanks, GP

[JamesLindsay]

Thank you for your reply. In step 1, there is no Reference Field selection, there is a Field Type

In Step 2 here, I am not seeing the Dynamic Filter Option as a selection option and I'm not sure where I should expect to see it in the Filtering section. I've simply typed the name in here.

No mapping found for Interactive Filter Based On Reference
In the Interactive Filter record, scroll down to the Interactive Filter References related list and click New to add a new Interactive Filter References record.

[Bert_c1]

I have defined Dynamic filter Options with Field type 'Reference'. See below, why use a type of 'Sys ID'.