Hello;
Is it possible to let the items that will be selected in the purchase order to be filtered based on the supplier? In other words: To show only the items that are related to the selected supplier and not showing all the items. How?
Regards
Bilal
Hi @bghayad ! Yes this is possible. Is your ERPNext self hosted? If it is, you may want to use something like this:
JS:
me.frm.set_query("item_code", "items", function(doc, cdt, cdn) {
var d = locals[cdt][cdn];
return {
query: "app_name.def_name_here",
filters:{
'supplier': doc.supplier
}
}
});
Python Script:
def name_here(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql("""Select item_code from `tabItem` where default_supplier = %s""", (filters.get("supplier")))
Hope this helps!
What about is no default supplier is set? Probably I better to lookup Item Supplier table.
@creamdory
Can u please explain little bit for me how this set_query is working? It will be called whenever we clicked on the item to select an item?
Also, why you declared var d = locals[cdt][cdn];? For what it will be used?
And regarding to the python whitelist method which is:
def name_here(doctype, txt, searchfield, start, page_len, filters):
I can understand the filters is going to be taken from what the JS will pass, but the other arguments from where it will be taken because I do not see it passed from the the JS.
Appreciate the kindly help.
Regards
Bilal
I would also like to do this. Were you able to set this up the way you wanted? Thanks!
Hello @charlie-cook
Tell me what exactly you need to do, I can help you.
Please mention your case in some details.
Regards
Bilal
Thanks, Bilal, for your help!
What I need to be able to do is select the supplier and then choose/see a listing of that supplier’s items on the sales order. Items are assigned to supplier on the item level using a supplier number (abbreviation of their name).
We sell plants and we may get the same item/plants from one supplier this week and then from another supplier next week when this one sells out, etc… So, we need to choose the supplier first and then choose the plant.
Hope that makes sense!
Hello @charlie-cook
You are welcome.
First of all, you need to add the suppliers for the item as shown in the below image (and it is not condition to fill the supplier part number).
Then you need to build the above mentioned scripts (the .js and .py for set_query), but please note that the sql query should be from Item Supplier table as @JoEz mentioned.
If you need more help in the .js and .py script, please let me me know and I can help you here.
Regards
Bilal
I’m starting to get the hang of things in ERPNext. Thanks to all for your help!
I’m now putting in my items and I have item assigned to default supplier. When I create the sales order, I need to be able to see the supplier in the drop down or as a column where I choose supplier first. How do I edit the item table on the sales order?
Thanks!
Heather
Okay, figured out how to add the supplier column to the item table on the sales order. Now, I need to filter the items based on that choice in the first column.
For instance, if I choose Supplier 1, then Item drop down should only show Items associated with that Supplier. Make sense? I think I just need to build a dependency on the Item Code field but not sure how to do that. Any assistance would be great!
Hello @charlie-cook
You need to write code at purchase_order.js and purchase_order.py and as @creamdory mentioned.
You can place the .js code under setup event or onload event as following:
frappe.ui.form.on(“Purchase Order”, {
setup: function (frm) {
frm.fields_dict[“purchase_order”].grid.get_field(“items”).get_query = function(doc, cdt, cdn) {
var d = locals[cdt][cdn];
return {
query: “erpnext.buying.doctype.purchase_order.purchase_order.get_items_supplier”,
filters:{
‘supplier’: d.supplier
}
}
});
Or instead of setup, it can be onload.
Now, regarding to the server side script which is python script that can be written in purchase_order.py:
def name_here(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql("""Select item_code from `tabItem` where default_supplier = %s""", (filters.get("supplier")))
But really I am not sure about the server side code (.py) and I need help from @creamdory if she can advise.
But basically what I know that it is required to write the arguments “doctype, txt, searchfield, start, page_len, filters” even if it is not going to be used. This is how the query is working.
Hello @creamdory:
Can you please explain what each of these parameters is meaning:
doctype, txt, searchfield, start, page_len, filters
Regards
Bilal
Thank you for your assistance. Is this possible to apply using custom script on Sales Order?
I am using ERPNext Cloud version, not hosted on my own server. Is this okay?
My need is to choose supplier_number (custom field on supplier) and then filter the item list based on the supplier.
Hello @charlie-cook
The answer for your question is Yes. And this is possible using the above script.
Have u tried it?
You need to be in developer mode.
Regards
Bilal
I did try it and it crashed my instance. I can no longer get in… just says internal server error. 
I will have to wait for support to fix it.
I do not know how to be in developer mode as I have tried everything I know and seem to be missing it. There’s also no documentation on how to do that. I’ll try again once I get this sorted.
thanks-
Heather
Hello @charlie-cook
Actually you need to read and be ready for doing script … you can not work without having some basics.
Regards
Bilal
Hello @creamdory
Can you please advise what the below parameters are? (Some explanation please).
doctype, txt, searchfield, start, page_len, filters
Regards
Bilal
Thought you might like to know that ERPNext Cloud version doesn’t offer Developer mode per support.
Thanks-
Heather