Create script report with fields from multiple doctypes

ERPNext Reports
1

I have been trying to create a report which displays the following fields :

  • start date, employee, employee name, total hours from Timesheet
  • activity time, and start and end time from timesheet details
  • task subject and description from Tasks

I am using the following query, where is this going wrong :

frappe.db.sql(“”“select tabTimesheet.start_date, tabTimesheet.employee, tabTimesheet.employee_name,
tabTimesheet Detail.from_time, tabTimesheet Detail.to_time, tabTimesheet.total_hours,
tabTimesheet Detail.activity_type, tabTimesheet Detail.task, tabTimesheet Detail.project,
from tabTimesheet Detail, tabTimesheet
inner join tabTask on tabTask.name = tabTimesheet Detail.task
where tabTimesheet Detail.parent = tabTimesheet.name and %s order by tabTimesheet.name”“”%(conditions), filters, as_list=1)

2

Please share the error details

3

pymysql.err.ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ’ inner join tabTask on tabTask .name = tabTimesheet Detail .task
where tabTimesheet Detail .parent = tabTimesheet .name …’ at line 1”)

4

any solutions ?

5

table names with spaces need to be enclosed in backticks (`)

frappe.db.sql("""
    SELECT 
        `tabTimesheet`.start_date, 
        `tabTimesheet`.employee, 
        `tabTimesheet`.employee_name,
        `tabTimesheet Detail`.from_time, 
        `tabTimesheet Detail`.to_time, 
        `tabTimesheet`.total_hours,
        `tabTimesheet Detail`.activity_type, 
        `tabTimesheet Detail`.task, 
        `tabTimesheet Detail`.project
    FROM 
        `tabTimesheet Detail`
    INNER JOIN 
        `tabTimesheet` ON `tabTimesheet Detail`.parent = `tabTimesheet`.name
    INNER JOIN 
        `tabTask` ON `tabTask`.name = `tabTimesheet Detail`.task
    WHERE 
        {conditions}
    ORDER BY 
        `tabTimesheet`.name
""".format(conditions=conditions), filters, as_list=1)

try this code

1 Like