How to get child table field data in script report filter erpnext?

ERPNext
1

I have created a script report with join two table. One is parent table and one is its child table. I want to filter child tables data in the report.

report_filter
report_filter1380×684 81.3 KB

This is the child table’s data in the report and I want to filter it getting a list of this data in the filter field. This field is data type field in the child table.

2

Sorry, I lost there, what do u want to achieve? U want the report to be filtered based on Cheque Filter or u want the Cheque Filter to become a dropdown to show the list of values for u to select?

3

@AMS_Fauzi Second one

4

Could u show me ur filter code?

5 
frappe.query_reports["Cheque Transaction Report"] = {
	"filters": [
		{
			fieldname: "party",
			label: "Party Name",
			fieldtype: "Link",
			options: "Supplier"
		},
		{
			fieldname: "transaction_type",
			label: "Transaction Type",
			fieldtype: "Select",
			options: [" ", "Receive", "Pay", "Internal Transfer"]
		},
		{
			fieldname: "bank_name",
			label: "Bank Name",
			fieldtype: "Link",
			options: "Bank"
		},
		{
			fieldname: "cheque_number",
			label: "Cheque Number",
			fieldtype: "Select",
		},
		{
			fieldname: "date_from_filter",
			label: "Effective Date From",
			fieldtype: "Date",
			default: frappe.datetime.add_months(frappe.datetime.get_today(), -1)
		},
		{
			fieldname: "date_to_filter",
			label: "Effective Date To",
			fieldtype: "Date",
			// default: frappe.datetime.get_today()
			default: frappe.datetime.add_months(frappe.datetime.get_today(), +1)

		}
	]
};
6

Following will be an example to get options based on parent Sales Order > Sales Order Item > Item Code

frappe.query_reports["Cheque Transaction Report"] = {
	"filters": [
		{
			fieldname: "party",
			label: "Party Name",
			fieldtype: "Link",
			options: "Supplier"
		},
		{
			fieldname: "transaction_type",
			label: "Transaction Type",
			fieldtype: "Select",
			options: [" ", "Receive", "Pay", "Internal Transfer"]
		},
		{
			fieldname: "bank_name",
			label: "Bank Name",
			fieldtype: "Link",
			options: "Bank"
		},
		{
			fieldname: "cheque_number",
			label: "Cheque Number",
			fieldtype: "Select",
			options: get_options()
		},
		{
			fieldname: "date_from_filter",
			label: "Effective Date From",
			fieldtype: "Date",
			default: frappe.datetime.add_months(frappe.datetime.get_today(), -1)
		},
		{
			fieldname: "date_to_filter",
			label: "Effective Date To",
			fieldtype: "Date",
			// default: frappe.datetime.get_today()
			default: frappe.datetime.add_months(frappe.datetime.get_today(), +1)

		}
	]
};

function get_options() {
	let values = [];

	frappe.call({
		method: "frappe.client.get_list",
		args: {
			doctype: "Sales Order Item",
			filters: [
				["parent", "=", "<so_name>"]
			],
			fields: ["item_code"],
			parent: "Sales Order"
		},
		callback: function(r) {
			if (r.message) {
				r.message.forEach(row => values.push(row.item_code));
			}
		}
	});

	return values;
};
7

@AMS_Fauzi

["parent", "=", "<so_name>"]

Cannot get this so_name. Try to fetch Item Name from Sales Order Item. But not working.

My Parent table in Cheque Transaction and its child table is Cheque Transaction Details
cheque_number data in in child table

8 
frappe.query_reports["Cheque Transaction Report"] = {
	"filters": [
		{
			fieldname: "party",
			label: "Party Name",
			fieldtype: "Link",
			options: "Supplier"
		},
		{
			fieldname: "transaction_type",
			label: "Transaction Type",
			fieldtype: "Select",
			options: [" ", "Receive", "Pay", "Internal Transfer"]
		},
		{
			fieldname: "bank_name",
			label: "Bank Name",
			fieldtype: "Link",
			options: "Bank"
		},
		{
			fieldname: "cheque_number",
			label: "Cheque Number",
			fieldtype: "Select",
			options: get_options()
		},
		{
			fieldname: "date_from_filter",
			label: "Effective Date From",
			fieldtype: "Date",
			default: frappe.datetime.add_months(frappe.datetime.get_today(), -1)
		},
		{
			fieldname: "date_to_filter",
			label: "Effective Date To",
			fieldtype: "Date",
			// default: frappe.datetime.get_today()
			default: frappe.datetime.add_months(frappe.datetime.get_today(), +1)

		}
	]
};

function get_options() {
	let values = [];

	frappe.call({
		method: "frappe.client.get_list",
		args: {
			doctype: "Cheque Transaction Details",
			filters: [],
			fields: ["cheque_number"],
			parent: "Cheque Transaction"
		},
		callback: function(r) {
			if (r.message) {
				r.message.forEach(row => values.push(row.cheque_number));
			}
		}
	});

	return values;
};
1 Like
9

@AMS_Fauzi, Thank you so much. Its working

10

@Atiq0629 Ur welcome, could u mark it as Solution. Thanks

11

Hello, @AMS_Fauzi
I’m facing a similar issue, but it pertains to the Dynamic Link Field rather than the Select option. Do you have any insights on this?