Query Business rule for records from an extended table.

[M_iA]

Hi, was wondering if someone could assit?

Case table - sn_customerservice_case

We have an extended table called managed service case - sn_customerservice_managed_service_case

I want to create a rule that allows a specific roled user to see all cases created directly on the case table. But only certain cases raised on the sn_customerservice_managed_service_case table.

The parameters on the extended table are as follows:

our customer_account table records have a reference field linked to a security group.

So the BR script needs to look at these cases and if a security group linked to the record, check to see whether the current user is in the group and if so, allow them to view.

I have tried creating this using the script below, but it not working as I had hoped when looking at the sn_customerservice_case table. It shows all cases created on the sn_customerservice_case table, but also all cases created on the extended table.

Please could someone point out what I am doing wrong?

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

    if (current.getTableName() == 'sn_customerservice_managed_service_case') {

        var result = "";
        var currentUser = gs.getUser();

        var rec = new GlideRecord('customer_account');
        rec.addNotNullQuery('u_access_container_group');
        //rec.addQuery('u_managed_services',true);
        rec.addQuery('customer', true);

        rec.query();

        while (rec.next()) {

            if (currentUser.isMemberOf(rec.u_access_container_group.name.toString())) {
                result += rec.sys_id + ",";
            }
        }
        result = result.slice(0, -1);
        var qc = current.addQuery('company', "IN", result);
    }
})(current, previous);

[M_iA]

@AnubhavRitolia- Ive tried updating but still the same results with nothing in the log unfortunately

[AnubhavRitolia]

Hi @M_iA 

Can you try below code and let me know the logs:

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

  gs.info("Managed Service Case: Triggered");
    if(current.sys_class_name=='sn_customerservice_managed_service_case') {
gs.info("Managed Service Case: If Condition");
        var result = "";
        var currentUser = gs.getUserID();

        var rec = new GlideRecord('customer_account');
        rec.addNotNullQuery('u_access_container_group');
        //rec.addQuery('u_managed_services',true);
        rec.addQuery('customer', true);

        rec.query();

        while (rec.next()) {
gs.info("Managed Service Case: While Loop");

            if (currentUser.isMemberOf(rec.u_access_container_group.name.toString())) {
                result += rec.sys_id + ",";
            }
        }
gs.info("Managed Service Case: Result = "+result);
        result = result.slice(0, -1);
        var qc = current.addQuery('company', "IN", result);
    }
})(current, previous);

Can you please try and let me know what logs you get with filter 'Message starts Managed Service Case'. Do share screenshot of those logs.

[M_iA]

@AnubhavRitolia 

Here is the log:

[AnubhavRitolia]

Hi @M_iA 

I hope you have record in 'sn_customerservice_managed_service_case' table.

[M_iA]

Hey @AnubhavRitolia - yes, over 30k records