How to give permissions to User on Tasks in Projects based on Department?

I need that users of Department can see Tasks only from Projects for this Department.
For each Users appointed Department like and for each Project.
Now Users can’t see other Projects, but they can see all tasks in other Projects.
Or maybe exists better way?

created employee and filled the user_id field

create query_condition server script for both Project and Task respectively.

Query condition server script for Project doctype

department = frappe.db.get_value('Employee',{'user_id':frappe.user},'department')
conditions = """ department = '%s')""" %(department)

for Task

department = frappe.db.get_value('Employee',{'user_id':frappe.user},'department')
conditions = """ department = '%s')""" %(department)

@szufisher tried to do this

and have the error:

### App Versions
	"erpnext": "13.18.0",
	"frappe": "13.18.0"




Syntax error in query:
select `tabTask`.`name`, `tabTask`.`owner`, `tabTask`.`creation`, `tabTask`.`modified`, `tabTask`.`modified_by`, `tabTask`.`_user_tags`, `tabTask`.`_comments`, `tabTask`.`_assign`, `tabTask`.`_liked_by`, `tabTask`.`docstatus`, `tabTask`.`parent`, `tabTask`.`parenttype`, `tabTask`.`parentfield`, `tabTask`.`idx`, `tabTask`.`project`, `tabTask`.`is_group`, `tabTask`.`status`, `tabTask`.`priority`, `tabTask`.`is_milestone`, `tabTask`.`subject`, `tabTask`.`exp_start_date`, `tabTask`.`exp_end_date`, `tabTask`.`progress`, `tabTask`.`depends_on_tasks`, `tabTask`.`_seen`, `tabTask`.`color`
			from `tabTask`
			where `tabTask`.status = 'Open' and ((((coalesce(`tabTask`.`department`, '')='' or `tabTask`.`department` in ('Управление - УЦ\"')) and (coalesce(`tabTask`.`company`, '')='' or `tabTask`.`company` in ('Учебно-репетиторский центр \"Учазия\"')))) and  department = 'Управление - УЦ"'))
			 group by `tabTask`.`name`
			 order by `tabTask`.`modified` desc
			limit 20 offset 0
Traceback (most recent call last):
  File "apps/frappe/frappe/", line 68, in application
    response = frappe.api.handle()
  File "apps/frappe/frappe/", line 55, in handle
    return frappe.handler.handle()
  File "apps/frappe/frappe/", line 31, in handle
    data = execute_cmd(cmd)
  File "apps/frappe/frappe/", line 67, in execute_cmd
    return, **frappe.form_dict)
  File "apps/frappe/frappe/", line 1208, in call
    return fn(*args, **newargs)
  File "apps/frappe/frappe/", line 624, in wrapper_fn
    retval = fn(*args, **get_newargs(fn, kwargs))
  File "apps/frappe/frappe/desk/", line 28, in get
    data = compress(execute(**args), args=args)
  File "apps/frappe/frappe/desk/", line 47, in execute
    return DatabaseQuery(doctype).execute(*args, **kwargs)
  File "apps/frappe/frappe/model/", line 107, in execute
    result = self.build_and_run()
  File "apps/frappe/frappe/model/", line 144, in build_and_run
    return frappe.db.sql(query, as_dict=not self.as_list, debug=self.debug,
  File "apps/frappe/frappe/database/", line 154, in sql
  File "env/lib/python3.8/site-packages/pymysql/", line 148, in execute
    result = self._query(query)
  File "env/lib/python3.8/site-packages/pymysql/", line 310, in _query
  File "env/lib/python3.8/site-packages/pymysql/", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "env/lib/python3.8/site-packages/pymysql/", line 775, in _read_query_result
  File "env/lib/python3.8/site-packages/pymysql/", line 1156, in read
    first_packet = self.connection._read_packet()
  File "env/lib/python3.8/site-packages/pymysql/", line 725, in _read_packet
  File "env/lib/python3.8/site-packages/pymysql/", line 221, in raise_for_error
  File "env/lib/python3.8/site-packages/pymysql/", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
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 ')\n\t\t\t group by `tabTask`.`name`\n\t\t\t order by `tabTask`.`modified` desc\n\t\t\tlim...' at line 3")

Request Data

	"type": "POST",
	"args": {
		"doctype": "Task",
		"fields": "[\"`tabTask`.`name`\",\"`tabTask`.`owner`\",\"`tabTask`.`creation`\",\"`tabTask`.`modified`\",\"`tabTask`.`modified_by`\",\"`tabTask`.`_user_tags`\",\"`tabTask`.`_comments`\",\"`tabTask`.`_assign`\",\"`tabTask`.`_liked_by`\",\"`tabTask`.`docstatus`\",\"`tabTask`.`parent`\",\"`tabTask`.`parenttype`\",\"`tabTask`.`parentfield`\",\"`tabTask`.`idx`\",\"`tabTask`.`project`\",\"`tabTask`.`is_group`\",\"`tabTask`.`status`\",\"`tabTask`.`priority`\",\"`tabTask`.`is_milestone`\",\"`tabTask`.`subject`\",\"`tabTask`.`exp_start_date`\",\"`tabTask`.`exp_end_date`\",\"`tabTask`.`progress`\",\"`tabTask`.`depends_on_tasks`\",\"`tabTask`.`_seen`\",\"`tabTask`.`color`\"]",
		"filters": "[[\"Task\",\"status\",\"=\",\"Open\"]]",
		"order_by": "`tabTask`.`modified` desc",
		"start": 0,
		"page_length": 20,
		"view": "List",
		"group_by": "`tabTask`.`name`",
		"with_comment_count": true
	"freeze": false,
	"freeze_message": "Идёт загрузка...",
	"headers": {},
	"error_handlers": {},
	"url": "/api/method/frappe.desk.reportview.get"

Response Data

	"exception": "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 ')\\n\\t\\t\\t group by `tabTask`.`name`\\n\\t\\t\\t order by `tabTask`.`modified` desc\\n\\t\\t\\tlim...' at line 3\")"

@goldenscrew frappe.user is an object which is why it raises a SQL error. You want to use frappe.session.user to access a string that is the logged in user’s name/ PK.

sorry, the ) need to be removed. the corrected script as below

department = frappe.db.get_value('Employee',{'user_id':frappe.user},'department')
conditions = """ department = '%s' """ %(department)

please try again.

1 Like