UI Dropdown trying to allow for single entry or multiple

Dashboard
1

SO! I am using the UI dropdown node and having great success... until my entry is a single line. Iknow, I know! Why a dropdown node for a single entry. Let me explain. I am pulling from a database and most of the time I have several entries however there are cases that just came up that are single entries.

I am currently using the Change Node with Set-msg.options to an expression msg.payload.{company:client_id} then deleting the payload to to tidy up. This all works fine until I only have one item in my database table. SO! I tried msg.payload.[{company:client_id}] which works fine for the single entry but not for the multiple entries????

If I take the bracket asway I get a ERR: Invalid Options because it is no longer an array, but if I make it an array it makes arrays inside arrays... LOL
{"_msgid":"207c9a38.8343a6","topic":"select * from Device WHERE client_id=5;","options":{"Sean Window":"00000000d56a2bdd"}}

2

so what exactly have you tried ? Here are two examples one with one option and one with two

[{"id":"f0cfc369.2a5cf","type":"ui_dropdown","z":"5ef3fadf.31201c","name":"","label":"","tooltip":"","place":"Select option","group":"c0b1b8ca.93ab38","order":0,"width":0,"height":0,"passthru":true,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":510,"y":500,"wires":[[]]},{"id":"1f497ee7.dff881","type":"function","z":"5ef3fadf.31201c","name":"one option","func":"\nmsg.options = [ {\"Choice 1\":\"1\"} ]\n\nreturn msg;","outputs":1,"noerr":0,"x":330,"y":500,"wires":[["f0cfc369.2a5cf"]]},{"id":"e051001f.cf43a","type":"inject","z":"5ef3fadf.31201c","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"onceDelay":0.1,"x":170,"y":500,"wires":[["1f497ee7.dff881"]]},{"id":"200c578d.9d4f68","type":"ui_dropdown","z":"5ef3fadf.31201c","name":"","label":"","tooltip":"","place":"Select option","group":"c0b1b8ca.93ab38","order":0,"width":0,"height":0,"passthru":true,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":510,"y":560,"wires":[[]]},{"id":"13f40c5f.172c44","type":"function","z":"5ef3fadf.31201c","name":"two options","func":"\nmsg.options = [ {\"Choice 2\":\"2\"},{\"Choice 3\":\"3\"} ]\n\nreturn msg;","outputs":1,"noerr":0,"x":330,"y":560,"wires":[["200c578d.9d4f68"]]},{"id":"4f1a74fa.75532c","type":"inject","z":"5ef3fadf.31201c","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"onceDelay":0.1,"x":170,"y":560,"wires":[["13f40c5f.172c44"]]},{"id":"c0b1b8ca.93ab38","type":"ui_group","z":"","name":"Default","tab":"50372f16.d4f1a","disp":true,"width":"6","collapse":false},{"id":"50372f16.d4f1a","type":"ui_tab","z":"","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]
3

Thank you so much for your examples. I think maybe its how they are coming to me from the database that may be messing me up as I cant simple write them out as you did. I am getting in this from the database.
Capture2
Okay no problem right? LOL I am currently using a change node that looks like this.

Capture
And as expected, here is my return.

Capture3
Dumped into a dropdown with no problem. However when my table only has one entry, my change node expression no longer creates an array out of the single entry in the msg.options its now an object.

Capture4
Capture4.PNG307Ă—580 22.5 KB

The first debug is the return from the database again with one item in the table. The second debug is what is being sent into the dropdown using the same expression I showed above requesting the UUID and nickname from the DB array to be sent to the dropdown. The third is it not liking my formatting.

Do I need to write some sort of expression to hand an if statement based on array count or something of that sorts or does somebody know how to fix my change node expression to handle both?

4

Well it's returning an array of size 1 or more so it should be possible to do the right thing. Yes I'm sure there is some JSONata magic that could do it, though it eludes me at present. Another way would be to use a split node to split the array into individual messages - then use a change to delete the properties you don't need and move (rename) others to be label and value - the a join node to rebuild the array before feeding it to the select.

5

Thank you for taking a look. Its a tricky one for sure. Ill have to keep poking around!

6

Well if you can supply some test data (not screenshots) that we can cut/paste we would have a better chance to experiment.

7

how would you like it? How do I provide that here? The flow? its just database query that is supplying me with one item or several items and then trying to put both options into the dropdown whether its one item or several

[{"id":"73af97be.4caf68","type":"function","z":"8a7edab5.f9e298","name":"DropDn -> Decive","func":"msg.topic = \"select * from Device WHERE client_id=\"+msg.payload+\";\";\nmsg.payload = \"\";\nreturn msg;\n","outputs":1,"noerr":0,"x":350,"y":580,"wires":[["494f4be2.4025e4"]]},{"id":"e9b00572.e10ab8","type":"inject","z":"8a7edab5.f9e298","name":"","topic":"","payload":"7","payloadType":"num","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":190,"y":580,"wires":[["73af97be.4caf68"]]},{"id":"a6982238.0b9d6","type":"change","z":"8a7edab5.f9e298","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"msg.payload.{nickname:uuid}","tot":"jsonata"},{"t":"delete","p":"payload","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":660,"y":580,"wires":[["f6b79231.ea80e"]]},{"id":"f6b79231.ea80e","type":"ui_dropdown","z":"8a7edab5.f9e298","name":"","label":"","tooltip":"","place":"Select option","group":"11d9f965.afc2b7","order":1,"width":0,"height":0,"passthru":true,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":820,"y":580,"wires":[["7b2b67a0.3c9c08"]]},{"id":"7b2b67a0.3c9c08","type":"debug","z":"8a7edab5.f9e298","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":950,"y":580,"wires":[]},{"id":"494f4be2.4025e4","type":"mysql","z":"8a7edab5.f9e298","name":"","x":510,"y":580,"wires":[["a6982238.0b9d6"]]},{"id":"11d9f965.afc2b7","type":"ui_group","z":"","name":"Devices","tab":"107ef2f7.06e5dd","order":2,"disp":true,"width":"6","collapse":false},{"id":"107ef2f7.06e5dd","type":"ui_tab","z":"","name":"Client Data","icon":"dashboard","order":3,"disabled":false,"hidden":false}]

however you will not have the database or query return if you use the pasted flow. The inject node is just representing the client ID I am getting from another function. Then that calls all the devices from that client and it may be 10 or 1. I have the 10 working but not the 1 LOL

8

From ui_dropdown info page: The Options may be configured by inputting msg.options containing an array.

So you must to be sure that you are feeding always an array. Even if it contains only one item. As you have two possible situations - one entry only and multiple of them, you can't feed the data directly from sql output.
You will need to check sql output and prepare the data in the way it covers both situations. May be split the flow into two branches, one for single entry and other for multiple. This way you can handle different data structures and feed result to the dropdown.
We haven't seen the structure of the data for both situations so it is hard to give proper advise but overall logic has been advised by @dceejay

9

Simply I am getting this first return from my database and I am going to populate the dropdown with only the nickname and uuid. With these multiple returns I am using the change node and setting it to options with msg.payload.{nickname:uuid} then deleting the rest of the payload as shown above. Works great!

{"_msgid":"79ac0384.97f40c","topic":"select * from Device WHERE client_id=7;","payload":[{"uuid":"00000000a76452e1","nickname":"Zero Lobby","latitude":37,"longitude":-93,"display_res":"1920x1080","display_type":"LED TV","client_id":7},{"uuid":"00000000247d5c98","nickname":"Mini iPoster","latitude":37,"longitude":-93,"display_res":"128x320","display_type":"P2 SMD","client_id":7},{"uuid":"00000000852dccd8","nickname":"7 Inch Demo","latitude":37,"longitude":-93,"display_res":"800x480","display_type":"Touchscreen Display","client_id":7}]}

and if my database returns only a single item.

{"_msgid":"9b826e89.e0cfa","topic":"select * from Device WHERE client_id=5;","payload":[{"uuid":"00000000d56a2bdd","nickname":"Sean Window","latitude":37,"longitude":-93,"display_res":"1024x768","display_type":"Projector","client_id":5}]}

and I try to use the same change expression setting it to options with msg.payload.{nickname:uuid} it fails. So I if I was to split it when it comes from the database could I do a switch node based on the array count or something like that?

10

Okay folks! Here is what I did. Thank you all for your help. Input is always helpful. I used a switch node based on payload.length to seperate my database returns and then used the two different change nodes depending on if it was a single or multiple input.

[{"id":"a6982238.0b9d6","type":"change","z":"8a7edab5.f9e298","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"msg.payload.[{nickname:uuid}]","tot":"jsonata"},{"t":"delete","p":"payload","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1480,"y":160,"wires":[["316891c3.2c889e"]]},{"id":"fcc6a2e0.f295c","type":"switch","z":"8a7edab5.f9e298","name":"","property":"payload.length","propertyType":"msg","rules":[{"t":"gt","v":"1","vt":"num"},{"t":"lt","v":"2","vt":"str"}],"checkall":"true","repair":false,"outputs":2,"x":1310,"y":140,"wires":[["856c51e2.f706b"],["a6982238.0b9d6"]]},{"id":"856c51e2.f706b","type":"change","z":"8a7edab5.f9e298","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"msg.payload.{nickname:uuid}","tot":"jsonata"},{"t":"delete","p":"payload","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1480,"y":120,"wires":[["316891c3.2c889e"]]},{"id":"316891c3.2c889e","type":"link out","z":"8a7edab5.f9e298","name":"SelectDevice-Out","links":["5a7168db.bb4258"],"x":1615,"y":140,"wires":[]}]

11

Does
select * from Device WHERE client_id="whatever";
actually return an array if there are multiple matching records, but a single value if only one? I would have expected it to return an array containing just one record in the latter case.

12

I agree with @Colin, the query should return an array no matter what... either empty , with one item, or many.... If it doesn't then really I would consider that a bug in the node that needs to be addressed.

I would do it like this (but yes I'm sure some magic jsonata would do a better job.

[{"id":"cfbd24a.6ac83d8","type":"inject","z":"9a4d61c8.a8bc3","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":820,"wires":[["a1e275b0.155a98"]]},{"id":"a1e275b0.155a98","type":"function","z":"9a4d61c8.a8bc3","name":"","func":"msg = {\"_msgid\":\"79ac0384.97f40c\",\"topic\":\"select * from Device WHERE client_id=7;\",\"payload\":[{\"uuid\":\"00000000a76452e1\",\"nickname\":\"Zero Lobby\",\"latitude\":37,\"longitude\":-93,\"display_res\":\"1920x1080\",\"display_type\":\"LED TV\",\"client_id\":7},{\"uuid\":\"00000000247d5c98\",\"nickname\":\"Mini iPoster\",\"latitude\":37,\"longitude\":-93,\"display_res\":\"128x320\",\"display_type\":\"P2 SMD\",\"client_id\":7},{\"uuid\":\"00000000852dccd8\",\"nickname\":\"7 Inch Demo\",\"latitude\":37,\"longitude\":-93,\"display_res\":\"800x480\",\"display_type\":\"Touchscreen Display\",\"client_id\":7}]}\n\n\nreturn msg;","outputs":1,"noerr":0,"x":270,"y":820,"wires":[["82c537.fbd63ac8"]]},{"id":"82c537.fbd63ac8","type":"split","z":"9a4d61c8.a8bc3","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":150,"y":940,"wires":[["527cf82d.56adc8"]]},{"id":"67a2ac29.4cf2c4","type":"function","z":"9a4d61c8.a8bc3","name":"","func":"msg = {\"_msgid\":\"9b826e89.e0cfa\",\"topic\":\"select * from Device WHERE client_id=5;\",\"payload\":[{\"uuid\":\"00000000d56a2bdd\",\"nickname\":\"Sean Window\",\"latitude\":37,\"longitude\":-93,\"display_res\":\"1024x768\",\"display_type\":\"Projector\",\"client_id\":5}]}\n\nreturn msg;","outputs":1,"noerr":0,"x":270,"y":860,"wires":[["82c537.fbd63ac8"]]},{"id":"527cf82d.56adc8","type":"function","z":"9a4d61c8.a8bc3","name":"","func":"var l = msg.payload.nickname;\nvar v = msg.payload.uuid;\nmsg.payload = {};\nmsg.payload[l] = v;\nreturn msg;","outputs":1,"noerr":0,"x":270,"y":940,"wires":[["ec604c0a.4620c"]]},{"id":"31daa51c.0cd1da","type":"inject","z":"9a4d61c8.a8bc3","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":860,"wires":[["67a2ac29.4cf2c4"]]},{"id":"ec604c0a.4620c","type":"join","z":"9a4d61c8.a8bc3","name":"","mode":"auto","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":"false","timeout":"","count":"","reduceRight":false,"x":390,"y":940,"wires":[["82aefcb1.26461"]]},{"id":"82aefcb1.26461","type":"change","z":"9a4d61c8.a8bc3","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"options","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":550,"y":940,"wires":[["ee730f50.80f92","9475ad3d.d6113"]]},{"id":"ee730f50.80f92","type":"debug","z":"9a4d61c8.a8bc3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":750,"y":900,"wires":[]},{"id":"9475ad3d.d6113","type":"ui_dropdown","z":"9a4d61c8.a8bc3","name":"","label":"","tooltip":"","place":"Select option","group":"6be5b344.e6d4dc","order":0,"width":0,"height":0,"passthru":true,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":750,"y":940,"wires":[[]]},{"id":"6be5b344.e6d4dc","type":"ui_group","name":"Group 1","tab":"402eee3d.80541","order":1,"disp":true,"width":6},{"id":"402eee3d.80541","type":"ui_tab","name":"Tab 3","icon":"dashboard","order":3}]
13

To answer the question. Yes my query does return an array either way single or multiple...

I determined its not the return or my formatting before the change node. its actually the change node can not handle and throughput a single entry array with the expression I am using. msg.payload.{nickname:uuid} I am not good enough with coding to know why but it will not handle a single array. YOu have to then giv it msg.payload.[{nickname:uuid}] for it to take the single item array.

14

@dceejay that little flow you provided works as well. Nicely done! Thank you. I think yours is a bit more elegant that my count-and-spit flow... LOL Thanks again for your help. Got it runnin like a champ.

15

You’re welcome

16

@dceejay could I ask one more question? How do I get just the number from this DB query single array to just a number in the payload.

uuid

This one is also eluding me.

17

One of the little buttons that you will see to the right of the line of interest in the debug window when you hover over the line will copy the path to that item into the clipboard. The trick with attribute names with special characters such as space, bracket, +- etc is use the square bracket method rather than the .name method of reference. So it will be something like
msg.payload[0]["COUNT( DISTINCT uuid)"]

1 Like
18

awesome! Thank you sir!!!

19

Actually I am still getting about what I put into it... I need just the 5 as a INT and I am getting...

uuid
uuid.PNG864Ă—331 13.9 KB

20

Use move not set