How to add new field to fetch users by role

deatram · November 28, 2018, 5:58pm

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

vinhnguyent090 · November 29, 2018, 4:01am

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"
        }
    };
});

});

deatram · November 29, 2018, 5:24am

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

deatram · November 29, 2018, 5:32am

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?

root13F · November 29, 2018, 5:40am

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

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

github.com

frappe/erpnext/blob/e3136c38e1c1ee10211a7606da4e15f97336347b/erpnext/hr/doctype/expense_claim/expense_claim.py#L259


      
          	doc.total_amount_reimbursed = amt
          	frappe.db.set_value("Expense Claim", doc.name , "total_amount_reimbursed", amt)
          
          
	doc.set_status()
          	frappe.db.set_value("Expense Claim", doc.name , "status", doc.status)
          
          
@frappe.whitelist()
          def get_expense_approver(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 Approver' 
          		and u.enabled = 1 and u.name like %s
          	""", ("%" + txt + "%"))
          
          
@frappe.whitelist()
          def make_bank_entry(dt, dn):
          	from erpnext.accounts.doctype.journal_entry.journal_entry import get_default_bank_cash_account
          
          
	expense_claim = frappe.get_doc(dt, dn)
          	default_bank_cash_account = get_default_bank_cash_account(expense_claim.company, "Bank")

deatram · November 29, 2018, 5:48am

verifier is linked to User doctype

root13F · November 29, 2018, 5:48am

check my reply and the code you will get an idea

deatram · November 29, 2018, 5:52am

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

root13F · November 29, 2018, 6:09am

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 ?

deatram · November 29, 2018, 6:11am

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

hereabdulla · November 29, 2018, 6:16am

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

root13F · November 29, 2018, 6:20am

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"
        }
    };
});

deatram · November 29, 2018, 6:26am

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?

deatram · November 29, 2018, 6:26am

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

deatram · November 29, 2018, 6:49am

@root13F I have already created a file,

how can I refer to the path in custom script?

root13F · November 29, 2018, 7:03am

where is it saved ?

deatram · November 29, 2018, 7:38am

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

root13F · November 29, 2018, 7:44am

try this

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

deatram · November 29, 2018, 8:03am

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.

hereabdulla · November 29, 2018, 8:26am

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
        }
    };
});
});