Hi,
In task doctype, i have created one report. The fields are
-
From Task doctype - ‘Task ID’ , ‘subject’, ‘custom_country’, ‘custom_region’, ‘custom_type_of_period’, ‘custom_month’, ‘custom_week’.
-
From Task Doctype Childtable (“Action Items”- fieldname “custom_action_item”) - ‘action_item’, ‘urgency’, ‘due_date’, ‘progress_status’, ‘remarksif_any’, ‘status’, ‘responsible_person’, ‘action_items’.
If i generate report by using below code in Query Report, Its repeating the parent doctype fields data follow up with childtable row datas.
For eg: I have 1 task , Inside the task 3 rows in the childtable. So it generate the task doctype fields repeating with 3 rows of childtable details.
How to avoid that repeating?
Give me any suggestion.
SELECT
t.name AS "Task ID",
t.subject,
t.custom_country,
t.custom_region,
t.custom_type_of_period,
t.custom_month,
t.custom_week,
a.action_item,
a.urgency,
a.due_date,
a.progress_status,
a.remarksif_any,
a.status,
a.responsible_person,
a.action_items
FROM
`tabTask` t
LEFT JOIN
`tabAction Items` a ON a.parent = t.name
WHERE
t.docstatus = 0
import frappe
from frappe import _
def execute(filters=None):
columns, data = [], []
columns = [
_("Task ID"),
_("Subject"),
_("Country"),
_("Region"),
_("Type of Period"),
_("Month"),
_("Week"),
_("Action Item"),
_("Urgency"),
_("Due Date"),
_("Progress Status"),
_("Remarks if Any"),
_("Status"),
_("Responsible Person"),
_("Action Items")
]
query = """
SELECT
t.name AS "Task ID",
t.subject,
t.custom_country,
t.custom_region,
t.custom_type_of_period,
t.custom_month,
t.custom_week,
a.action_item,
a.urgency,
a.due_date,
a.progress_status,
a.remarksif_any,
a.status,
a.responsible_person,
a.action_items
FROM
`tabTask` t
LEFT JOIN
`tabAction Items` a ON a.parent = t.name
WHERE
t.docstatus = 0
"""
result = frappe.db.sql(query, as_dict=True)
task_map = {}
for row in result:
task_id = row.get("Task ID")
if task_id not in task_map:
task_map[task_id] = {
"task_id": row.get("Task ID"),
"subject": row.get("subject"),
"custom_country": row.get("custom_country"),
"custom_region": row.get("custom_region"),
"custom_type_of_period": row.get("custom_type_of_period"),
"custom_month": row.get("custom_month"),
"custom_week": row.get("custom_week"),
"action_items": []
}
task_map[task_id]["action_items"].append({
"action_item": row.get("action_item"),
"urgency": row.get("urgency"),
"due_date": row.get("due_date"),
"progress_status": row.get("progress_status"),
"remarksif_any": row.get("remarksif_any"),
"status": row.get("status"),
"responsible_person": row.get("responsible_person"),
"action_items": row.get("action_items")
})
for task_id, task_data in task_map.items():
first_row = True
for action_item in task_data["action_items"]:
if first_row:
data.append([
task_data["task_id"],
task_data["subject"],
task_data["custom_country"],
task_data["custom_region"],
task_data["custom_type_of_period"],
task_data["custom_month"],
task_data["custom_week"],
action_item["action_item"],
action_item["urgency"],
action_item["due_date"],
action_item["progress_status"],
action_item["remarksif_any"],
action_item["status"],
action_item["responsible_person"],
action_item["action_items"]
])
first_row = False
else:
data.append([
"",
"",
"",
"",
"",
"",
"",
action_item["action_item"],
action_item["urgency"],
action_item["due_date"],
action_item["progress_status"],
action_item["remarksif_any"],
action_item["status"],
action_item["responsible_person"],
action_item["action_items"]
])
return columns, data