How to add new field to fetch users by role

Hello,
consider a scenario of an Expense Approver.
A user is assigned a role Expense Approver

When I go to create Expense Claim I see all users with role Expense Approver like this

Now I have created a new Role called Expense Verifier and assigned it to user and gave permission to expense claim document

How can I create a field in Expense Claim document that only searches users with Role Expense Verifier?
I have tried adding a field to link it to user, but it shows list of all users.

How can I limit it to show only users of certain role? or does it require to customize the code?

Thanks

Hi @deatram

You can use custom script to query list of selector.

https://frappe.io/docs/user/en/guides/app-development/overriding-link-query-by-custom-script

frappe.ui.form.on("Bank Reconciliation", "onload", function(frm) {
cur_frm.set_query("bank_account", function() {
    return {
        "filters": {
            "account_type": "Bank",
            "group_or_ledger": "Ledger"
        }
    };
});

});

1 Like

Hi, Thank you @vinhnguyent090 .
I’m familiar with the custom scripts,
now how can I create one like that but specific to my document Expense Claim and Role Expense Verifier?
because in that example, it shows about bank reconciliation.
Thanks

I have tried this so far, but it does not work,

frappe.ui.form.on("Expense Claim", "onload", function(frm) {
cur_frm.set_query("verifier", function() {
    return {
        "filters": {
            "role": "Expense Verifier"
        }
    };
});

});

How can I improve it?

This verifier, which doctype is it linked to Employee or User ?

Check this code from expense_claim.py, ths will help you:

verifier is linked to User doctype

check my reply and the code you will get an idea

please help, I don’t understand the code,
I only know how to add custom script as the one above.
Thanks.

Via custom script it seems difficult because this checking of roles requires two tables and not one.

Do you have access to the server ? or are you on cloud ?

1 Like

Yes, It is on cloud, and I have server access to it. How can I proceed from there?

instead of using User and role, you can use Doctype as Employee and Department as Filter,it works fine

2 Likes

Make a python file

Copy that function get_expense_approver complete and paste it there. Change its name to get_expense_verifier and replace Expense Approver by Expense Verifier in this line

where u.name = r.parent and r.role = 'Expense Approver' 

don’t forget to write import frappe in the python file.

Save it not in erpnext app but outside it - because you don’t want to save and commit it there. If you have a custom app then it is even better. Save it there.

Now create a custom script like this:

frappe.ui.form.on("Expense Claim", "onload", function(frm) {
cur_frm.set_query("verifier", function() {
    return {
        "filters": {
           query: "path.to.your.python.function"
        }
    };
});
1 Like

Thank you,
won’t other members of that filtered department be visible as well?
Or does it mean I will have to create another department unique to Expense Verifier only?

Thanks @root13F
I’m testing it out on clone server and will get back

1 Like

@root13F I have already created a file,

how can I refer to the path in custom script?

where is it saved ?

1 Like

I saved it in app folder. just there.

Sorry, I’m still new to customization.
I know I’l probably have to learn to create custom app at some point in time.
but for now I just want to see first if this function will work, then I’l move to the next stage of custom app

try this

"home.frappe.frappe-bench.apps.expense_verifier.get_expense_verifier"

1 Like

it is not working,
Here is the snapshot of the .py file.

import frappe


@frappe.whitelist()
def get_expense_verifier(doctype, txt, searchfield, start, page_len, filters):
        return frappe.db.sql("""
                select u.name, concat(u.first_name, ' ', u.last_name)
                from tabUser u, `tabHas Role` r
                where u.name = r.parent and r.role = 'Expense Verifier'
                and u.enabled = 1 and u.name like %s
        """, ("%" + txt + "%"))

And here is my custom script.

frappe.ui.form.on("Expense Claim", "onload", function(frm) {
cur_frm.set_query("verifier", function() {
    return {
        "filters": {
           query: "home.frappe.frappe-bench.apps.expense_verifier.get_expense_verifier"
        }
    };
});
});

please tell if there is anything I’m missing.

you can create a check in Employee and use as a filter in custom script like this

frappe.ui.form.on("Expense Claim", "onload", function(frm) {
cur_frm.set_query("verifier", function() {
    return {
        "filters": {
          'is_verifier':1
        }
    };
});
});
2 Likes