SELECT
att.employee AS “Employee”,
att.employee_name AS “Employee Name”,
DATE(att.time) AS “Date”,
TIMEDIFF(MAX(att.time), MIN(att.time)) AS “Total Time”
FROM
`tabEmployee Checkin` AS att
WHERE
att.employee = ‘${frm.doc.employee}’
GROUP BY
att.employee, DATE(att.time)
how to give another doctype field value for where cluse?
please elaborate the scenario, and is that query report?
yes this is for query report.
based on the login user employee field value the query will be execute.
kindly give solution for this
thanks.
Again you explained incompletely. Which doctype value do you want to set there? and whether it is a doctype field or not.
i want take the employee doctype employee field in the query report
is this clear?
Just consider the scenario Employee doctype and child doctype Phone. i want to display phone numbers of each employee
SELECT
e.name,
e.first_name,
COALESCE(GROUP_CONCAT(e.mobile_no SEPARATOR ', '), '') AS PhoneNumber
FROM
tabEmployee e
LEFT JOIN
tabPhone p ON e.name = p.parent
WHERE
e.status = 'Yes'
GROUP BY
e.name, e.first_name
Please check the example:
SELECT
att.employee AS "Employee",
att.employee_name AS "Employee Name",
DATE(att.time) AS "Date",
TIMEDIFF(MAX(att.time), MIN(att.time)) AS "Total Time",
emp.department AS "Department"
FROM
`tabEmployee Checkin` AS att
JOIN
`tabEmployee` AS emp ON att.employee = emp.name
WHERE
emp.department = 'Sales' -- Example: filter where employee's department is 'Sales'
GROUP BY
att.employee, DATE(att.time)
Reference:
Thanks for your reply ,
the above you given query is not suited my scenario , i want to filter based on the portal login employee.
how to fetch login user employee field value in the sql query report
Please check the reference, there is a video.
I checked those videos but there is filter we want to select one field based on that the query will executing,
But I want without the filter fields, based on Employee doctype employee field the filter is automatically append based on the employee login the portal
check this.
this is also filter based working I want preload in one field then i will use the field
is this possible?
SELECT
att.employee AS “Employee”,
att.employee_name AS “Employee Name”,
DATE(att.time) AS “Date”,
TIMEDIFF(MAX(att.time), MIN(att.time)) AS “Total Time”
FROM
tabEmployee Checkin AS att
WHERE
att.employee = %(employee)s
GROUP BY
att.employee, DATE(att.time)
the above query script how to use
frappe.db.get_value(“Employee”, {“employee”:frappe.session.employee}, “employee”,function(value){
console.log(“employee”,value.employee);
})
employee value in query script?
please give solution for this
I set the default value, but the query is not executing once the filter field is select then only the query is executing.
frappe.db.get_value(“Employee”, {“employee”:frappe.session.employee}, “employee”,function(value){
console.log(“employee”,value.employee);
})
I want to set the default employee value kindly give solution for this @NCP
Please read this.
This logic does not work in a query report. And read the reference post which I have given you carefully because query report side has some limitations, not everything is possible.
Please check it.
User sessions do not function correctly on the query report side, and as I have previously mentioned, there are some limitations.
{report-name}.py file.{report-name}.jsI have created employee_worklog_standard.py and employee_worklog_standard.js
In the JS file i have configured
frappe.query_reports[‘Employee Worklog Standard’] = {
filters: [
{
fieldname: ‘employee’,
label: __(‘Employee’),
fieldtype: ‘Link’,
options: ‘Employee’,
default: ‘HR-EMP-00007’
}
]
}
In the .py file how to write logic for this . please give reference @NCP
Iam not familiar with python please help me .
