Hide list items based on criteria

ERPNext
1

I am bulding a custom erpnext for my client,
I need to find a way to hide certain list item in list view of a doctype based on a criteria
For example a lead is shown to every user 7 days after the user has created it, but the creator of the lead has to have access to it.

I cannot seem to find a way to do it, i can use some css, but it wouldn’t really work with the number of items that it shows,
if for example 20 lead are not shown but the 21 is more than 7 days old,
You wouldn’ t see it without clicking on the 50 button, which is not what i aiming for.

Thank you in advance for your time !

1 Like
2

image
image1039×347 15.2 KB

Here is some test

3

To hide certain list items in list view of a doctype based on a criteria, you can use a server-side script. Here’s an approach you can follow:

  1. Create a custom field in the lead doctype, say “show_lead_to_user”, which is a check field that is unchecked by default.
  2. Write a server-side script that runs periodically (e.g., every day), and sets the “show_lead_to_user” field to true for all leads created more than 7 days ago.
  3. Modify the query that retrieves the leads to include a condition that filters out leads where “show_lead_to_user” is false, except for the user who created the lead. You can use the frappe.db.sql method to execute the query and filter the results.

Here’s an example script that sets the “show_lead_to_user” field to true for leads created more than 7 days ago:

import frappe
from frappe.utils import now_datetime, add_days

def update_lead_visibility():
    seven_days_ago = add_days(now_datetime(), -7)
    leads = frappe.get_all("Lead", filters={"creation": ("<", seven_days_ago)})
    for lead in leads:
        frappe.db.set_value("Lead", lead.name, "show_lead_to_user", 1)

You can schedule this script to run periodically using the frappe.utils.background_jobs.enqueue method. For example, to run it every day at midnight, you can add the following code to a module’s hooks.py file:

from frappe.utils import background_jobs

def daily():
    background_jobs.enqueue(
        update_lead_visibility,
        enqueue_after_commit=True,
        enqueue_after_commit_kwargs={}
    )

With this approach, you can ensure that the leads are hidden from all users except for the creator until 7 days after creation.

4

Thank you very much !
Will this be possible if i just change the query permission using a custom app ?
import frappe

I omitted the part with days but you get the gist of it

def lead_query(user):
	if not user:
		user = frappe.session.user
	return """(`tabLead`.lead_owner = '{user}')""".format(user)
5

The code you provided is a custom query for the Lead doctype that filters leads based on the lead_owner field.

To modify this query to achieve your requirement of showing leads to everyone 7 days after creation, but only to the creator before that, you could use the following query:


import frappe

def lead_query(user):
    if not user:
        user = frappe.session.user

    return """(`tabLead`.lead_owner = '{user}' OR 
               (`tabLead`.creation > DATE_SUB(NOW(), INTERVAL 7 DAY) AND 
                `tabLead`.owner = '{user}'))""".format(user)

This query filters leads based on two conditions:

  1. If the lead owner is the current user (lead_owner = user).
  2. If the lead was created within the last 7 days (creation > DATE_SUB(NOW(), INTERVAL 7 DAY)) and the owner of the lead is the current user (owner = user).

This means that for leads created within the last 7 days, they will only be visible to the creator of the lead, while for leads created more than 7 days ago, they will be visible to everyone.

Note that modifying the query permission alone may not be sufficient to achieve your requirement. You also need to ensure that the show_lead_to_user field is set correctly for each lead. The approach I provided earlier using a server-side script is one way to achieve this.

6

Thanks I just get this error if you will

App Versions

{
	"egide_power_modification": "0.0.5",
	"erpnext": "14.17.0",
	"frappe": "14.26.0",
	"hrms": "14.0.3",
	"insights": "0.3.0",
	"payments": "0.0.1"
}

Route

List/Lead/List

Trackeback

