List Select Option From Database

ERPNext
,
1

Hi, Everyone. Is it possible if data from select option list from database?

2

Hi,

See if a Custom Report will do.

https://docs.erpnext.com/docs/v13/user/manual/en/customize-erpnext/articles/making-custom-reports

3

Hi @joeK ,

Not sure exactly what your use case is. I will share one of mine, though.

I had a DocField that is a “Select”. I wanted to change its Options at runtime.

  • I chose “Select”, because I wanted the User to choose from a list of values, and not deviate.
  • I could not use Link or Dynamic Link, because the list of values is not that simple. The list of values is always calculated, depending on other values on the form.

Here’s my JS code, edited and commented to be a bit more generic.

// First, get your database data.
// This data could be from anywhere.  Maybe current page elements, or using frappe.call(), or various other ways.
let my_database_data = <your logic goes here>   // this should be an array of values.

// Next, I created an array for my "Select" field's drop-down values:
let my_select_array = [].push('');   // Add an initial, empty string choice.

// Loop through your database data, adding each result to your Select array:
my_database_data.forEach( (some_value) => {
    my_select_array.push(some_value);
}
my_select_array.sort();  // Optional.  I wanted to sort my data.

// Now you need to get your DocField's meta data, and edit its Options:
frappe.meta.get_docfield("MyDocType", "my_docfield_name", frm.doc.name).options = my_select_array;

// Although 'options' are now set, I found it was very tricky to ask the page to refresh the values.  This worked "sometimes"
frm.refresh_field("my_docfield_name");
1 Like
4

Hi @brian_pond ,

Where i put this JS code in erp next? and its possible if i use this code for check connection and get data from database

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='databaseName',
                                         user='databaseUser',
                                         password='databasePassword')
    if connection.is_connected():
        db_Info = connection.get_server_info()
        print("Connected to MySQL Server version ", db_Info)
        cursor = connection.cursor()
        cursor.execute("select database();")
        record = cursor.fetchone()
        print("You're connected to database: ", record)

except Error as e:
    print("Error while connecting to MySQL", e)
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")
5

Your Python code seems okay. To use it, you’ll need a few more things:

  1. Wrap your Python code inside a function.
  2. Decorate the function with @frappe.whitelist()
    • Required if you want JS to call your function.

Here’s a simple example:

@frappe.whitelist()|
def get_some_data_values():
    """
    This function returns a Python List of 3 values.  It is called by my JavaScript code!
    """
    return ['foo', 'bar', 'baz']

Of course, replace your own code. Just make sure you -do- return something.

Next, the JavaScript. Let’s assume you’re working on a DocType named ‘MyTest’. You’ll want to edit the file “mytest.js”


frappe.ui.form.on('MyTest', {

	refresh: function(frm) {

		frappe.call({
			method: "my_app.my_module.doctype.mytest.mytest.get_some_data_values",
			callback: function(r) {
				if(r.message) {
					console.log(r.message);
				}
			}
		});
    }
});

In my example above, the key named “method” has a value that is the path to your Python function.

If I actually put this together: my browser would print to console an Object with “foo” “bar” and “baz”, when I opened a MyTest document.

Hopefully this helps get you started. JavaScript is not my strongest area. Other forum members could probably explain more elegant solutions.

My best advice:

  1. Start small. Write a tiny Python function that returns a String or Integer. Practice reading that from your browser. Then start adding more pieces, like returning larger objects, making custom database calls, etc.

  2. Examine the ERPNext Code. Something like Customer, or Supplier, or Item. Read the JS code for those DocTypes. They have many examples of what I’m talking about.

Until you’re more experienced, I would avoid the JS code in DocTypes like Sales Order, Purchase Order, or Stock Entry. Those transactional DocTypes have a lot of “extra stuff” that is not documented, and can get very confusing.

3 Likes
6

Hi @brian_pond , thanks for advice :+1: