• Version 14
  • Version
      Version 13 Version 14
  • Docs
  • Reviews
  • Tutorial
  • GitHub
  • Learn
    • Getting Started
      • Introduction
      • Prerequisites
      • Installation
      • Tutorial
        • Frappe Framework Tutorial
        • Install and Setup Bench
        • Create an App
        • Create a Site
        • Create a DocType
        • DocType Features
        • Controller Methods
        • Types of DocType
        • Form Scripts
        • Web View Pages
        • What's Next?
      • Guides
        • Guides
        • Basics
          • Frappe Apps
          • Contribute Translations
          • Frappe Ajax Call
          • How to Enable Backup Encryption
          • Sites
          • Translations
        • App Development
          • Executing Code On Doctype Events
          • How To Improve A Standard Control
          • Running Background Jobs
          • Adding Custom Button To Form
          • Trigger Event On Deletion Of Grid Row
          • Dialogs Types
          • Overriding Link Query By Custom Script
          • Set up a new Connected App
          • Custom Module Icon
          • Insert A Document Via Api
          • How To Create Custom Fields During App Installation
          • Using Html Templates In Javascript
          • How to Enable Developer Mode in Frappe
          • Fetch a Field Value from a Document into a Transaction
          • Adding Module Icons On Desktop
          • Single Type Doctype
          • Prerequisites
          • Adding Social Login Provider
          • Exporting Customizations to your App
        • Deployment
          • Packages
          • Email Notifications For Failed Background Jobs
          • How To Migrate Doctype Changes To Production
          • Migrations
          • How To Enable Social Logins
        • Reports and Printing
          • Getting Information From Another Document In Print Format
          • Where Do I Find Standard Print Formats
          • How To Make Query Report
          • Report Print Formats
          • Script Report
        • Portal Development
          • Pages
          • Ordering
          • Adding Pages
          • Table of Contents
          • Dynamic Pages
          • Portal Roles
          • Customizing Web Forms
          • Generators
          • Redirects
        • Data
          • Using the Data Migration Tool
          • Import Large Csv File
        • Integration
          • Google GSuite
          • Token based authentication
          • OpenID Connect and Frappe social login
          • Webhooks
          • Social Login Key
          • Google Calendar Integration
          • How to setup OAuth 2?
          • Token based authentication
          • Rest Api
            • Introduction
            • Simple Authentication
            • Token Based Authentication
            • OAuth 2
            • Listing documents
            • Manipulating DocTypes
        • Database Settings
          • Setup read operations from slave/secondary mysql system
          • Postgres Database Setup
        • Automated Testing
          • Automated Testing
          • Unit Testing
          • UI Integration Testing
          • UI Testing with Frappe API
        • Desk
          • Desk Customization
          • Formatter For Link Fields
          • Making Charts
    • Basics
      • What is Frappe Framework?
      • Why Frappe Framework?
      • Architecture
      • Directory structure
      • Apps
      • Sites
      • Understanding DocTypes
      • Developer API
      • Users and Permissions
      • Asset Bundling
      • Static Assets
      • Configuration
      • Doctypes
        • Docstatus
        • Module
        • DocField
        • Field Types
        • Naming
        • Controllers
        • Form & View Settings
        • Child / Table DocType
        • Single DocType
        • Actions and Links
        • Customizing DocTypes
    • Desk
      • Desk
      • Reports
      • Printing
      • Attachments
      • Scripting
      • Reports
        • Script Report
        • Query Report
        • Report Builder
      • Scripting
        • Client Script
        • System Console
        • Server Script
        • Script API
    • Portal
      • Discussions
      • Portal Pages
      • Web Forms
      • Blog Post
    • Python API
      • Background Jobs
      • Document API
      • Database API
      • Jinja API
      • Request Lifecycle
      • Language Resolution
      • Utility Functions
      • Responses
      • Search
      • Hooks
      • REST API
      • FullTextSearch API
      • Dialog API
      • Query Builder
    • JS API
      • Form Scripts
      • Controls
      • List
      • Page API
      • Tree
      • Common Utilities API
      • Dialog API
      • Chart API
      • Scanner API
      • Server Calls (AJAX)
      • Logging
      • Form Tours
    • Integration
      • Razorpay
        • RazorPay
      • Google Drive
        • Google Drive
      • LDAP
        • Setting up LDAP
      • Rest Api
    • Videos
      • Video Tutorials for Frappe Framework
    • Bench
      • Bench
      • Bench Commands
      • Frappe Commands
      • Bench - Extending the CLI
      • Resources
        • Background Services
        • Bench Commands Cheatsheet
        • Bench Procfile
      • Guides
        • Adding Custom Domains to your Site
        • Configuring HTTPS
        • Diagnosing The Scheduler
        • Using Let's Encrypt to setup HTTPS
        • Manual Setup
        • Setting Limits for your Site
        • Setup Multitenancy
        • Setup Production
      • Reference
        • Commands Reference
        • bench new-site
        • bench drop-site
        • bench migrate
        • bench backup
        • bench reinstall
        • bench list-apps
        • bench uninstall-app
        • bench show-config
        • bench set-config
        • bench restore
        • bench partial-restore
        • bench version
        • bench transform-database
        • bench trim-database
        • bench trim-tables
    • Deployment
      • Database Migrations
      • Production Setup
      • Rate Limiting
    • Writing Tests
      • Testing
      • UI Testing
    • Help
      • Debugging
      • How to contribute
      • Profiling and Monitoring
      • Translations
      • Logging

  • Getting Started
    • Introduction
    • Prerequisites
    • Installation
    • Tutorial
      • Frappe Framework Tutorial
      • Install and Setup Bench
      • Create an App
      • Create a Site
      • Create a DocType
      • DocType Features
      • Controller Methods
      • Types of DocType
      • Form Scripts
      • Web View Pages
      • What's Next?
    • Guides
      • Guides
      • Basics
        • Frappe Apps
        • Contribute Translations
        • Frappe Ajax Call
        • How to Enable Backup Encryption
        • Sites
        • Translations
      • App Development
        • Executing Code On Doctype Events
        • How To Improve A Standard Control
        • Running Background Jobs
        • Adding Custom Button To Form
        • Trigger Event On Deletion Of Grid Row
        • Dialogs Types
        • Overriding Link Query By Custom Script
        • Set up a new Connected App
        • Custom Module Icon
        • Insert A Document Via Api
        • How To Create Custom Fields During App Installation
        • Using Html Templates In Javascript
        • How to Enable Developer Mode in Frappe
        • Fetch a Field Value from a Document into a Transaction
        • Adding Module Icons On Desktop
        • Single Type Doctype
        • Prerequisites
        • Adding Social Login Provider
        • Exporting Customizations to your App
      • Deployment
        • Packages
        • Email Notifications For Failed Background Jobs
        • How To Migrate Doctype Changes To Production
        • Migrations
        • How To Enable Social Logins
      • Reports and Printing
        • Getting Information From Another Document In Print Format
        • Where Do I Find Standard Print Formats
        • How To Make Query Report
        • Report Print Formats
        • Script Report
      • Portal Development
        • Pages
        • Ordering
        • Adding Pages
        • Table of Contents
        • Dynamic Pages
        • Portal Roles
        • Customizing Web Forms
        • Generators
        • Redirects
      • Data
        • Using the Data Migration Tool
        • Import Large Csv File
      • Integration
        • Google GSuite
        • Token based authentication
        • OpenID Connect and Frappe social login
        • Webhooks
        • Social Login Key
        • Google Calendar Integration
        • How to setup OAuth 2?
        • Token based authentication
        • Rest Api
          • Introduction
          • Simple Authentication
          • Token Based Authentication
          • OAuth 2
          • Listing documents
          • Manipulating DocTypes
      • Database Settings
        • Setup read operations from slave/secondary mysql system
        • Postgres Database Setup
      • Automated Testing
        • Automated Testing
        • Unit Testing
        • UI Integration Testing
        • UI Testing with Frappe API
      • Desk
        • Desk Customization
        • Formatter For Link Fields
        • Making Charts
  • Basics
    • What is Frappe Framework?
    • Why Frappe Framework?
    • Architecture
    • Directory structure
    • Apps
    • Sites
    • Understanding DocTypes
    • Developer API
    • Users and Permissions
    • Asset Bundling
    • Static Assets
    • Configuration
    • Doctypes
      • Docstatus
      • Module
      • DocField
      • Field Types
      • Naming
      • Controllers
      • Form & View Settings
      • Child / Table DocType
      • Single DocType
      • Actions and Links
      • Customizing DocTypes
  • Desk
    • Desk
    • Reports
    • Printing
    • Attachments
    • Scripting
    • Reports
      • Script Report
      • Query Report
      • Report Builder
    • Scripting
      • Client Script
      • System Console
      • Server Script
      • Script API
  • Portal
    • Discussions
    • Portal Pages
    • Web Forms
    • Blog Post
  • Python API
    • Background Jobs
    • Document API
    • Database API
    • Jinja API
    • Request Lifecycle
    • Language Resolution
    • Utility Functions
    • Responses
    • Search
    • Hooks
    • REST API
    • FullTextSearch API
    • Dialog API
    • Query Builder
  • JS API
    • Form Scripts
    • Controls
    • List
    • Page API
    • Tree
    • Common Utilities API
    • Dialog API
    • Chart API
    • Scanner API
    • Server Calls (AJAX)
    • Logging
    • Form Tours
  • Integration
    • Razorpay
      • RazorPay
    • Google Drive
      • Google Drive
    • LDAP
      • Setting up LDAP
    • Rest Api
  • Videos
    • Video Tutorials for Frappe Framework
  • Bench
    • Bench
    • Bench Commands
    • Frappe Commands
    • Bench - Extending the CLI
    • Resources
      • Background Services
      • Bench Commands Cheatsheet
      • Bench Procfile
    • Guides
      • Adding Custom Domains to your Site
      • Configuring HTTPS
      • Diagnosing The Scheduler
      • Using Let's Encrypt to setup HTTPS
      • Manual Setup
      • Setting Limits for your Site
      • Setup Multitenancy
      • Setup Production
    • Reference
      • Commands Reference
      • bench new-site
      • bench drop-site
      • bench migrate
      • bench backup
      • bench reinstall
      • bench list-apps
      • bench uninstall-app
      • bench show-config
      • bench set-config
      • bench restore
      • bench partial-restore
      • bench version
      • bench transform-database
      • bench trim-database
      • bench trim-tables
  • Deployment
    • Database Migrations
    • Production Setup
    • Rate Limiting
  • Writing Tests
    • Testing
    • UI Testing
  • Help
    • Debugging
    • How to contribute
    • Profiling and Monitoring
    • Translations
    • Logging
  1. Guides
  2. App Development
  3. Overriding Link Query By Custom Script

