UI Dropdown - get result from SQL

Dashboard
1

Have some data from a MSSQL node that I want to insert as values in a Dropdown node, so I can return the selected value.

But have searched high and low, but have not found and examble I can use

Data…
[{“Name”:“Name1”},{“Name”:“Name2”},{“Name”:“Name3”},{“Name”:“Name4”},{“Name”:“Name5”},{“Name”:“Name6”},{“Name”:“Name7”},{“Name”:“Name8”}]

1 Like
2

Hi,

from the info sidebar of the ui_dropdown node:

The Options may be configured by inputting msg.options containing an array. If just text then the value will be the same as the label, otherwise you can specify both by using an object of "label":"value" pairs :
[ "Choice 1", "Choice 2", {"Choice 3":"3"} ]

So you need to convert you data to the format:

["Name1","Name2","Name3","Name4", ... etc]

You can do that with a Change node using a JSONata expression:

Local_Node-RED
Local_Node-RED984×238 7.04 KB

[{"id":"15e33f54.d14571","type":"change","z":"662bde8e.6979a","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"$.payload.Name","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":510,"y":190,"wires":[["95f2e469.76f718"]]}]

And pass the resulting message to you ui_dropdown node.

3

Great thanks - learning all the time

4

Hi Nick,

I don't know how to do this. Do you have any example flow for my understanding?

Database: MySQL

Thanks for helping.

5

Nick is currently on vacation (as am I). Which bit are you stuck on ? What have you tried so far ?

6

Hi dceejay,

Thanks for helping. The following is what I had tried so far. I'm unable to output the options to the dropdown.

image
image.png913×107 7.8 KB

The following is the JSON of the flow:

[{"id":"c0e20345.80304","type":"mui_dropdown","z":"b54c837d.8775b","name":"","label":"","place":"Select option","group":"c5b665f2.5666e8","order":0,"width":0,"height":0,"passthru":false,"options":[{"label":"","value":0,"type":"num"}],"payload":"","topic":"","x":690,"y":90,"wires":[["a61bb0c4.66aad"]]},{"id":"57fcfb43.f66374","type":"function","z":"b54c837d.8775b","name":"select-query","func":"msg.topic=\"SELECT id,location FROM location\";\nvar loc = msg.payload.location;\nvar id = msg.payload.id;\nmsg.options = [{loc:id}];\nreturn msg;","outputs":1,"noerr":0,"x":355,"y":90,"wires":[["76c01968.0365c8"]]},{"id":"d7b758c0.613c58","type":"inject","z":"b54c837d.8775b","name":"","topic":"","payload":"","payloadType":"date","repeat":"1","crontab":"","once":false,"onceDelay":0.1,"x":175,"y":90,"wires":[["57fcfb43.f66374"]]},{"id":"76c01968.0365c8","type":"mysql","z":"b54c837d.8775b","mydb":"c64480b2.86566","name":"database","x":525,"y":90,"wires":[["c0e20345.80304"]]},{"id":"a61bb0c4.66aad","type":"debug","z":"b54c837d.8775b","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":865,"y":90,"wires":[]},{"id":"c5b665f2.5666e8","type":"mui_group","z":0,"name":"Inari Logo","tab":"64262644.5f27d8","order":1,"disp":false,"width":"4","collapse":false},{"id":"c64480b2.86566","type":"MySQLdatabase","z":"","host":"10.21.11.15","port":"3306","db":"i4inari","tz":""},{"id":"64262644.5f27d8","type":"mui_tab","z":0,"name":"I4Inari","icon":"home","order":1}]
7

As per previous replies you need to modify the reply from your MySQL node into msg.options (rather than msg.payload) and into the correct format. Add a debug node after your MySQL node to see what you have so far. Then look at the code Nick showed above and try to work out how you need to change it

8

I had tried the following, but still unable to output the options. The following is the JSON of the flow:

[{"id":"c0e20345.80304","type":"mui_dropdown","z":"b54c837d.8775b","name":"","label":"","place":"Select Option","group":"c5b665f2.5666e8","order":0,"width":0,"height":0,"passthru":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":900,"y":90,"wires":[[]]},{"id":"57fcfb43.f66374","type":"function","z":"b54c837d.8775b","name":"select-query","func":"msg.topic=\"SELECT id,location FROM location\";\n//var loc = msg.payload.location;\n//var id = msg.payload.id;\n//msg.options = [{loc:id}];\nreturn msg;","outputs":1,"noerr":0,"x":355,"y":90,"wires":[["76c01968.0365c8"]]},{"id":"d7b758c0.613c58","type":"inject","z":"b54c837d.8775b","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"onceDelay":0.1,"x":175,"y":90,"wires":[["57fcfb43.f66374"]]},{"id":"76c01968.0365c8","type":"mysql","z":"b54c837d.8775b","mydb":"c64480b2.86566","name":"database","x":525,"y":90,"wires":[["7fa19071.7dece"]]},{"id":"a61bb0c4.66aad","type":"debug","z":"b54c837d.8775b","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":910,"y":165,"wires":[]},{"id":"7fa19071.7dece","type":"change","z":"b54c837d.8775b","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"$.payload.location","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":710,"y":90,"wires":[["c0e20345.80304","a61bb0c4.66aad"]]},{"id":"c5b665f2.5666e8","type":"mui_group","z":0,"name":"Inari Logo","tab":"64262644.5f27d8","order":1,"disp":false,"width":"4","collapse":false},{"id":"c64480b2.86566","type":"MySQLdatabase","z":"","host":"10.21.11.15","port":"3306","db":"i4inari","tz":""},{"id":"64262644.5f27d8","type":"mui_tab","z":0,"name":"I4Inari","icon":"home","order":1}]

This is what I can see on the debug tab:

image

9

I got it now, but I would like to save the value of the dropdown instead of label.

For example: value = 1 label = Option 1

I want Option 1 to be displayed in dashboard and save value 1 to the database.

10

The sidebar information for the drop-down node tells you the format you need to use. I won't paste it again as it is there for you to read and I already copied it earlier in this thread.

11

Hi Nick,

I already seen it. But, what I want is to display the label and save the value of the option.

Thanks for helping.

12

So do what the sidebar says when formatting your msg.options value.

[{"Plant 13": 3}, ... ]
13

What if I'm taking the "label":"value" from the database?

14

It doesn't matter where you are getting the data from... You need to get it into the right format expected by the dashboard node.

You will need to write some Javascript in a function node to do it. Or use a JSONata expression in a change node.

15

I'm sorry, Nick. But, I'm unclear at the Change node part.

Capture%20-%20Final

How to do the expression for "label":"value" here so that I'm able to save the value to the database instead of label?

16

If you aren't clear about the change node and JSONata, then write a JavaScript function to transform your data. I'm now out for the day.

17

Based on the screenshot of the data you have in this post - UI Dropdown - get result from SQL

You can either use a Function node with:

msg.options = msg.payload.map(function(value) {
    var v = {};
    v[value.location] = value.id;
    return v;
})
return msg;

Or a Change node, configured to "Set msg.options to the expression: $.payload.{location:id}

18

I tried as what you said. I had configured set msg.options to the expression: $.payload.{location:id} , but I'm getting the following error:

image

19

In which case your data is in a different format to what you said it is in. Or you have an entry in the database without a location property.

Did you try the Function node example?

20

I have location column in the database. I also tried function node example. Is said msg.payload.map is not a function.