Hi Community, Request for Assistance in Dynamically Adding Reference Qualifier via Script

[rajeshKongamudi]

We are attempting to implement a Before Query Business Rule on the Portfolio table that filters out records based on their association with Service Catalog Items (sc_cat_item). Specifically, when the “Apply to Catalog Item” field is set to true, we want to ensure that any Portfolio referenced in Catalog Items is automatically excluded from query results—but only when the query originates from the sc_cat_item table.

The goal is to dynamically add a filter to hide these Portfolios during selection in Catalog Items, while ensuring that this rule does not impact queries from other tables. (Note: instead of using reference qualifier we need some automation process here)

We are currently using a script and a system property to retrieve the calling table name and construct the query accordingly. However, we are encountering challenges ensuring that the filtering applies only to the sc_cat_item context, without affecting other tables.

Could you please assist us in refining the approach or suggest a solution that meets the above requirement?

// (function() {

//     var allowedTables = gs.getProperty('glide.sys_reference_table', '');

//     gs.log('Notification: Fetching allowed tables from system property. Allowed tables: ' + allowedTables, 'CustomScript'); // Log the allowed tables from the system property

//     if (allowedTables) {

//         // Split the allowed tables from the system property and trim spaces

//         var tables = allowedTables.split(',').map(function(t) {

//             return t.trim();

//         });

//         gs.log('Notification: Allowed tables parsed successfully: ' + tables.join(', '), 'CustomScript'); // Log the parsed tables

//         // Check if 'sc_cat_item' (Service Catalog Item) is in the allowed tables list

//         if (tables.indexOf('sc_cat_item') !== -1) {

//             gs.log('Notification: Service Catalog Item (sc_cat_item) found in the allowed tables. Proceeding with GlideRecord query.', 'CustomScript'); // Log when the table is allowed

//             var gr = new GlideRecord('sys_user_group'); // Use 'sys_user_group' as the table for query

//             gr.addQuery('u_apply_to_catalog_item', false);

//             gr.query();

//             var ids = [];

//             while (gr.next()) { // Loop through each record returned by the query

//                 gs.log('Notification: Found sys_id: ' + gr.sys_id + ' for the user group.', 'CustomScript'); // Log the sys_id of the found group

//                 ids.push(gr.sys_id); // Push the sys_id to the ids array

//             }

//             // Return the sys_ids as a filter for the catalog item reference qualifier after the loop completes

//             gs.log('Notification: Returning the following sys_ids for the catalog item reference qualifier: ' + ids.join(','), 'CustomScript'); // Log the final sys_ids returned

//             return ids.join(',');

//         } else {

//             gs.log('Notification: Service Catalog Item (sc_cat_item) NOT found in the allowed tables list. Query will not proceed for this table.', 'CustomScript'); // Log if sc_cat_item is not allowed

//         }

//     } else {

//         gs.log('Error: Allowed tables are not set or empty in system property. Please configure correctly.', 'CustomScript'); // Log if allowedTables is missing or empty

//     }

//     return '';

// })();

