Before query Business rule

[Fabricio4]

Hi!

So, I have a request to block some users from seeing Projects and Project tasks that aren't related to them.

I've followed the article below to achieve this with before query business rules:

https://servicenowguru.com/business-rules-scripting/controlling-record-access-before-query-business-...

As well as the OOB business rule "incident query".

It worked great for most of my requirements. There is, however, one nagging query that it just won't work with, and that is the query for parent.project_manager on the pm_project_task table. Here's the code:

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

if (gs.getSession().isInteractive()) {  

   var u = gs.getUserID(); 

   var g = getMyGroups(); 

   var q = current.addQuery('parent.assigned_to', u).addOrCondition('parent.assignment_group', g).addOrCondition('opened_by', u).addOrCondition('parent.additional_assignee_list', 'CONTAINS', u).addOrCondition('parent.project_manager', u).addOrCondition('parent.watch_list', 'CONTAINS', u); 

}
})(current, previous);

All of the queries work flawlessly. Parent.assigned_to, parent.assigment_group, etc. The only one that will not work is parent.project_manager. If that's the only query I use, it returns all of the records regardless of the condition. If I use only one of the other queries in isolation, they will all work.

That's also when I've discovered that I cannot add the parent.project_manager column in the pm_project_task list layout (it won't appear even when I dot walk 'parent'), for some reason, so I'm sure I'm missing something here.

Any ideas?

[SanjivMeher]

Can you check the dictionary of project_manager, if it is a reference field of user table and what is the table name of this field?

[Fabricio4]

It is a reference field to the sys_user table. The field, unlike assigned_to, assigned_group and opened_by, however, is on the pm_project table. The others I mentioned (of which the query works) are from the task table.

I suspect this is part of the problem, but I can't tell why if that's the case.

[SanjivMeher]

Ohh....In that case, I would suggest building the query in the List view first and then copy that query. If the table is in pm_project, parent.project_manager doesnt work.

I think in your case it will be parent.ref_pm_project.project_manager

[Rupanjani]

Hi @Fabricio4,

Try applying all the above filters manually on the list of the table. Hope you can figure out the issue then.

If not, right click and copy the query. Use addEncodedQuery in the business rule and make the modifications as required to make it work dynamically.