Client/Server script to filter (Link) field based on Multi-Select from another DocType

Frappe Framework Custom Script
,
1

Hello,
I have this setup:

  • (A) DocType, and it has a Multi-select field named (allowed_hotels). Which is a Link to the Supplier Doctype, and Filters applied: Supplier Group = Hotels
  • (B) DocType, linked to (A) DocType, and has another Link field (hotel).

I want the last field (hotel) in DocType B to display only pre-selected hotels in the multi-select in DocType A.

I tried this script on DocTyp B

frappe.ui.form.on('B', {
    refresh: function(frm) {
        frm.fields_dict['hotel'].get_query = function(doc, cdt, cdn) {
            let a = doc.a; // Get the linked A DocType
            let allowed_hotels = []; // default to empty if no A is linked
            if (a) {
                // Fetch the allowed_hotels field data from the linked A
                    return ('A', a, 'allowed_hotels')
                    .then(response => {
                        var allowed_hotels = response.message.allowed_hotels;
                        return {
                            filters: [
                                ['supplier_group', '=', 'Hotels'],
                                ['name', 'in', allowed_hotels]
                            ]
                        };
                    });
            } else {
                return {
                    filters: [
                        ['supplier_group', '=', 'Hotels']
                    ]
                };
            }
        };
    },
2

Hi:

Use client side to set a custom query, that will be called at run time.
See this example

Client side:

		frm.set_query("supplier_primary_contact", function(doc) {
			return {
				query: "erpnext.buying.doctype.supplier.supplier.get_supplier_primary_contact",
				filters: {
					"supplier": doc.name
				}
			};
		});

Server side:

def get_supplier_primary_contact(doctype, txt, searchfield, start, page_len, filters):
	supplier = filters.get("supplier")
	return frappe.db.sql(
		"""
		SELECT
			`tabContact`.name from `tabContact`,
			`tabDynamic Link`
		WHERE
			`tabContact`.name = `tabDynamic Link`.parent
			and `tabDynamic Link`.link_name = %(supplier)s
			and `tabDynamic Link`.link_doctype = 'Supplier'
			and `tabContact`.name like %(txt)s
		""",
		{"supplier": supplier, "txt": "%%%s%%" % txt},
	)

Hope this helps.

1 Like
3

Thanks for the effort and help.

I mapped the client script, but could not get the server script.

I also noticed something: the (A) Doctype that has the multi-select field, does not have it stored in its table.

Checking the database, in tabA, there is no (allowed_hotels) or any of my other multi_select fields. Are they treated differently?

4

For whoever might need it:

@frappe.whitelist()
def get_allowed_hotels(doctype, txt, searchfield, start, page_len, filters):
    a = filters.get("a")
    
    # Fetch the allowed hotels from the child table
    allowed_hotels_list = frappe.db.sql("""
        SELECT hotel 
        FROM `tabA_Allowed_Hotels` 
        WHERE parent = %s
    """, a)
    
    if not allowed_hotels_list:
        return []

    allowed_hotels_list = [item[0] for item in allowed_hotels_list]

    return frappe.db.sql("""
                         SELECT name, supplier_name 
                         FROM `tabSupplier` 
                         WHERE `tabSupplier`.name IN (%s)  
                         AND `tabSupplier`.name LIKE %s 
                         LIMIT %s OFFSET %s
                         """, 
                         (tuple(allowed_hotels_list), "%%%s%%" % txt, page_len, start))

And it worked perfectly. A_Allowed_Hotels is my child doctype for the multi-select field

2 Likes