Before Query Business Rule to restrict Catalog Items based on Available for/Not available for evaluation

[Mark233]

I have a requirement to restrict view permissions to Catalog Items based on whether or not the Item is available for the current user to request.

This is working correctly in terms of whether or not the user can request the Item in the Service Portal, however all itil users can view all Catalog Items in the list view in the back end ( ie https://<instance_name>.service-now.com/nav_to.do?uri=%2Fsc_cat_item_list.do ).

E.G. of current scenario: Catalog Item A is not available for itil User A. User A cannot request the Item in the Service Portal. User A can however see the Catalog Item and all the details of it at https://<instance>.service-now.com/nav_to.do?uri=%2Fsc_cat_item.do%3Fsys_id=<sys_id of Catalog Item>

How can I create a before query business rule that restricts queries against sc_cat_item to just those which are evaluated as available for the user?

I did the below, however it resulted in an issue when clicking into an item from a list view:

• Created Database View of sc_cat_item with Left Join to both sc_cat_item_user_criteria_mtom and sc_cat_item_user_criteria_no_mtom
• Create before query business rule which:
• • Queries the database view for all Items where either:
  • • The item has no value in Available for (so it is available for everyone)
    • The Available for user criteria is one of the user's criteria
  • Queries for all Items where the Not available for is one of the user's criteria
  • Remove any items from the available array where the item is in the not available array
  • Add condition to the current query of the sc_cat_item table that the sys_id is in the available for array

This works and restricts as expected, however when clicking into an item from a list view the call back URL is so long that the page gives a 400 error. This is because it stores the query from the previous list view in the URL and that query contains the sys_id of every available Catalog Item.

Business Rule below for anyone interested, however I need a new approach. Any help would be much appreciated.

(function executeRule(current, previous /*null when async*/) {

	var user_criteria = get_current_user_criteria_csv_string();
	var not_available_for_items = get_not_available_for_items(user_criteria);
	var available_for_items = get_available_for_items(user_criteria);
	var resultant_available_items = get_resultant_items(available_for_items, not_available_for_items);
	// Restrict returned records to those evaluated as available
	current.addQuery('sys_id', 'IN', resultant_available_items.join(','));
	
})(current, previous);

function get_current_user_criteria_csv_string() {
	/* 
	 * Below call returns java array, using + "" to cast to JavaScript string to prevent issue
	 * when using replace: Ambiguous call to String.replace() on java.lang.Strings
	 * Blog post explaining: https://community.alfresco.com/thread/175439-ambiguous-call-to-stringreplace-on-javalangstrings
	 */
	var user_criteria = SNC.UserCriteriaLoader.getAllUserCriteria() + "";
	return user_criteria.replace(/\[|\]| /g, "");
}

function get_available_for_items(user_criteria) {
	var gr = new GlideRecord('u_sc_cat_item_available_for_mtoms');
	var availablefor_query = gr.addQuery('availablefor_user_criteria', 'IN', user_criteria);
	availablefor_query.addOrCondition('availablefor_user_criteria', '');
	var items = [];
	gr.query();
	while (gr.next())
		items.push(gr.catitem_sys_id.toString());
	return new ArrayUtil().unique(items);
}

function get_not_available_for_items(user_criteria) {
	// @todo if you get this working - make query function to be used by both
	// available for and not avaiable for functions. This is the opposite of DRY
	var gr = new GlideRecord('u_sc_cat_item_available_for_mtoms');
	var notavailablefor_query = gr.addQuery('notavailablefor_user_criteria', 'IN', user_criteria);
	notavailablefor_query.addOrCondition('notavailablefor_user_criteria', '');
	var items = [];
	gr.query();
	while (gr.next())
		items.push(gr.catitem_sys_id.toString());
	return new ArrayUtil().unique(items);
}

function get_resultant_items(available_items, not_available_for_items) {
	// Remove all the not available for sys_ids from the available for array
	var resultant_items = new ArrayUtil().diff(available_items, not_available_for_items);
	return resultant_items;
}

Thanks for reading!

[Mark233]

PS I can't use ACLs (I don't think) to avoid the x number of rows removed issue when looking at a list view