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 

Thank you for marking my response as helpful.

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

[Dharma Liyanage]

@rajeshKongamudi 

Somewhat similar implementation: get use of sc_cat_item_group_mtom table to store catalog items.

1. Navigate to the Service Catalog Item [sc_cat_item] table User Group reference field &  Add this script to the "Reference qual" field in the Dictionary entry
2. Provides different filtering logic for new vs. existing catalog items for an example
3. For existing items, it includes:
   • Groups with catalog management roles
   • Groups explicitly permitted for this catalog item (via a many-to-many sc_cat_item_group_mtom table)
   • Groups associated with the catalog item's category
4. For new items, it shows active groups by default

You can customize the qualifier logic based on your specific business requirements.

// Advanced reference qualifier for the sys_user_group reference field on sc_cat_item table

function refQualifier() {
// Get the current catalog item's sys_id
var catalogItemId = current.sys_id;

// Initialize the qualifier string - will be expanded based on conditions
var qualifier = '';

// If this is a new catalog item being created
if (catalogItemId == '' || !catalogItemId) {
// For new items, we'll use a basic condition to show active groups
qualifier = 'active=true';
} else {
// For existing items, build a more complex qualifier

// First, get any groups that have specific permissions for this catalog item
var permissionsGr = new GlideRecord('sc_cat_item_group_mtom');
permissionsGr.addQuery('sc_cat_item', catalogItemId);
permissionsGr.query();

var permittedGroups = [];
while (permissionsGr.next()) {
permittedGroups.push(permissionsGr.getValue('group'));
}

// Include groups that have catalog management roles
qualifier = 'active=true^role=catalog_manager';

// If we found any explicitly permitted groups, include those as well
if (permittedGroups.length > 0) {
qualifier += '^ORsys_idIN' + permittedGroups.join(',');
}

// If the catalog item has a specific category, we might want to limit to groups associated with that category
if (current.category) {
var categoryId = current.getValue('category');
qualifier += '^ORcategory=' + categoryId;
}
}

return qualifier;
}

Hope this helps 🙂