Filter Item List in Sales Order

Hi Everyone,

I want to restrict item list in Sales Order.

User Case

  1. I am having one select type field in Sales Order and Item both are the common field type.
  2. Apply filter or show item list based on that field type.

Thank You

use this in JS
cur_frm.fields_dict[ā€˜itemsā€™].grid.get_field(ā€œitemā€).get_query = function(frm,cdt,cdn){
var d = locals[cdt][cdn];
var field = frm.field;
return {
filters:{ ā€œfieldā€: field }
}
}

Hi @nouman
not worked.

frappe.ui.form.on(ā€˜Sales Orderā€™, ā€˜onloadā€™, function(frm,cdt, cdn) {
cur_frm.fields_dict[ā€˜itemsā€™].grid.get_field(ā€œitemā€).get_query = function(frm,cdt,cdn){
var d = locals[cdt][cdn];
var risk = cur_frm.risk_appetite;
return {
filters:{ ā€œrisk_appetiteā€: risk }
}
}
});

Try this one

cur_frm.cscript.onload = function(frm) {
cur_frm.set_query(ā€œitemā€,function() {
return{
filters:{ ā€œrisk_appetiteā€: risk }
}
});
}

Run this script outside frappe.ui.form.on

Hi @AkshayJadhao

this also not worked.
letā€™s see my use case

  1. In Sales Order one field is Risk Appetite.
  2. Same Field is in Item also.

letā€™s suppose sales order field value is Low then in item table filter the below conditions

Low == Low (correct In this condition user select the particular item )
Low == Medium (Incorrect i mean user not select the item )
Low == High(Incorrect same as above)
Low == Moderately High (same as above)

ā€¦ e.t.c

filters:{ ā€œrisk_appetiteā€: frm.doc.risk }

if this not work too then try this one (write your sql query in python script to return data )

cur_frm.cscript.onload = function(frm) {
cur_frm.set_query(ā€œItem_codeā€,function() {
return {ā€œqueryā€:ā€œyour.module.path.method_nameā€,
filters:{ā€˜collectionā€™:your_filter}}
});
}

your.module.path.method_name ā€”> is path to your python script from which you are returning a data you can write sql query there like i.e ā€œā€ā€œreturn frappe.db.sql( select item_name from tabItem where collection ={0}ā€".format(collection))

1 Like

but have no access of backend.

i am trying to do using custom script.

I am trying to do on this way.


cur_frm.add_fetch(ā€˜customerā€™,ā€˜risk_appetiteā€™,ā€˜risk_appetiteā€™)
frappe.ui.form.on(ā€˜Sales Order Itemā€™, ā€˜item_codeā€™, function(frm,cdt, cdn) {
var service_risk=[];
var risk=cur_frm.doc.risk_appetite;
alert(ā€˜here i amā€™);
frappe.call({
ā€˜methodā€™: ā€˜frappe.client.get_listā€™,
args: {
doctype: ā€œItemā€,
fields: ā€˜risk_appetiteā€™,
filters: {
item_code: cur_frm.doc.item_code,
}
},

async: false,
	callback: function(r) {
	service_risk=r.message[0][service_risk];
	console.log(service_risk);

	// Case-1
	if(service_risk=='Low'){
		alert('coming here')
		//frappe.set_route("List", "Customer")
	}
	
	// Case-2
	else if(service_risk=='Medium'){
		alert('coming here')
		//frappe.set_route("List", "Customer")
	}
	// Case-3
	else if(service_risk=='High'){
		alert('coming here')
		//frappe.set_route("List", "Customer")
	}
	// Case-4
	else if(service_risk=='Moderately High'){
		alert('coming here')
		//frappe.set_route("List", "Customer")
	}
	
	}
});	

});

frappe.ui.form.on(ā€œSales Orderā€,{

});

cur_frm.cscript.onload = function(frm) {
cur_frm.set_query(ā€œitem_codeā€,function() {
return {ā€œqueryā€:ā€œmy_method.pathā€,
filters:{ā€˜user_roleā€™:get_user_role()}}
});

}

this worked for me kindly not where i have written this script i have writen this outside the custom script

@AkshayJadhao
but this not worked for me. and i am using custom script.

Please Help !

@hari.kishor where you able to fix this ?

hello every one

here is my user scenario

when i want create sales order and i click on the sales order item table

it shows all the item in the item manager

is there a way it can show only the list of items in my preferred item group ā€œCarā€

@AkshayJadhao @nouman @Alaa_Badri

i would appreciate any idea on this as this feature would help restrict employees using the selling module from using procurement item

Hi @Chibuzor_Derrick, in Item you can select ā€œIs Sales Itemā€ and/or ā€œIs Purchase Itemā€. This has exactly this effect: you can only sell sales items, you can only buy purchase items.

1 Like

works perfectly well for me
thanks @rmeyer