Before Query Business Rule Script help

[Valon Sheremeti]

Hi all. I was hoping if I can pick your brain to complete business rule script.
Goal: I need to restrict access to certain catalog item variable values so only the following users can query them:

1. Any member of group named "MyGroup" 
OR
2. User who filled the variable itself (this is the userID in sys_created_by field in the table)

I've come with the following script and I was hoping if you can help me complete the part of the script in red.
P.S. I was able to get it work by hard coding the sys_id of certain user, but I am unable to make it dynamic for any logged user.

Business Rule
Table: Options [sc_item_option]
When: Before Query
Script:

if((gs.getUserName()!=current.sys_created_by)&&(!gs.getUser().isMemberOf('MyGroup')))
{
current.addQuery('item_option_new','!=','490bd8e51bcdd050af0787bae54bcb75');
}

// 490bd8e51bcdd050af0787bae54bcb75 is the sys_id of catalog item variable

I appreciate your comments 🙂
Val

[Valon Sheremeti]

Yes, if(!gs.getUser().isMemberOf('MyGroup')) works perfectly well.

I absolutely agree on you comment:

For a Query Business Rule, Current is not the "current record" in the usual sense, so this is working exactly as designed, believe it or not. Current is really just the query that going to be executed to display the results

I tried ACL path but it involves changing permissions in other tables (possibly another M2M table so I'd rather not go that path)

All I need is to have a 'tail' from this 'current' record and then I would use GlideRecord to iterate through other records in this table records and filter the results. There must be a way of adding another layer of condition here 🙂

I think you already figured, but table name is Options [sc_item_option] 

Thank you.

V.

[Michael Jones -]

Sorry for the edits - it's late, heh. Should be correct now. 

[Ash10Patil]

Hi,

As per my understanding, before query business rule works before query is performed on table which means you do not have access to data yet. As a result, you can not access current.<field_name> value and it is empty when you try to access it.

Instead you can add it in the query like this:

current.addQuery("sys_created_by",gs.getUserName());

[Michael Jones - · Accepted solution]

Ok - amazing what a few hours of sleep can do. 

Give this a try! The trick was figuring out what was actually being "queried" when a request item was loaded and then working out the details from there. 

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

    var looking_for = '490bd8e51bcdd050af0787bae54bcb75'; //this is the id of your variable
    var found_var = false;
	var found_where = '';
    var sc_item_option = new GlideRecord('sc_item_option');
    sc_item_option.addEncodedQuery(current.getEncodedQuery());
	sc_item_option.setWorkflow(false); //critical to prevent recursive loop
    sc_item_option.query();
    while (sc_item_option.next()) {
        if (sc_item_option.item_option_new.sys_id == looking_for) {
			//ok we found the variable, ow we see if it was created by the user, or if the user is not part of the group
			if(sc_item_option.sys_created_by !=  gs.getUserName() && !gs.getUser().isMemberOf('MyGroup')) {
			found_var = true;
			found_where = sc_item_option.getValue('sys_id');
			}
        }

    }

    if (found_var == true ) {
		//If we found the var, the user is not the creator, and is not in the group - exclude that record!
        current.addQuery('sys_id', '!=', found_where);
    }


})(current, previous);

If this was helpful or correct, please be kind and click appropriately!

Michael Jones - Proud member of the CloudPires Team!

[Valon Sheremeti]

Michael, this works as expected! Thank you so much for your time and efforts on this.

If you don't mind, I would like to ask your opinion on how would you approach the following in this BR:

1. If I were to introduce multiple variables to be checked, (var looking_for) would you recommend creating an array instead?

2. I noticed pretty big performance issue when I tried accessing [sc_item_option] table records. Do you have recommendation on conditions when to run this BR?

I really appreciate your help on this.
Thanks a lot!