(function() {

        // Retrieve allowed tables from the system property

        var allowedTables = gs.getProperty('glide.sys_reference_table', '');

        gs.log('Notification: Fetching allowed tables from system property. Allowed tables: ' + allowedTables, 'CustomScript');

        if (allowedTables) {

            // Split the allowed tables from the system property and trim spaces

            var tables = allowedTables.split(',').map(function(t) {

                return t.trim();

            });

            gs.log('Notification: Allowed tables parsed successfully: ' + tables.join(', '), 'CustomScript');

            // Check if 'sc_cat_item' (Service Catalog Item) is in the allowed tables list

            if (tables.indexOf('sc_cat_item') !== -1) {

                gs.log('Notification: Service Catalog Item (sc_cat_item) found in the allowed tables. Proceeding with GlideRecord query.', 'CustomScript');

                // Query the 'sys_user_group' table where u_apply_to_catalog_item is false

                var gr = new GlideRecord('sys_user_group');

                gr.addQuery('u_apply_to_catalog_item', false);

                gr.query();

                var ids = [];

                while (gr.next()) { // Loop through each record returned by the query

                    gs.log('Notification: Found sys_id: ' + gr.sys_id + ' for the user group.', 'CustomScript');

                    ids.push(gr.sys_id); // Push the sys_id to the ids array

                }

                // If we found any sys_ids, prepare the reference qualifier for the catalog item field

                if (ids.length > 0) {

                    var refQualifier = 'sys_idIN' + ids.join(','); // Create the reference qualifier string

                    gs.log('Notification: Returning the following sys_ids for the catalog item reference qualifier: ' + refQualifier, 'CustomScript');

                    // Loop through all variables (fields) in the catalog item to apply the filter

                    var catItemGR = new GlideRecord('sc_cat_item');

                    catItemGR.addQuery('sys_id', current.sys_id); // Query for the current catalog item (or other condition as needed)

                    catItemGR.query();

                    gs.log('iTEM IS WORKING FINE');

                    if (catItemGR.next()) {

                        // Loop through all variables of the catalog item

                        var varGR = new GlideRecord('item_option_new'); // Get the variables (fields) for the catalog item

                        varGR.addQuery('catalog_item', catItemGR.sys_id);

                        gs.log('iTEM OPTION IS WORKING FINE');

                        varGR.query();

                        while (varGR.next()) {

                            gs.log('while iTEM IS WORKING FINE');

                            // Check if the variable's reference field refers to the sys_user_group table

                            if (varGR.type == 'reference' && varGR.reference.indexOf('sys_user_group') !== -1) {

                                // Apply the reference qualifier to filter groups based on sys_ids

                                varGR.setValue('reference_qualifier', ids); // Set the reference qualifier dynamically

                                gs.log('Applied reference qualifier to variable ' + ids + ' in catalog item.', 'CustomScript');

                                gs.log('Notification: Applied reference qualifier to variable ' + varGR.name + ' in catalog item.', 'CustomScript');

                                varGR.update(); // Update the variable record with the new reference qualifier

                            }

                        }

                    } else {

                        gs.log('Error: No catalog item record found for the current sys_id.', 'CustomScript');

                    }

                    return refQualifier; // Return the reference qualifier string

                } else {

                    gs.log('Error: No sys_ids found for the condition u_apply_to_catalog_item = false', 'CustomScript');

                }

            } else {

                gs.log('Notification: Service Catalog Item (sc_cat_item) NOT found in the allowed tables list. Query will not proceed for this table.', 'CustomScript');

            }

        } else {

            gs.log('Error: Allowed tables are not set or empty in system property. Please configure correctly.', 'CustomScript');

        }

        return '';

})();

[Ankur Bawiskar]

@rajeshKongamudi 

why not use advanced ref qualifier rather than using query business rule?

query business rule will impact all fields in instance which refer to this table

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@rajeshKongamudi 

Hope you are doing good.

Did my reply answer your question?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[rajeshKongamudi]

@Ankur Bawiskar 

The client proposes that in the sc_cat_item table, if a field is referencing the user group table, and the group has the attribute u_apply_to_catalog_item = false (because we are currently working on a PDI), then the filter should apply only to those records where the group is referenced in the sc_cat_item table.

Additionally, the client requires that if a group is referenced in approximately 30 catalog items, the reference qualifier should be applied to each of these catalog items. The reference qualifier should only filter records where the user group is inactive (i.e., records where the active flag is false).

Moreover, this logic should only be applicable to sc_cat_item table records, and not to any other tables such as the portfolio table.

In summary, the client requests that:

1. The filter should apply only to sc_cat_item records that reference the user group.

2. The logic should only be relevant to the sc_cat_item table, not the portfolio table.

