Query BR issue when runs for specific role

abhilashsg
Tera Contributor

There is an issue when query before BR runs, the encoded query works fine when checked as admin but for a custom roleA for which it should run, only returns few record compare to checking the encoded query with admin role.

I have checked ACL, there is no such issue, and also there is no security constraints message for that roleA. not sure what is missing here

i have checked logs, the encoded query which is returned looks fine but not sure what is happening. the difference in number of records show in admin to role is A huge. from 2000 - 18000 records

1 ACCEPTED SOLUTION

@abhilashsg 

your closing bracket for while should not be at the end

Did you try with hard-coded query in query BR?

things to check

1) field level READ ACL

2) domain separation

3) extra comma in encodedQuery

4) test the queries using list view

5) remove any empty strings from your arrays

try this

// Get the current user's sys_id
var userid = gs.getUserID();

// Prepare arrays to store company and segment values
var companyList = [];
var segmentlist = [];

// Query the custom table for relevant records
var companyListGr = new GlideRecord('x_custom_table');
companyListGr.addEncodedQuery("active!=false^ORactive=NULL^requested_for=" + userid);
companyListGr.query();

while (companyListGr.next()) {
    // Collect company sys_ids (assuming comma-separated)
    var companyArray = companyListGr.company_sys_id.toString().split(',');
    companyList = companyList.concat(companyArray);

    // Collect segment values (assuming comma-separated display values)
    var segmentValue = companyListGr.getDisplayValue('segment').toString();
    if (segmentValue) {
        var segment1 = segmentValue.split(',');
        segmentlist = segmentlist.concat(segment1);
    }
}

// Clean up arrays: remove empty/whitespace values and duplicates
function cleanArray(arr) {
    return arr.filter(function(x, i, self) {
        return x && x.trim() && self.indexOf(x) === i;
    });
}
companyList = cleanArray(companyList);
segmentlist = cleanArray(segmentlist);

// Build the encoded query only if there are values to use
if (companyList.length > 0 || segmentlist.length > 0) {
    var queryParts = [];

    if (companyList.length > 0) {
        queryParts.push('company_name_of_customer.sys_idIN' + companyList.join(','));
        queryParts.push('company_name_of_the_service_provider.sys_idIN' + companyList.join(','));
    }
    if (segmentlist.length > 0) {
        queryParts.push('segment_of_customerIN' + segmentlist.join(','));
        queryParts.push('segment_of_service_providerIN' + segmentlist.join(','));
    }

    // Add your static conditions
    queryParts.push('state!=delete');
    queryParts.push('contract_type!=intra_company_cost_shift');
    queryParts.push('segment_of_customerISNOTEMPTY');
    queryParts.push('segment_of_service_providerISNOTEMPTY');

    // Join with ^OR as needed
    var newQry = queryParts.join('^OR');

    // Apply the encoded query to the current GlideRecord
    current.addEncodedQuery(newQry);

    // Optional: log the query for debugging
    // gs.info("Final Encoded Query: " + newQry);
}

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
✨ Certified Technical Architect  ||  ✨ 9x ServiceNow MVP  ||  ✨ ServiceNow Community Leader

View solution in original post

6 REPLIES 6

Ankur Bawiskar
Tera Patron
Tera Patron

@abhilashsg 

in that encoded query is there some field to which that non-admin doesn't have access?

Any table level READ ACL is blocking the other records?

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
✨ Certified Technical Architect  ||  ✨ 9x ServiceNow MVP  ||  ✨ ServiceNow Community Leader

I have already checked read ACL and it is correctly configured for non - admin users. However in further investigation i found that in encoded code, where we use the query" is one of" doesn't consider when there are more that 2 values. for example below query which is a result of before query BR:

company_name_of_customer.sys_idIN,^ORcompany_name_of_the_service_provider.sys_idIN,^ORsegment_of_customerINNew Buses,New Trucks^ORsegment_of_service_providerINNew Buses,New Trucks

 

here - "ORsegment_of_customerINNew Buses,New Trucks", segment of customer only returns the query for the first value - New buses but ignore New Trucks.

Same thing happens with other part of query -"company_name_of_customer"/company_name_of_the_service_provider/segment_of_service_provider

@abhilashsg 

ideally should not behave like this

Did you try to hard-code and see if it works fine?

share the query BR script

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
✨ Certified Technical Architect  ||  ✨ 9x ServiceNow MVP  ||  ✨ ServiceNow Community Leader

Here is the query BR script:

var userid = gs.getUserID();
    //var username = gs.getUserDisplayName();
    var last = [];
    var companyList = [];
    var segmentlist = [];
    var companyListGr = new GlideRecord('x_custom_table');
    companyListGr.addEncodedQuery("active!=false^ORactive=NULL^requested_for=" + userid);
    companyListGr.query();

    while (companyListGr.next()) {
        var companyArray = companyListGr.company_sys_id.toString().split(',');
        companyList = companyList.concat(companyArray);
        last = companyList;
        var segmentValue = companyListGr.getDisplayValue('segment').toString();
        if (segmentValue) {
            var segment1 = segmentValue.toString().split(',');
            segmentlist = segmentlist.concat(segment1);
        }
        //gs.info('segmentValue: ' + segmentlist);
        //gs.info('companyValue: ' + last);

        if ((last.length > 0) || (segmentlist.length > 0)) {
            var newQry = 'company_name_of_customer.sys_idIN' + last.join(',') + '^ORcompany_name_of_the_service_provider.sys_idIN' + last.join(',') + '^ORsegment_of_customerIN' + segmentlist.join(',') + '^ORsegment_of_service_providerIN' + segmentlist.join(',') + "^state!=delete^contract_type!=intra_company_cost_shift^segment_of_customerISNOTEMPTY^ORsegment_of_service_providerISNOTEMPTY";
            current.addEncodedQuery(newQry);
            //gs.info("Query BR2: " + newQry);
        }
    }