Client script on a Custom Link field to filter only users with specific roles

[maulana]

Hi Everyone.

I have a query below to get users that has specific role

select tu.name from `tabUser` tu, `tabHas Role` thr
where thr.parent = tu.name
and thr.role like "%PO Approver%";

I want to apply this query to a custom client/server script.

so far what I can do is filtering the user list based on columns in the User table. but on this case I need to join between tabUser and tabHas Role, that’s where I need help.
I’m not a developer so I don’t know to achieve that with a client/server script.

Can someone help or give me a clue?

[NCP]

Hi @maulana,

Please try this step and check it.

• For example, you can create a Custom Link field named “User” in a Sales Order.
• Write a client script for the Custom Link field. To do this, go to the Custom Script application and create a new script. Select the applicable doctype, in this case, Sales Order, and add the following code to filter the users based on their roles:

frappe.ui.form.on('Sales Order', {
    refresh: function(frm) {
        cur_frm.fields_dict.user.get_query = function(doc) {
            return {
                filters: {
                    'user_type': 'System User',
                    'enabled': 1,
                    'roles': ['Sales Person', 'Sales Manager']
                }
            };
        };
    }
});

• This script will filter the users based on their role as either Sales Person or Sales Manager.
• Save the client script and reload the form. The Custom Link field will now display only the users who match the specified roles.

Note: Replace the “Sales Person” and “Sales Manager” with the specific roles you want to filter.

Then reload (Ctrl + Shift + R) and check it.

Thank You!

[maulana]

Hi @NCP
thanks for your response.

This is my script after following your guide

frappe.ui.form.on("Purchase Order", {
	
	refresh(frm) {
		cur_frm.fields_dict.level_1_approver.get_query= function(doc) {
            return {
                filters:{
                        'user_type': 'System User',
                        'enabled': 1,
                        'roles': ['[S6] PO Approver Lvl1 A', '[S6] PO Approver Lvl1 B', '[S6] PO Approver Lvl1 C']
                }
                
            };
        };
	}
});

the result throws an error message on my PO when I select the field.

pymysql.err.OperationalError: (1054, “Unknown column ‘tabUser.roles’ in ‘where clause’”)

I believe this happens because “roles” does not exist in the tabUser table, thus needs a join to tabHas Role

Is it possible to join the table to tabHas Role to get the assigned role?
Oh, and is there a way to use “like” operator so I don’t have to put every role?

Regards,
MLN

[garciagonpablo]

Has anyone found a way to do this?

[Bhagyashri_Patil]

Hello sir I also stuck on the same issue
Please provide me solution

Thanking You

[lequockhanh]

I think this may help