Overriding Link Query By Custom Script

You can override the standard link query by using set_query via the Client Script DocType from the desk.

The set_query method takes one of two formats: set_query(field_name, options_function()) for regular fields, or set_query(field_name, child_table_name, options_function()) for fields in child tables.

1. Adding Filters

You can add filters to the query:

frappe.ui.form.on("Bank Reconciliation", "onload", function(frm) {
    frm.set_query("bank_account", function() {
        return {
            "filters": {
                "account_type": "Bank",
                "group_or_ledger": "Ledger"
            }
        };
    });
});

A more complex query:

frappe.ui.form.on("Bank Reconciliation", "onload", function(frm){
    frm.set_query("bank_account", function(){
        return {
            "filters": [
                ["Bank Account", "account_type", "=", "Bank"],
                ["Bank Account", "group_or_ledger", "!=", "Group"]
            ]
        }
    });
});

2. Calling a Different Method to Generate Results

You can also set a server side method to be called on the query:

frm.set_query("item_code", "items", function() {
    return {
        query: "erpnext.controllers.queries.item_query",
        filters: frm.doc.enquiry_type === "Maintenance" ?
            {"is_service_item": "Yes"} : {"is_sales_item": "Yes"}
    };
});

Custom Method

The custom method should return a list of items for auto select. If you want to send additional data, you can send multiple columns in the list.

