deatram
November 28, 2018, 5:58pm
1
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
deatram
November 29, 2018, 5:24am
3
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
4
deatram:
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?
root13F
November 29, 2018, 5:40am
5
This verifier, which doctype is it linked to Employee or User ?
Check this code from expense_claim.py, ths will help you:
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
6
verifier is linked to User doctype
root13F
November 29, 2018, 5:48am
7
check my reply and the code you will get an idea
deatram
November 29, 2018, 5:52am
8
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
9
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
deatram
November 29, 2018, 6:11am
10
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
root13F
November 29, 2018, 6:20am
12
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
deatram
November 29, 2018, 6:26am
13
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
14
Thanks @root13F
I’m testing it out on clone server and will get back
1 Like
deatram
November 29, 2018, 6:49am
15
@root13F I have already created a file,
how can I refer to the path in custom script?
deatram
November 29, 2018, 7:38am
17
root13F:
where is it saved ?
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
18
try this
"home.frappe.frappe-bench.apps.expense_verifier.get_expense_verifier"
1 Like
deatram
November 29, 2018, 8:03am
19
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