Self Exclude Items in Child Table [Tutorial]

, ,
1

So ppl,

I was working on my Alternative Bom lines PR and realized a common scenario that I haven’t found any solution in the discuss.

The scenario is simple,
Doctype Parent “A” has a “Table” Child Table “B”
Doctype “B” which have Link to Doctype example to Item Doctype

So user can select Item in Child Table but we need to update this Link Item to exclude it. For example if user selects Item 1 in row 1 by clicking add row the Item 1 should not appear in the list.

This is self query…
The logic is …

  1. Loop the child table already
  2. make a list of Items that that already selected
  3. include them to sql query with not it.
// Filters Alternatives Items item fields
// List all Items except current parent Item and already selected in child tables
// Can not select as Alternative Item the "self" or "this" item_code
cur_frm.fields_dict['alt_items'].grid.get_field('item').get_query = function(doc, cdt, cdn) {
	 var d = locals[cdt][cdn];
         // loop child table to find the item
        // Javascript freaks can make this loop one liner!! :slight_smile: 
	 var allready_alt_items = []
	 if (cur_frm.doc.alt_items) {
		var current_alt_items = cur_frm.doc.alt_items
		for (var i = 0; i < current_alt_items.length; i++) {
            // add row adds an empty one row.. so next "if" ensures that no undefended variable is passed

			if (current_alt_items[i].item) {
				allready_alt_items.push(current_alt_items[i].item)
			}
		}
	 }
     return {
        filters: [
			['Item', 'item_code', '!=', cur_frm.doc.item],
			['Item', 'item_code', 'not in', allready_alt_items] // not in!!! 
		]
    }
};

Enjoy…

3 Likes
2

what exactly this line means? especially cur_frm.doc.item?
‘Item’, ‘item_code’, ‘!=’, cur_frm.doc.item

3

@szufisher

Thank you for notice this.

This actually a bonus :slight_smile: . The Parent “A” has a Link field to Item. So the line you said is actually filtering the Child Table Link Item based on Parent “A” item field. I am filtering child table based on an actual parent value.

‘Item’, ‘item_code’, ‘!=’, cur_frm.doc.item || Item Doctype field item_code is not equal to Parent A link field to Item with fieldname “item”.

4

Hi! I share my solution (very similar to yours)

cur_frm.fields_dict.childrens.grid.get_field('children').get_query = function(doc, cdt, cdn) {
	return {
		filters: [
			["name", "not in", get_selected_childrens()],
		]
	}
}

function get_selected_childrens() {
	var childrens = [];
	for (var i in cur_frm.doc.childrens) {
	 	retenciones.push(cur_frm.doc.childrens[i].children);
   	}
   	return childrens
}
5

not really clear …you’re pushing into retenciones and returning childrens :thinking:

6

@kickapoo Thank you so much for this!!!

For anyone’s reference, here’s my copy of the code kickapoo created, adapted for the field names I needed:

// Filters Alternatives Items item fields
// List all Items except current parent Item and already selected in child tables
// Can not select as Alternative Item the "self" or "this" item_code
cur_frm.fields_dict['qa_codes'].grid.get_field('code').get_query = function(doc, cdt, cdn) {
	var d = locals[cdt][cdn];
	// loop child table to find the item
	// Javascript freaks can make this loop one liner!! :slight_smile: 
	var already_qa_codes = [];
	if (cur_frm.doc.qa_codes) {
		var current_qa_codes = cur_frm.doc.qa_codes;
		for (var i = 0; i < current_qa_codes.length; i++) {
			// add row adds an empty one row.. so next "if" ensures that no undefended variable is passed
			if (current_qa_codes[i].code) {
				already_qa_codes.push(current_qa_codes[i].code);
			}
		}
	}
	return {
		filters: [
			['Internal Code', 'code', '!=', cur_frm.doc.code],
			['Internal Code', 'code', 'not in', already_qa_codes] // not in!!! 
		]
	};
};
2 Likes