Populate data from database into Dropdownlist

General
1

When I use a Query on Mysql I get an array with 120 objects.

I would like to push these results in a dropdownlist, but all I know is how to write a function to get 1 value out of the array, not all of them.

So first step is that I would need a function that gets all my values from my array.
Next step would be to get those results into the dropdownlist (and later work further from there)

Here is my flow:

[{"id":"3b93f73c.7a4e48","type":"tab","label":"TEST","disabled":false,"info":""},{"id":"a134927a.6ac65","type":"mysql","z":"3b93f73c.7a4e48","mydb":"98dc2b32.560778","name":"","x":500,"y":180,"wires":[["801c5d97.3feb3"]]},{"id":"c23f9c75.3db44","type":"inject","z":"3b93f73c.7a4e48","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":180,"wires":[["81e5bd0.5c3dd4"]]},{"id":"81e5bd0.5c3dd4","type":"function","z":"3b93f73c.7a4e48","name":"","func":"msg.topic = \"SELECT `channel` FROM `NodeRed`.`7bchannels`\"\nreturn msg;","outputs":1,"noerr":0,"x":310,"y":180,"wires":[["a134927a.6ac65"]]},{"id":"fe2bd468.16c1a8","type":"debug","z":"3b93f73c.7a4e48","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1110,"y":180,"wires":[]},{"id":"70f497ac.1896a8","type":"ui_dropdown","z":"3b93f73c.7a4e48","name":"","label":"","tooltip":"","place":"Select option","group":"a782ff3f.bf5bb","order":0,"width":0,"height":0,"passthru":true,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":1100,"y":240,"wires":[[]]},{"id":"801c5d97.3feb3","type":"function","z":"3b93f73c.7a4e48","name":"","func":"msg.payload = msg.payload[0]['channel']\nreturn msg;","outputs":1,"noerr":0,"x":690,"y":180,"wires":[["fe2bd468.16c1a8","70f497ac.1896a8"]]},{"id":"98dc2b32.560778","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"NodeRed","tz":""},{"id":"a782ff3f.bf5bb","type":"ui_group","z":"","name":"Default","tab":"f0a3f99c.51e178","disp":true,"width":"6","collapse":false},{"id":"f0a3f99c.51e178","type":"ui_tab","z":"","name":"Test","icon":"dashboard","order":6,"disabled":false,"hidden":false}]

snip

2

So you there are several different ways you could do it.
You could look at jsonata there are tutorials on the jsonata website

Or you could do it in a function node with javascript, where you will need to iterate through the array. There are lots of tutorials online for javascript which will show you how to do this.

3

Oh fun.....you have an array of objects :slight_smile:

NOTE: my function "create an array of objects is just mimicking your output from your database

arofobj
arofobj1098Ă—67 7.95 KB

[{"id":"9de0d6f5.91f458","type":"inject","z":"548d67a6.28d68","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":180,"wires":[["27d7a3.bff6685e"]]},{"id":"27d7a3.bff6685e","type":"function","z":"548d67a6.28d68","name":"create an array of objects","func":"\nmsg.payload = \n[ \n    {\"channel\":\"1A6\"},\n    {\"channel\":\"1B6\"},\n    {\"channel\":\"1C6\"},\n    {\"channel\":\"1D6\"}\n];\nreturn msg;","outputs":1,"noerr":0,"x":370,"y":180,"wires":[["c26ba790.1382a8"]]},{"id":"a34cc810.384d28","type":"debug","z":"548d67a6.28d68","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":1010,"y":180,"wires":[]},{"id":"c26ba790.1382a8","type":"function","z":"548d67a6.28d68","name":"format array of objects for dropdown in dashboard","func":"//Reformat array to work with dashboard\nvar reformattedArray = msg.payload.map(obj =>{\n    //declare the reformatted object\n    var rObj = {};\n    //format it for drop down\n    //\"label\":\"value\" is the format so lets move the value to the label and add some txt as well\n    //you can also replce the txt with a var if you want\n    rObj[obj.channel+\" text you want to add\"] = obj.channel;\n    return rObj;\n});\nmsg.payload =  reformattedArray;\nreturn msg;\n","outputs":1,"noerr":0,"x":710,"y":180,"wires":[["a34cc810.384d28"]]}]

code in the function node formatting it

//Reformat array to work with dashboard

var reformattedArray = msg.payload.map(obj =>{

//declare the reformatted object
var rObj = {};

//format it for drop down
//"label":"value" is the format so lets move the value to the label and add some txt as well
//you can also replce the txt with a var if you want
rObj[obj.channel+" text you want to add"] = obj.channel;
return rObj;

});

msg.payload = reformattedArray;
return msg;

output

4

I just had fun with this last week. I wanted a drop down list for all my temp sensors so I could add them to my database as I connect them.

myarofobj

5

Thank you meekie007

When I import your flow, it still outputs the array with data. So I am wondering about your next step, the dropdown. It looks like you do more or less the same as I am trying to do.
Can you show me the setup of your dropdown node ?

6

Hope this helps.

yepyep

current flow

[{"id":"548d67a6.28d68","type":"tab","label":"MEEKI TEST","disabled":false,"info":""},{"id":"27d7a3.bff6685e","type":"function","z":"548d67a6.28d68","name":"create an array of objects","func":"\nmsg.payload = \n[ \n    {\"channel\":\"1A6\"},\n    {\"channel\":\"1B6\"},\n    {\"channel\":\"1C6\"},\n    {\"channel\":\"1D6\"}\n];\nreturn msg;","outputs":1,"noerr":0,"x":770,"y":180,"wires":[["c26ba790.1382a8"]]},{"id":"a34cc810.384d28","type":"debug","z":"548d67a6.28d68","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1130,"y":260,"wires":[]},{"id":"c26ba790.1382a8","type":"function","z":"548d67a6.28d68","name":"format array of objects for dropdown in dashboard","func":"//Reformat array to work with dashboard\nvar reformattedArray = msg.payload.map(obj =>{\n    //declare the reformatted object\n    var rObj = {};\n    //format it for drop down\n    //\"label\":\"value\" is the format so lets move the value to the label and add some txt as well\n    //you can also replce the txt with a var if you want\n    rObj[obj.channel+\" text you want to add\"] = obj.channel;\n    return rObj;\n});\nmsg.payload =  reformattedArray;\nreturn msg;\n","outputs":1,"noerr":0,"x":1110,"y":180,"wires":[["92aa60c.a2ca3a"]]},{"id":"92aa60c.a2ca3a","type":"change","z":"548d67a6.28d68","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"payload","tot":"msg"},{"t":"delete","p":"payload","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":760,"y":260,"wires":[["a240a9f9.81f17"]]},{"id":"a240a9f9.81f17","type":"ui_dropdown","z":"548d67a6.28d68","name":"","label":"","tooltip":"","place":"Select Value From Database","group":"cec06512.a43738","order":0,"width":0,"height":0,"passthru":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":980,"y":260,"wires":[["a34cc810.384d28"]]},{"id":"532aaeea.6c868","type":"change","z":"548d67a6.28d68","name":"","rules":[{"t":"delete","p":"payload","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":420,"y":180,"wires":[["27d7a3.bff6685e","b6a44462.ff9ee"]]},{"id":"1ab11e52.667262","type":"switch","z":"548d67a6.28d68","name":"","property":"name","propertyType":"msg","rules":[{"t":"eq","v":"Meeki Test","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":250,"y":180,"wires":[["532aaeea.6c868"]]},{"id":"6bf567b8.7c91","type":"ui_ui_control","z":"548d67a6.28d68","name":"","events":"change","x":120,"y":180,"wires":[["1ab11e52.667262"]]},{"id":"6badeb6e.36273c","type":"comment","z":"548d67a6.28d68","name":"Trigger to populate list when user opens menu tab","info":"","x":290,"y":120,"wires":[]},{"id":"b6a44462.ff9ee","type":"function","z":"548d67a6.28d68","name":"create an array of objects","func":"\nmsg.payload = \n[ \n    {\"channel\":\"1A6\"},\n    {\"channel\":\"1B6\"},\n    {\"channel\":\"1C6\"},\n    {\"channel\":\"1D6\"}\n];\nreturn msg;","outputs":1,"noerr":0,"x":770,"y":440,"wires":[["e1bed24c.5255a8"]]},{"id":"bdd7eaec.933898","type":"comment","z":"548d67a6.28d68","name":"Dropdown with formatting","info":"","x":910,"y":120,"wires":[]},{"id":"358443d4.f8ee04","type":"comment","z":"548d67a6.28d68","name":"Dropdown without formatting","info":"","x":920,"y":380,"wires":[]},{"id":"e1bed24c.5255a8","type":"change","z":"548d67a6.28d68","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"payload","tot":"msg"},{"t":"delete","p":"payload","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":760,"y":500,"wires":[["b425c21d.814518"]]},{"id":"b425c21d.814518","type":"ui_dropdown","z":"548d67a6.28d68","name":"","label":"","tooltip":"","place":"Select Value From Database","group":"810d7da4.fff398","order":0,"width":0,"height":0,"passthru":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":940,"y":500,"wires":[["59f0a5e5.aa864c"]]},{"id":"59f0a5e5.aa864c","type":"debug","z":"548d67a6.28d68","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1090,"y":500,"wires":[]},{"id":"cec06512.a43738","type":"ui_group","z":"","name":"Formatted","tab":"b4be09eb.9c2dc","disp":true,"width":"6","collapse":false},{"id":"810d7da4.fff398","type":"ui_group","z":"","name":"Un-Formatted","tab":"b4be09eb.9c2dc","disp":true,"width":"6","collapse":false},{"id":"b4be09eb.9c2dc","type":"ui_tab","z":"","name":"Meeki Test","icon":"dashboard","order":6,"disabled":false,"hidden":false}]

flow
flow.png1377Ă—550 44.8 KB

7

Also the function node i sent you to format the array of objects is hard to understand what's happening because so little transformation is taking place.

This is my array of objects I needed to format.
asdf

I want my drop down to show the sensor id and the temp ; thats it, not the family or the file ... etc

So in the function I have.

//Reformat array to work with dashboard
var reformattedArray = msg.payload.map(obj =>{
var rObj = {};
rObj[obj.id+" "+obj.temp.toFixed(1)+"°C"+' | '+(((obj.temp*9)/5)+32).toFixed(1)+"°F"] = obj.id;
return rObj;
});

I've bolded what you should notice:
rObj[obj.id+" "+obj.temp.toFixed(1)+"°C"+' | '+(((obj.temp*9)/5)+32).toFixed(1)+"°F"] = obj.id;

what its basically saying is:
replace the id before "FF84C7B31701" with the string "sensor id - sensor temp"

and so I now get this after my reformatting function
Screenshot%20from%202019-09-26%2014-48-51

I hope this helps you think bigger with what you can do for your drop down. This can be complicated at first but it will save you allot of time and clutter later if you get it working right.

1 Like
8

Thank you.

This does the job. All I needed was to add the change node.
I see that you added there a delete msg.payload, why is that ?

9

@kresten88 I see that you added there a delete msg.payload, why is that ?

Force of habitat. Too many times ive had a rouge msg.payload mess up my dropdown.
also the dropdown node states " Setting msg.payload to the value will set the pre-selected choice in the dropdown."

I don't want data coming in that might change what i want the default value to be.
or I have a pre selected user value as a place holder coming in from a file that is set to a flow.payload value that is than set to msg.payload; then drop down shows the last user selection.

Its a big flow :slight_smile: ive only shared a section of mine that is relevant to your needs.