Frappe v13 Script report could not fetch from child table also could not filter


I created 2 doctypes and one is a child table for the other.

i created one script report and managed to write the script and query on the js and py files respectively and here are the issues i encountered.

  1. managed to fetch the fields from the parent doctype but not from the child table
  2. i could not filter based on current date / selected date also based on child table fields

i could not find a clear resource to fix this issue.
can anyone help?

thank you :pray:

Please post the code snippets where you do the above 2 activities.

thank you for your quick reply sir

if (frappe.user.has_role('System Manager')) {
	frappe.query_reports["Gate Clearance Report"] = {
		"filters": [
				"fieldname": "entry_end_date",
				"label": __("Today"),
				"fieldtype": "Date",
				"default": frappe.datetime.get_today(),
				"reqd": 0,

				"fieldname": "gate",
				"label": __("Filter By gate"),
				"fieldtype": "Select",
				"options": ["Gate 01", "Gate 02", "Gate 03"],
				"default": "Gate 01"

and the python code

from __future__ import unicode_literals
import frappe
from frappe.utils import cstr, cint, getdate
from frappe import msgprint, _
from frappe.utils import getdate, validate_email_address, today, add_years

def execute(filters=None):
	if not filters: filters = {}
	columns = get_columns()
	data = get_requests(filters)

	return columns, data

def get_columns():
	return [
		_("Full Name") + ":Data:150",_("Organization") + ":Data:150",_("End Date") + ":Data:150",
		_("Gate") + ":Data:50", _("Approved By") + ":Data:100",
		_("Mobile Telephone") + ":Data:70",_("Laptop Computer") + ":Data:70",_("Storage (USB, Hard disk)") + ":Data:50", _("Other Device") + ":Data:400", _("Reason") + ":Data:8000",
def get_conditions(filters):
	conditions = ""
	if filters.get("entry_end_date"):
		if (filters.get("gate") == "Gate 01"):
			conditions += "and entry_end_date >= %(entry_end_date)s and gate LIKE '%%Gate 01%%'"
		if (filters.get("gate") == "Gate 02"):
			conditions += "and entry_end_date >= %(entry_end_date)s and gate LIKE '%%Gate 02%%'"
		if (filters.get("gate") == "Gate 03"):
			conditions += "and entry_end_date >= %(entry_end_date)s and gate LIKE '%%Gate 03%%'"
		# if (filters.get("gate") == "String"):
		# 	conditions += "and entry_end_date >= %(entry_end_date)s and gate LIKE '%%String%%'"
	return conditions

def get_requests(filters):
	conditions = get_conditions(filters)
	return frappe.db.sql("""select full_name,organization,entry_end_date,gate,approved_by
	,mobile_telephone,laptop_computer,storage_usb_hard_disk,other_device,detailed_reason,idx from `tabGuest Information` 
	where docstatus=1  %s order by organization """ % conditions, filters, as_list=1)
  • so the other thing is i didnt know how to query / join the parent table
    'tabGate Clearance' and the child table 'tabGuest Information' because it doesn’t display the child table values (i just used the child table in the query).
    i viewed the database and there seems to be no common data between them (as far as i noticed)

As for any linked docfield, invoking frappe.client.get_list or frappe.client.get_all or frappe.query_reports does not return these fields automatically. You have to query the linked DocType directly by specifying the “parent”: as an additional argument.

i see,
kindly, can you please show me how i can fix my query?

I’ve never used the “parent”: in frappe.query_reports but presume it’ll be something such as

frappe.query_reports["Gate Clearance Report"] = {
    "parent":'Guest Information',
    "filters": [
			{ ...

Search on this forum for “parent”: and see whether it is used in a frappe.query_reports method call.

thank you.
tried it and it doesnt seem to be effective.
one thing i noticed is i could not see a console.log() output and i am not sure if its reading the .js file for the script report

what could be the issue? tried clearing the cache for the site and so on

in child table there are three fields that connect to parent:

  • parent which contain the parent name.
  • parenttype which contain the name of parent doctype.
  • parentfield which contain the name of the field in parent.

thank you, figured it out.

i used a python query to join the parent and the child tables and filtered from the joined table.

thank you for giving me your attentions.