Syntax error in query:
select `tabLead`.`name`, `tabLead`.`owner`, `tabLead`.`creation`, `tabLead`.`modified`, `tabLead`.`modified_by`, `tabLead`.`_user_tags`, `tabLead`.`_comments`, `tabLead`.`_assign`, `tabLead`.`_liked_by`, `tabLead`.`docstatus`, `tabLead`.`idx`, `tabLead`.`job_title`, `tabLead`.`status`, `tabLead`.`company_name`, `tabLead`.`territory`, `tabLead`.`title`, `tabLead`.`image`, `tabLead`.`disabled`
			from `tabLead`
			where (`tabLead`.owner = ''Administrator'' or `tabLead`.lead_owner = ''Administrator'')
			 group by `tabLead`.`name`
			 order by `tabLead`.`modified` desc
			limit 20 offset 0 None
Traceback (most recent call last):
  File "apps/frappe/frappe/app.py", line 66, in application
    response = frappe.api.handle()
  File "apps/frappe/frappe/api.py", line 54, in handle
    return frappe.handler.handle()
  File "apps/frappe/frappe/handler.py", line 45, in handle
    data = execute_cmd(cmd)
  File "apps/frappe/frappe/handler.py", line 83, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
  File "apps/frappe/frappe/__init__.py", line 1607, in call
    return fn(*args, **newargs)
  File "apps/frappe/frappe/__init__.py", line 789, in wrapper_fn
    retval = fn(*args, **get_newargs(fn, kwargs))
  File "apps/frappe/frappe/desk/reportview.py", line 29, in get
    data = compress(execute(**args), args=args)
  File "apps/frappe/frappe/desk/reportview.py", line 65, in execute
    return DatabaseQuery(doctype).execute(*args, **kwargs)
  File "apps/frappe/frappe/model/db_query.py", line 188, in execute
    result = self.build_and_run()
  File "apps/frappe/frappe/model/db_query.py", line 228, in build_and_run
    return frappe.db.sql(
  File "apps/frappe/frappe/database/database.py", line 219, in sql
    self._cursor.execute(query, values)
  File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "env/lib/python3.10/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "env/lib/python3.10/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "env/lib/python3.10/site-packages/pymysql/err.py", 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 'Administrator'' or `tabLead`.lead_owner = ''Administrator'')\n\t\t\t group by `ta...' at line 3")

Request Data

{
	"type": "POST",
	"args": {
		"doctype": "Lead",
		"fields": "[\"`tabLead`.`name`\",\"`tabLead`.`owner`\",\"`tabLead`.`creation`\",\"`tabLead`.`modified`\",\"`tabLead`.`modified_by`\",\"`tabLead`.`_user_tags`\",\"`tabLead`.`_comments`\",\"`tabLead`.`_assign`\",\"`tabLead`.`_liked_by`\",\"`tabLead`.`docstatus`\",\"`tabLead`.`idx`\",\"`tabLead`.`job_title`\",\"`tabLead`.`status`\",\"`tabLead`.`company_name`\",\"`tabLead`.`territory`\",\"`tabLead`.`title`\",\"`tabLead`.`image`\",\"`tabLead`.`disabled`\"]",
		"filters": "[]",
		"order_by": "`tabLead`.`modified` desc",
		"start": 0,
		"page_length": 20,
		"view": "List",
		"group_by": "`tabLead`.`name`",
		"with_comment_count": true
	},
	"freeze": false,
	"freeze_message": "Loading...",
	"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 'Administrator'' or `tabLead`.lead_owner = ''Administrator'')\\n\\t\\t\\t group by `ta...' at line 3\")"
}
7

try

import frappe

def lead_query(user):
    if not user:
        user = frappe.session.user

    return """(`tabLead`.lead_owner = {user} OR 
               (`tabLead`.creation > DATE_SUB(NOW(), INTERVAL 7 DAY) """.format(user)
8

I’ve changed everything, and use server script it does work, thank for all the help

if not user:
    user = frappe.session.user
# create a dictionary with properly escaped user variable
user_dict = {'user': frappe.db.escape(user)}
user = frappe.get_doc("User", frappe.user)
roles = [r.role for r in user.roles]
is_admin = "Sales Manager" in roles
if not is_admin:
    # use str.format_map() to safely interpolate the user variable into the conditions string
    conditions = '(owner = {user} or lead_owner = {user}) or (hidforeveryone = 0) or ( creation < DATE_SUB(NOW(), INTERVAL 7 DAY) and hidforeveryone = 1)'.format_map(user_dict)
2 Likes