ACL to compare multiple fields between tables

[Adam H]

Hi SNow Community,

I have a requirement where I need to limit case visibility for users based on the account they can support and their assignment group. So I need to check the case's account and assignment group and compare that to the user's group and account they can support.

When I say "account they can support" i do not mean that they are on the customer_account table. What I did was I created a new m2m table called "sn_customerservice_m2m_users_accounts" that references the sys_user table and the customer_account table. That way, the internal user is not apart of that account, but they still have associations to the account to work on a case.

I have tried making an ACL to fulfill this requirement, but I can't seem to resolve it; I am not a scripting guru. The first line of code I tried is this:

var gr = new GlideRecord('sn_customerservice_m2m_users_accounts');
gr.addQuery('u_user', gs.getUserDisplayName());

gr.addNotNullQuery('u_account');
gr.query();

while (gr.next()) {
if (gs.getUser().isMemberOf(current.assignment_group) && gr.u_account == current.account) {
answer = true;
} else {
answer = false;
}
}

and then I found a gliderecord function called "addJoinQuery" that returns records based on a relationship in a current table and started to try this:

var gr = new GlideRecord('sn_customer_service_case');

gr.addJoinQuery('sn_customerservice_m2m_users_accounts', 'account', 'u_account');

gr.query();

Not quite sure how to proceed with this. Any help would be greatly appreciated and I can give more info if needed. Thank you!

[Goran WitchDoc]

Hi,

Before going down this road. Have you looked into "account team member"? You can read about it here: Set up account teams | ServiceNow Docs

I think it is exactly what you are looking for and shouldn't need a custom table for it. Let me know if that is what you want and I can help you setup an ACL for that. Since in that case it's pretty much to allow users to see only those records that they have any(or specific) responsibility for.

//Göran

[Adam H]

Hi Goran,

Thank you for your response; this seems like it could be something I could use. So would it be creating a responsibility such as "Account support member" and associating the user to that responsibility and the responsibility to the account in question?

I started an ACL based on this, but it doesn't seem to be just right yet:

var gr = new GlideRecord('sn_customerservice_team_member'); //Account team members table
gr.addQuery('account', current.account); // Compare the account field on account team members to the account field on the case records
gr.addQuery('responsibility', '6eb0cb84db0233008ecbe1bb4b96190d'); // responsibility created
gr.addQuery('user', gs.getUser()); // Compare user field on account team members to the current logged in user
gr.query();

while (gr.next()) {
answer = true;
}

[Goran WitchDoc · Accepted solution]

Hi Adam,

I haven't had time to look through reply so much, but at a glance I can see that you have one mistake and that is using gs.getUser(). Replace that with gs.getUserID() instead since it's the logged in users sys_id you are looking for. So the code would look like this:

var gr = new GlideRecord('sn_customerservice_team_member'); //Account team members table
gr.addQuery('account', current.account); // Compare the account field on account team members to the account field on the case records
gr.addQuery('responsibility', '6eb0cb84db0233008ecbe1bb4b96190d'); // responsibility created
gr.addQuery('user', gs.getUserID()); // Compare user field on account team members to the current logged in user
gr.setLimit(1);
gr.query();


if (gr.next()) {
 answer = true;
}

I also added a "setLimit" for performance, since you don't need to continue going through the records if you find one that matches your query. I also changed the WHILE to IF since you don't need to loop through records since you only have one. Could even use gr.hasNext() instead as well if you want since you don't need the record it self, you just want to know if you find one.

//Göran
Feel free to connect:
LinkedIn
Subscribe to my YouTube Channel
or look at my Book: The Witch Doctor’s Guide To ServiceNow