I have a doctype Lab Schedule in which i have a child table Lab schedule readings
i want to create a report to fetch all data of the child table as it store all the readings data
I have tried script report and made a script but its not seem to be working
I want to filter out the submitted documents and show its report
Script:
def execute(filters=None):
columns, data = get_columns(),
frappe.log_error("Starting Lab Workbook Report Execution", "Lab Workbook Report")
# Fetch Lab_Schedule documents that are submitted (docstatus=1)
lab_schedules = frappe.get_all(
"Lab_Schedule",
filters={"docstatus": 1},
fields=[
"name", "lab_template", "created_datetime", "submitted_datetime",
"plant", "division", "department", "area", "subarea",
"maintenance", "maintenance_type"
]
)
frappe.log_error(f"Fetched Lab_Schedule: {lab_schedules}", "Lab Workbook Report")
# Fetch the child table data for each Lab_Schedule document
for lab_schedule in lab_schedules:
readings = frappe.get_all(
"Lab_Schedule_Readings",
filters={"parent": lab_schedule.name, "parenttype": "Lab_Schedule"},
fields=[
"machine_group", "heat_number", "machine", "crucible",
"measure", "unit", "readings"
]
)
if readings:
for reading in readings:
data.append([
lab_schedule.name, # ID (expression generated field)
lab_schedule.lab_template,
lab_schedule.created_datetime,
lab_schedule.submitted_datetime,
lab_schedule.plant,
lab_schedule.division,
lab_schedule.department,
lab_schedule.area,
lab_schedule.subarea,
lab_schedule.maintenance,
lab_schedule.maintenance_type,
reading.machine_group,
reading.heat_number,
reading.machine,
reading.crucible,
reading.measure,
reading.unit,
reading.readings
])
else:
data.append([
lab_schedule.name, # ID (expression generated field)
lab_schedule.lab_template,
lab_schedule.created_datetime,
lab_schedule.submitted_datetime,
lab_schedule.plant,
lab_schedule.division,
lab_schedule.department,
lab_schedule.area,
lab_schedule.subarea,
lab_schedule.maintenance,
lab_schedule.maintenance_type,
None, None, None, None, None, None, None
])
frappe.log_error(f"Final Report Data: {data}", "Lab Workbook Report")
return columns, data
def get_columns():
return [
{“fieldname”: “id”, “label”: “ID”, “fieldtype”: “Data”, “width”: 100},
{“fieldname”: “lab_template”, “label”: “Lab Template”, “fieldtype”: “Link”, “options”: “Lab_Template”, “width”: 150},
{“fieldname”: “created_datetime”, “label”: “Created Datetime”, “fieldtype”: “Datetime”, “width”: 150},
{“fieldname”: “submitted_datetime”, “label”: “Submitted Datetime”, “fieldtype”: “Datetime”, “width”: 150},
{“fieldname”: “plant”, “label”: “Plant”, “fieldtype”: “Link”, “options”: “Plant”, “width”: 100},
{“fieldname”: “division”, “label”: “Division”, “fieldtype”: “Link”, “options”: “Division”, “width”: 100},
{“fieldname”: “department”, “label”: “Department”, “fieldtype”: “Link”, “options”: “Department”, “width”: 100},
{“fieldname”: “area”, “label”: “Area”, “fieldtype”: “Link”, “options”: “Area”, “width”: 100},
{“fieldname”: “subarea”, “label”: “Subarea”, “fieldtype”: “Link”, “options”: “Subarea”, “width”: 100},
{“fieldname”: “maintenance”, “label”: “Maintenance”, “fieldtype”: “Link”, “options”: “Maintenance”, “width”: 100},
{“fieldname”: “maintenance_type”, “label”: “Maintenance Type”, “fieldtype”: “Link”, “options”: “Maintenance_Type”, “width”: 100},
{“fieldname”: “machine_group”, “label”: “Machine Group”, “fieldtype”: “Link”, “options”: “Machine_Group”, “width”: 100},
{“fieldname”: “heat_number”, “label”: “Heat Number”, “fieldtype”: “Data”, “width”: 100},
{“fieldname”: “machine”, “label”: “Machine”, “fieldtype”: “Link”, “options”: “Machine_Master”, “width”: 100},
{“fieldname”: “crucible”, “label”: “Crucible”, “fieldtype”: “Link”, “options”: “Crucible_Master”, “width”: 100},
{“fieldname”: “measure”, “label”: “Measure”, “fieldtype”: “Link”, “options”: “UOM_Measure”, “width”: 100},
{“fieldname”: “unit”, “label”: “Unit”, “fieldtype”: “Link”, “options”: “UOM”, “width”: 100},
{“fieldname”: “readings”, “label”: “Readings”, “fieldtype”: “Float”, “precision”: 4, “width”: 100}
]
this is a sample report that i want to show as a report
