Script Report sql with case returns data but in an unordered manner

I want to get follow up child table information depending on the Lead and Customer Here is my code in the script report python file

def execute(filters=None):
columns, data = [], []
columns = [
{
“fieldname”: “date_of_contact”,
“label” : _(“Next Scheduled Communication”),
“fieldtype”: “Datetime”,
},
{
“fieldname”: “customer”,
“label”: _(“Customer”),
“fieldtype”: “Link”,
“options”: “Customer”,
},
{
“fieldname”: “reference_type”,
“label”: _(“Reference Type”),
“fieldtype”: “Link”,
“options”: “Customer”,
},
{
“fieldname”: “reference_name”,
“label”: _(“Reference name”),
“fieldtype”: “Data”,
},
{
“fieldname”: “assigned_to”,
“label”: _(“Assigned To”),
“fieldtype”: “Link”,
“options”: “User”,
},
{
“fieldname”: “last_communication_date”,
“label”: _(“Last Communication Date”),
“fieldtype”: “Datetime”,
},
{
“fieldname”: “last_feedback”,
“label”: _(“Last Communication Feedback”),
“fieldtype”: “Data”,
“width”: 200
},
{
“fieldname”:“followup_type”,
“label”: (“Alert Type”),
“fieldtype”: “Select”,
},
]
if filters.from_date > filters.to_date:
frappe.throw(
(“From Date must be before To Date {}”).format(filters.to_date))

where_filter = {"from_date": filters.from_date,"to_date": filters.to_date,}
where = ""
if filters.user:
	where += '  AND `tabLead Follow Up`.contacted_by = %(user)s '
	where_filter.update({"user": filters.user})

if filters.alert_type:
	where += ' AND `tabLead Follow Up`.alert_type = %(alert_type)s'
	where_filter.update({"alert_type": filters.alert_type})

if '''`tabLead`.lead_name''':
	reference_type = 'lead'
else:
	reference_type = 'customer'

data = frappe.db.sql('''SELECT 
							tfu.next_contact_date AS date_of_contact,
							tl.lead_name AS reference_name, 
							tc.customer_name AS reference_name,
							tfu.parenttype AS reference_type, 
							CASE 
								WHEN tfu.parent = tl.name THEN tl.lead_name
								WHEN tfu.parent = tc.name THEN tc.customer_name
							END
							FROM 
							(`tabLead Follow Up` tfu)
							LEFT JOIN tabLead tl
							ON (tfu.parent=tl.name)
							LEFT JOIN tabCustomer tc
							ON (tfu.parent=tc.name)
							'''
						);
return columns, data

What is the error you are facing when trying to create a script report? Have you tried the steps in the following link

I am getting the data but in the wrong columns

You are getting the wrong order because here your SQL must also return the columns in the same order as the columns. Simply match the order as your columns and it should be done.

1 Like

Thanks for the reply @root13F, i tried but still won’t work. check out the query

data = frappe.db.sql(’’‘SELECT
tfu.next_contact_date AS date_of_contact,
tfu.parenttype AS reference_type,
tl.lead_name AS reference_name,
tc.customer_name AS reference_name,
tfu.contacted_by AS assigned_to,
tfu.contact_date AS last_communication_date,
tfu.feedback AS last_feedback,
tfu.alert_type AS followup_type,
CASE
WHEN tfu.parent = tl.name THEN tl.lead_name
WHEN tfu.parent = tc.name THEN tc.customer_name
END
FROM
(tabLead Follow Up tfu)
LEFT JOIN tabLead tl
ON (tfu.parent=tl.name)
LEFT JOIN tabCustomer tc
ON (tfu.parent=tc.name)
‘’’
);
return columns, data

specify as_list=1 at the end of query string. This should work.

1 Like

Thank you @root13F it worked but now in the reference name column all the rows are empty except for those whose reference type is customer and not lead

post the corrected query and that screenshot

Thanks for the reply but i solved it by addind AS reference_name at the END of CASE statement, here is the query.
CASE
WHEN tfu.parent = tl.name THEN tl.lead_name
WHEN tfu.parent = tc.name THEN tc.customer_name
END AS reference_name

1 Like

Great. You can now close this thread.