(function() {

        // Retrieve allowed tables from the system property

        var allowedTables = gs.getProperty('glide.sys_reference_table', '');

        gs.log('Notification: Fetching allowed tables from system property. Allowed tables: ' + allowedTables, 'CustomScript');

        if (allowedTables) {

            // Split the allowed tables from the system property and trim spaces

            var tables = allowedTables.split(',').map(function(t) {

                return t.trim();

            });

            gs.log('Notification: Allowed tables parsed successfully: ' + tables.join(', '), 'CustomScript');

            // Check if 'sc_cat_item' (Service Catalog Item) is in the allowed tables list

            if (tables.indexOf('sc_cat_item') !== -1) {

                gs.log('Notification: Service Catalog Item (sc_cat_item) found in the allowed tables. Proceeding with GlideRecord query.', 'CustomScript');

                // Query the 'sys_user_group' table where u_apply_to_catalog_item is false

                var gr = new GlideRecord('sys_user_group');

                gr.addQuery('u_apply_to_catalog_item', false);

                gr.query();

                var ids = [];

                while (gr.next()) { // Loop through each record returned by the query

                    gs.log('Notification: Found sys_id: ' + gr.sys_id + ' for the user group.', 'CustomScript');

                    ids.push(gr.sys_id); // Push the sys_id to the ids array

                }

                // If we found any sys_ids, prepare the reference qualifier for the catalog item field

                if (ids.length > 0) {

                    var refQualifier = 'sys_idIN' + ids.join(','); // Create the reference qualifier string

                    gs.log('Notification: Returning the following sys_ids for the catalog item reference qualifier: ' + refQualifier, 'CustomScript');

                    // Loop through all variables (fields) in the catalog item to apply the filter

                    var catItemGR = new GlideRecord('sc_cat_item');

                    catItemGR.addQuery('sys_id', current.sys_id); // Query for the current catalog item (or other condition as needed)

                    catItemGR.query();

                    gs.log('iTEM IS WORKING FINE');

                    if (catItemGR.next()) {

                        // Loop through all variables of the catalog item

                        var varGR = new GlideRecord('item_option_new'); // Get the variables (fields) for the catalog item

                        varGR.addQuery('catalog_item', catItemGR.sys_id);

                        gs.log('iTEM OPTION IS WORKING FINE');

                        varGR.query();

                        while (varGR.next()) {

                            gs.log('while iTEM IS WORKING FINE');

                            // Check if the variable's reference field refers to the sys_user_group table

                            if (varGR.type == 'reference' && varGR.reference.indexOf('sys_user_group') !== -1) {

                                // Apply the reference qualifier to filter groups based on sys_ids

                                varGR.setValue('reference_qualifier', ids); // Set the reference qualifier dynamically

                                gs.log('Applied reference qualifier to variable ' + ids + ' in catalog item.', 'CustomScript');

                                gs.log('Notification: Applied reference qualifier to variable ' + varGR.name + ' in catalog item.', 'CustomScript');

                                varGR.update(); // Update the variable record with the new reference qualifier

                            }

                        }

                    } else {

                        gs.log('Error: No catalog item record found for the current sys_id.', 'CustomScript');

                    }

                    return refQualifier; // Return the reference qualifier string

                } else {

                    gs.log('Error: No sys_ids found for the condition u_apply_to_catalog_item = false', 'CustomScript');

                }

            } else {

                gs.log('Notification: Service Catalog Item (sc_cat_item) NOT found in the allowed tables list. Query will not proceed for this table.', 'CustomScript');

            }

        } else {

            gs.log('Error: Allowed tables are not set or empty in system property. Please configure correctly.', 'CustomScript');

        }

        return '';

})();

[Ankur Bawiskar]

@rajeshKongamudi 

so what's not working in the advanced ref qualifier?

did you add logs and see?

Something like this in advanced ref qualifier on variable referring to sc_cat_item should work

var CatalogItemFilter = Class.create();
CatalogItemFilter.prototype = {
    initialize: function() {},

    filterCatalogItems: function() {
        var arr = [];
        var gr = new GlideRecord('sc_cat_item');
        gr.addQuery('groupField.u_apply_to_catalog_item', false);
        gr.query();
        while (gr.next()) {
            var userGroup = gr.groupField.getRefRecord();
            if (userGroup && !userGroup.active) {
                arr.push(gr.getUniqueValue());
            }
        }
        return 'sys_idIN' + arr.toString();
    },

    type: 'CatalogItemFilter'
};

Ref qualifier as this

javascript: new CatalogItemFilter().filterCatalogItems();

If my response helped please mark it correct and close the thread so that it benefits future readers.