Reference Qual Filter not working for non-admin users querying custom table

[Liam Rhodes1]

Hey all,

On one of our catalog items, I have a list collector variable that uses the follow reference qual filter which uses a script include to only display records in the u_sjp_partner_practices table where the user selected from the user variable is associated to. 

javascript:new PracticeFilter().getPracticeFilter(current.variables.user);

This executes the following Script Include which is client callable called PracticeFilter:

var PracticeFilter = Class.create();
PracticeFilter.prototype = {
    getPracticeFilter: function(userSysId) {
        var ids = [];
        var filter = 'sys_idIN';

        gs.info('[PracticeFilter] Called with userSysId: ' + userSysId);

        if (!userSysId) {
            gs.warn('[PracticeFilter] No userSysId provided, returning sys_idISEMPTY');
            return 'sys_idISEMPTY';
        }

        var assocGR = new GlideRecord('u_sjp_pss_firm_association');
        assocGR.addQuery('u_user', userSysId);
        assocGR.query();

        gs.info('[PracticeFilter] Found ' + assocGR.getRowCount() + ' association records for user: ' + userSysId);

        while (assocGR.next()) {
            if (assocGR.u_pss_practice_name)
                ids.push(assocGR.u_pss_practice_name.sys_id.toString());
        }

        if (ids.length === 0) {
            gs.warn('[PracticeFilter] No practices found for user: ' + userSysId);
            return 'sys_idISEMPTY';
        }

        filter += ids.join(',');
        gs.info('[PracticeFilter] Returning filter: ' + filter);
        return filter;
    },

    type: 'PracticeFilter'
};

So what this script does is it queries another custom table that we have created called 

u_sjp_pss_firm_association and searched against the u_user field (reference to sys_user) for any records where the user we've passed through matches. What it then does is return all records from the u_sjp_partner_practices table using that reference field to do so.

So the records on the u_sjp_pss_firm_association all look like this:

So it queries all records where the User field is a match and returns all records via the PSS Practice Name field.

These two custom tables have basic read/write/create/delete ACL's using a custom role which is assigned the user I'm testing with. For me, as an admin, this works perfectly fine (see below) but for a non-admin they're having no results returned:

I have checked the test users access to the table and to the records using Access Analyzer and it comes back as a pass on all ACL's. So I'm at a bit of a loss here, is this just messy ACL's or is there something else that I need to ensure has been set up to allow them to run this query?

Thanks in advance for any assistance!

[Ankur Bawiskar]

@Liam Rhodes1 

check my latest comment.

[Chaitanya ILCR]

query_range and query_match ACL are created on field level not on the table level

update those ACLs as u_sjp_partner_practices.* and check

Please mark my answer as helpful/correct if it resolves your query.

Regards,
Chaitanya

[John Gilmore]

@Liam Rhodes1 

Your instance may be configured in such a way that the user role requires query_range access. Try creating a query_range ACL for each table and assigning the role to it and see if that resolves your issue.

[Ankur Bawiskar]

@Liam Rhodes1 

Things to do

-> you need to create query_range ACL with Table.* i.e. u_sjp_partner_practices.*

-> Give role to that ACL and it will start showing records for non-admins in portal list collector variable

-> Also in variable attributes of your list collector add this so that it dynamically shows filtered records based on user variable

ref_qual_elements=user

-> Also try creating query_range ACL for u_sjp_pss_firm_association.u_user and add snc_internal role

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[Ankur Bawiskar]

@Liam Rhodes1 

I believe I shared the same solution to use query_range Table.* ACL

Could you please mark my response as correct as well so that I can receive credit for it?