Parameters to the custom method are:

def custom_query(doctype, txt, searchfield, start, page_len, filters)

Example:

# searches for leads which are not converted
@frappe.whitelist()
@frappe.validate_and_sanitize_search_inputs
def lead_query(doctype, txt, searchfield, start, page_len, filters):
    return frappe.db.sql("""
        SELECT name, lead_name, company_name
        FROM `tabLead`
        WHERE docstatus < 2
            AND ifnull(status, '') != 'Converted'
            AND ({key} LIKE %(txt)s
                OR lead_name LIKE %(txt)s
                OR company_name LIKE %(txt)s)
            {mcond}
        ORDER BY
            IF(LOCATE(%(_txt)s, name), LOCATE(%(_txt)s, name), 99999),
            IF(LOCATE(%(_txt)s, lead_name), LOCATE(%(_txt)s, lead_name), 99999),
            IF(LOCATE(%(_txt)s, company_name), LOCATE(%(_txt)s, company_name), 99999),
            name, lead_name
        LIMIT %(start)s, %(page_len)s
    """.format(**{
            'key': searchfield,
            'mcond':get_match_cond(doctype)
        }), {
        'txt': "%{}%".format(txt),
        '_txt': txt.replace("%", ""),
        'start': start,
        'page_len': page_len
    })

Note: @frappe.whitelist() is used to expose lead_query to the client-side and @frappe.validate_and_sanitize_search_inputs decorator is used to validate and sanitize user inputs sent through API or client-side request to avoid possible SQLi.

For more examples see:

https://github.com/frappe/erpnext/blob/develop/erpnext/controllers/queries.py

    peter_g edited 1 year ago ·
  • 2 Revisions 
  • Edit Page 
  • Delete Page 
  • New Page 
Left Right
On this page
  • 1. Adding Filters
  • 2. Calling a Different Method to Generate Results
    • Custom Method
Footer Logo
Getting Started
  • Introduction
  • Installation
  • Tutorial
  • Guides
  • Frappe School
Basics
  • What is Frappe?
  • Architecture
  • Apps
  • Sites
  • User Permissions
Admin UI
  • Desk
  • Reports
  • Printing
  • Scripting
  • Translations
Deployment
  • Bench
  • Production Setup
  • Migrations
  • Rate Limiting
Releases
  • Version 14
Forum Frappe Docs GitHub Telegram
© Frappe. Content licensed under CC-BY-SA 3.0.
+91 22 4897 0555 - hello@frappe.io

Built on Frappe