UI Dropdown - Populating Data

General
1

I read a few posts on the UI dropdown but I am not able to see my test data.

I created a function to create an array of data:
msg.payload = [ "Name1","Name2","Name3","Name4" ]
return msg;

Used the change node:
image

My /ui is not displaying the values. Do I need to identify something in the dropdown node?

image
image.png514Ă—756 15.8 KB

image
image.png887Ă—131 9.18 KB

2

Actually it would be better not using the function node, unless you have some other additional purpose in mind (other than initializing msg.options).

Flow below seems to work:

[{"id":"ae72b941.c54948","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"9baec202.fcc6e","type":"ui_dropdown","z":"ae72b941.c54948","name":"","label":"","place":"Select option","group":"f4ddc713.cb9a58","order":0,"width":0,"height":0,"passthru":true,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":580,"y":140,"wires":[[]]},{"id":"276d9610.54d42a","type":"inject","z":"ae72b941.c54948","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":240,"y":140,"wires":[["1913cada.05b895"]]},{"id":"1913cada.05b895","type":"change","z":"ae72b941.c54948","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"[\"name1\", \"name2\",\"name3\",\"name4\"]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":400,"y":140,"wires":[["9baec202.fcc6e","9494803c.d15ad"]]},{"id":"9494803c.d15ad","type":"debug","z":"ae72b941.c54948","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"options","x":580,"y":200,"wires":[]},{"id":"f4ddc713.cb9a58","type":"ui_group","z":"","name":"T1","tab":"763667c5.ae1af8","disp":true,"width":"6","collapse":false},{"id":"763667c5.ae1af8","type":"ui_tab","z":"","name":"LAB","icon":"dashboard"}]
3

Thank you for the fast response. We have a bash script that gets build numbers from SVN. I was trying to use the function node to mirror the data format that I need to pass to the change node. Should I use a different node to monkey up test data?

Once we get this data in a dropdown, we can then select our build number and kick off a back-end job to run that build. I thought the data from our shell script would need to look like this:
["Build1","Build2","Build3"]

I have not been able to get any data to show up in the dropdown list. I just tried putting the data you suggested in my function but that did not work for me. Am I missing some formatting in how I setup the dropdown node?

4

Your function node is ok. The issue is the rule in the change node. See how it should be:

[{"id":"ae72b941.c54948","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"9baec202.fcc6e","type":"ui_dropdown","z":"ae72b941.c54948","name":"","label":"","place":"Select option","group":"f4ddc713.cb9a58","order":0,"width":0,"height":0,"passthru":true,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":680,"y":140,"wires":[[]]},{"id":"276d9610.54d42a","type":"inject","z":"ae72b941.c54948","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":140,"wires":[["c5df7390.37c45"]]},{"id":"1913cada.05b895","type":"change","z":"ae72b941.c54948","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":500,"y":140,"wires":[["9baec202.fcc6e","9494803c.d15ad"]]},{"id":"9494803c.d15ad","type":"debug","z":"ae72b941.c54948","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"options","x":680,"y":200,"wires":[]},{"id":"c5df7390.37c45","type":"function","z":"ae72b941.c54948","name":"Test","func":"msg.payload = [ \"Name1\",\"Name2\",\"Name3\",\"Name4\" ]\nreturn msg;","outputs":1,"noerr":0,"x":310,"y":140,"wires":[["1913cada.05b895"]]},{"id":"f4ddc713.cb9a58","type":"ui_group","z":"","name":"T1","tab":"763667c5.ae1af8","disp":true,"width":"6","collapse":false},{"id":"763667c5.ae1af8","type":"ui_tab","z":"","name":"LAB","icon":"dashboard"}]

r-01

5

Such an easy change that I never thought of. That worked for me.

Thank you! A couple of us tried multiple options for at least a couple of hours before reaching out for help. I really do appreciate the info.

1 Like
6

Dear Andrei, i was looking for some tips for creating the list of elements out of the array returned from the MySQL query, but havn't found any. The only solution here is a setting already predefined list (array) of strings, to push it to the dropdown list. Could you please suggest how i can access the value of the returned msq.payload, to fulfil the dropdown list on a dashboard. Thanks a lot in advance!

1 Like
7

can you share an example of what the existing array looks like and which parts need to be in the dropdown ?

1 Like
8

Hi, I dont have a MySQL to play with so I have simulated data with an inject node & fully commented a function node to help you.

The heart of this solution is to transform whatever format you get from the database (use a debug node to determine that) into a format the dropdown node likes (see the info panel when a dropdown node is selected for clues)

JS Function node: Transform the data...

//as per info panel, the data needs to be formatted
//as [ "Choice 1", "Choice 2", {"Choice 3":"3"} ]
//and sent in msg.options.
msg.options = [];//create empty array
for(let i = 0; i < msg.payload.length; i++){
    let row = msg.payload[i]; //get the row
    let opt = {};//make new opt object
    opt[row[0]] = row[1]; //add a propery to object called row[0] & set its value to row[1]
    msg.options.push(opt);//add the opt to array    
}
return msg;//pass the msg (containing our options) to the next node

A working flow...

[{"id":"4526cd47.623524","type":"inject","z":"4ee0e132.e7df2","name":"Fake DB Result","topic":"","payload":"[[\"bob\",1],[\"rita\",2],[\"sue\",3]]","payloadType":"json","repeat":"","crontab":"","once":true,"onceDelay":0.1,"x":160,"y":320,"wires":[["e2acd642.38aa98"]]},{"id":"e2acd642.38aa98","type":"function","z":"4ee0e132.e7df2","name":"transform data to correct format","func":"//as per info panel, the data needs to be formatted\n//as [ \"Choice 1\", \"Choice 2\", {\"Choice 3\":\"3\"} ]\n//and sent in msg.options.\nmsg.options = [];//create empty array\nfor(let i = 0; i < msg.payload.length; i++){\n    let row = msg.payload[i]; //get the row\n    let opt = {};//make new opt object\n    opt[row[0]] = row[1]; //add a propery to object called row[0] & set its value to row[1]\n    msg.options.push(opt);//add the opt to array    \n}\nreturn msg;","outputs":1,"noerr":0,"x":410,"y":320,"wires":[["45e76152.32dcd"]]},{"id":"45e76152.32dcd","type":"ui_dropdown","z":"4ee0e132.e7df2","name":"","label":"","tooltip":"","place":"Select option","group":"1a45cdd4.2db222","order":0,"width":0,"height":0,"passthru":true,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":620,"y":320,"wires":[["ed546608.89a868"]]},{"id":"ed546608.89a868","type":"ui_toast","z":"4ee0e132.e7df2","position":"top right","displayTime":"3","highlight":"","outputs":0,"ok":"OK","cancel":"","topic":"","name":"","x":320,"y":360,"wires":[]},{"id":"1a45cdd4.2db222","type":"ui_group","z":"","name":"Default","tab":"35ff53eb.0e3c8c","disp":true,"width":"6","collapse":false},{"id":"35ff53eb.0e3c8c","type":"ui_tab","z":"","name":"Menu","icon":"fa-tachometer","order":1,"disabled":false,"hidden":false}]
1 Like
9

Hi Liudmila, welcome to the forum. I am late to the party as Steve already provided a nice working example. If you share with us how the returned MySQL query looks like and how the dropdown should be then we could see how the solution from Steve could be adapted to fit your use case.

10

Hi, thanks, i didn't expect so many reply at so short time, i'll go throu all of them... meanwhile here is a flow i'm trying to create.

[{"id":"10759ae0.91fbd5","type":"change","z":"320f5181.7a99c6","name":"part_number_item_test1","rules":[{"t":"set","p":"options","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":870,"y":480,"wires":[["4367726.5ea520c"]]},{"id":"22d9c1f.87b00be","type":"inject","z":"320f5181.7a99c6","name":"First","topic":"","payload":"","payloadType":"date","repeat":"60","crontab":"","once":false,"onceDelay":0.1,"x":210,"y":480,"wires":[["de6e758.9e46308"]]},{"id":"de6e758.9e46308","type":"function","z":"320f5181.7a99c6","name":"SELECT new Query","func":"msg.topic='select distinct part_number_item from rfid_msg_arch'\nreturn msg;","outputs":1,"noerr":0,"x":450,"y":480,"wires":[["1a3aa540.18880b"]]},{"id":"1a3aa540.18880b","type":"mysql","z":"320f5181.7a99c6","mydb":"41296d80.f96174","name":"DTWIN","x":675,"y":480,"wires":[["10759ae0.91fbd5"]],"l":false},{"id":"4367726.5ea520c","type":"ui_dropdown","z":"320f5181.7a99c6","name":"","label":"test","tooltip":"","place":"Select option","group":"46f7ff7c.8dcba","order":1,"width":0,"height":0,"passthru":true,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":1110,"y":480,"wires":[[*]]},{"id":"41296d80.f96174","type":"MySQLdatabase","z":"","host":"10.41.11.142","port":"3306","db":"dtwin","tz":""},{"id":"46f7ff7c.8dcba","type":"ui_group","z":"320f5181.7a99c6","name":"Real Machine","tab":"82918b21.3bcfb","disp":true,"width":"6","collapse":false},{"id":"82918b21.3bcfb","type":"ui_tab","z":"","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]
and the array returned is like in the immage.
the funny thing what i can see the list of name of the array, and then in the flow the value:

Picture1
Picture11225Ă—775 123 KB

11

Unfortunately your flow isn't currently importable.Please read the following post How to share code or flow json and then edit the above message.

12

Hi, thanks for the working example, but the problem is what i'd like to see the second value of the array, not the first one, the first one i managed to see with the original code and it's useless, since it is the name of the column and always the same .. :frowning:

13

Since the structure of your data matches the structure of the dropdowns required msg.options you're getting exactly what you give it.

What is it you want displayed in the dropdown & what do you want for the value when its selected?

According to your screen shot you have "part_number_item" or its "value" only in your data from the DB (somewhat limiting you to display the value or "part_number_item"!).

If you want something else displayed (like a friendly name) you'll need to get that from somewhere (i.e. DB) or create a lookup in node-red.

I have slightly modified my test flow to show you how to display the value instead of "part_number_item" using fake data in similar format to your screen shot
flow

[{"id":"c5e1961a.63c208","type":"inject","z":"9f43aee.ab3dc5","name":"Fake DB Result","topic":"","payload":"[{\"part_number_item\":\"17260851BD\"},{\"part_number_item\":\"17299989AC\"},{\"part_number_item\":\"17246783FB\"}]","payloadType":"json","repeat":"","crontab":"","once":true,"onceDelay":0.1,"x":200,"y":280,"wires":[["4af43cc.3641ec4","3a6e7cef.bc0974"]]},{"id":"4af43cc.3641ec4","type":"function","z":"9f43aee.ab3dc5","name":"transform data","func":"//as per info panel, the data needs to be formatted\n//as [ \"Choice 1\", \"Choice 2\", {\"Choice 3\":\"3\"} ]\n//and sent in msg.options.\nmsg.options = [];//create empty array\nfor(let i = 0; i < msg.payload.length; i++){\n    let row = msg.payload[i]; //get the row\n    let opt = {};//make new opt object\n    opt[row.part_number_item] = row.part_number_item; \n    msg.options.push(opt);//add the opt to array    \n}\nreturn msg;","outputs":1,"noerr":0,"x":400,"y":260,"wires":[["c8d299c.7aaca68"]]},{"id":"c8d299c.7aaca68","type":"ui_dropdown","z":"9f43aee.ab3dc5","name":"","label":"","tooltip":"","place":"Select option","group":"ec26fe5d.d806c","order":0,"width":0,"height":0,"passthru":true,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":560,"y":260,"wires":[["dd13bfaa.86901"]]},{"id":"dd13bfaa.86901","type":"ui_toast","z":"9f43aee.ab3dc5","position":"top right","displayTime":"3","highlight":"","outputs":0,"ok":"OK","cancel":"","topic":"","name":"","x":810,"y":280,"wires":[]},{"id":"39fb345.20572cc","type":"ui_dropdown","z":"9f43aee.ab3dc5","name":"","label":"","tooltip":"","place":"Select option","group":"ec26fe5d.d806c","order":0,"width":0,"height":0,"passthru":true,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":560,"y":300,"wires":[["dd13bfaa.86901"]]},{"id":"3a6e7cef.bc0974","type":"change","z":"9f43aee.ab3dc5","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":400,"y":300,"wires":[["39fb345.20572cc"]]},{"id":"ec26fe5d.d806c","type":"ui_group","z":"","name":"Default","tab":"24d3dde5.1cc832","disp":true,"width":"6","collapse":false},{"id":"24d3dde5.1cc832","type":"ui_tab","z":"","name":"Menu","icon":"fa-tachometer","order":1,"disabled":false,"hidden":false}]
1 Like
14

See my second response above ^ :slight_smile:

15

This is exactly what i was looking for! Thanks a lot!

1 Like
16

Can i ask you one more question thought, everything is working, but the first time it gave me such an ugly screen:

immagine
immagine.png593Ă—759 30.2 KB

when i choose one of the value everything is gone, but the first time i get all the returned values on the screen. What's wrong with my flow?

17

hard to tell as you haven't posted your flow.

At a guess, you are passing the results of the DB to a node (text node?) that is rendering it on the page?

Or its a bug in the drop down?

18

I guess when you are creating the options you are still passing the whole msg.payload (with all the results) through as well... you should either delete msg.payload or set it to the default value you want selected (or " ")

19 
[{"id":"8df323ba.397c58","type":"function","z":"589c69f6.b27b4","name":"transform data","func":"//as per info panel, the data needs to be formatted\n//as [ \"Choice 1\", \"Choice 2\", {\"Choice 3\":\"3\"} ]\n//and sent in msg.options.\nmsg.options = [];//create empty array\nfor(let i = 0; i < msg.payload.length; i++){\n    let row = msg.payload[i]; //get the row\n    let opt = {};//make new opt object\n    opt[row.part_number_item] = row.part_number_item; \n    msg.options.push(opt);//add the opt to array    \n}\nreturn msg;","outputs":1,"noerr":0,"x":680,"y":100,"wires":[["de712e8a.ed1a9"]]},{"id":"de712e8a.ed1a9","type":"ui_dropdown","z":"589c69f6.b27b4","name":"","label":"Part number","tooltip":"","place":"Select option","group":"e96d9541.1c80b8","order":0,"width":"2","height":"2","passthru":true,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":850,"y":100,"wires":[["6ba25c63.3a220c","4dfe8292.d1e844"]]},{"id":"6ba25c63.3a220c","type":"ui_toast","z":"589c69f6.b27b4","position":"top right","displayTime":"3","highlight":"","outputs":0,"ok":"OK","cancel":"","topic":"","name":"","x":1110,"y":100,"wires":[]},{"id":"f22e20b2.d9b6e","type":"mysql","z":"589c69f6.b27b4","mydb":"41296d80.f96174","name":"DTWIN","x":520,"y":100,"wires":[["8df323ba.397c58"]]},{"id":"83846a4e.53d2d8","type":"function","z":"589c69f6.b27b4","name":"Select data from DB","func":"msg.topic='select distinct part_number_item from rfid_msg_arch'\nreturn msg;","outputs":1,"noerr":0,"x":320,"y":100,"wires":[["f22e20b2.d9b6e"]]},{"id":"22babdf3.51cdaa","type":"inject","z":"589c69f6.b27b4","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"onceDelay":0.1,"x":110,"y":100,"wires":[["83846a4e.53d2d8"]]},{"id":"e96d9541.1c80b8","type":"ui_group","z":"","name":"Details information","tab":"15da4b99.2d159c","disp":true,"width":"6","collapse":false},{"id":"41296d80.f96174","type":"MySQLdatabase","z":"","host":"10.41.11.142","port":"3306","db":"dtwin","tz":""},{"id":"15da4b99.2d159c","type":"ui_tab","z":"","name":"Menu","icon":"fa-tachometer","order":1,"disabled":false,"hidden":false}]
20

No text field, i'm trying to pass the array to the dropdown list.After that i was thinking to pass the selected value to the next node to do another filtered query from DB... and this part looks to work fine. But the first screen is something strange...