# UI Dropdown - get result from SQL

**URL:** <https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614>\
**Category:** Dashboard\
**Created:** [5 June 2018 09:46 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614 "2018-06-05T09:46:40Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![grawsom](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/grawsom/32/53070_2.png) [@grawsom](https://discourse.nodered.org/u/grawsom)\
**Post date:** [5 June 2018 09:46 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/1 "2018-06-05T09:46:40Z")

</div>

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”}]

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [5 June 2018 10:13 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/2 "2018-06-05T10:13:47Z")

</div>

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:

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

```

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

 ![Local_Node-RED](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/cbe4dafd179f940fc5732c1e6a8f26b55e6d5db7.png)

```auto
[{"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.

---

<div class="post-metadata">

**Author:** ![grawsom](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/grawsom/32/53070_2.png) [@grawsom](https://discourse.nodered.org/u/grawsom)\
**Post date:** [5 June 2018 12:00 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/3 "2018-06-05T12:00:11Z")

</div>

Great thanks - learning all the time

---

<div class="post-metadata">

**Author:** ![rishanaziz](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rishanaziz](https://discourse.nodered.org/u/rishanaziz)\
**Post date:** [2 August 2018 07:57 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/4 "2018-08-02T07:57:09Z")

</div>

Hi Nick,

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

Database: **MySQL**

Thanks for helping.

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [2 August 2018 08:05 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/5 "2018-08-02T08:05:04Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rishanaziz](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rishanaziz](https://discourse.nodered.org/u/rishanaziz)\
**Post date:** [2 August 2018 09:13 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/6 "2018-08-02T09:13:12Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/4/401e0c4e279d26cd80a51f824f968ee7677a27cf.png)

The following is the JSON of the flow:

```auto
[{"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}]

```

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [2 August 2018 09:44 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/7 "2018-08-02T09:44:02Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rishanaziz](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rishanaziz](https://discourse.nodered.org/u/rishanaziz)\
**Post date:** [2 August 2018 09:59 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/8 "2018-08-02T09:59:09Z")

</div>

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

```auto
[{"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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/e/e55b1453ed4d4d8c3876d613c1e2c294bbddaf6a.png)

---

<div class="post-metadata">

**Author:** ![rishanaziz](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rishanaziz](https://discourse.nodered.org/u/rishanaziz)\
**Post date:** [6 August 2018 01:39 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/9 "2018-08-06T01:39:16Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [6 August 2018 06:43 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/10 "2018-08-06T06:43:48Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![rishanaziz](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rishanaziz](https://discourse.nodered.org/u/rishanaziz)\
**Post date:** [6 August 2018 08:20 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/11 "2018-08-06T08:20:37Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [6 August 2018 08:26 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/12 "2018-08-06T08:26:37Z")

</div>

> [@rishanaziz](#):
>
> But, what I want is to display the label and save the value of the option.

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

> [@grawsom](#):
>
> you can specify both by using an object of “label”:“value” pairs

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

```

---

<div class="post-metadata">

**Author:** ![rishanaziz](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rishanaziz](https://discourse.nodered.org/u/rishanaziz)\
**Post date:** [6 August 2018 08:30 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/13 "2018-08-06T08:30:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [6 August 2018 08:46 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/14 "2018-08-06T08:46:40Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![rishanaziz](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rishanaziz](https://discourse.nodered.org/u/rishanaziz)\
**Post date:** [6 August 2018 09:07 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/15 "2018-08-06T09:07:17Z")

</div>

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

![Capture%20-%20Final](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/a/aa6e92ea43cac80e045109a6b41b6f054e4069ad.png)

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

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [6 August 2018 09:09 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/16 "2018-08-06T09:09:56Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [6 August 2018 21:25 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/17 "2018-08-06T21:25:29Z")

</div>

Based on the screenshot of the data you have in this post - [UI Dropdown - get result from SQL](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/8?u=knolleary)

You can either use a Function node with:

```auto
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}`

---

<div class="post-metadata">

**Author:** ![rishanaziz](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rishanaziz](https://discourse.nodered.org/u/rishanaziz)\
**Post date:** [8 August 2018 02:01 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/18 "2018-08-08T02:01:41Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/4/425225c93ba8e6533bcd0792b615071e0a9ff946.png)

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [8 August 2018 06:12 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/19 "2018-08-08T06:12:46Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![rishanaziz](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rishanaziz](https://discourse.nodered.org/u/rishanaziz)\
**Post date:** [8 August 2018 06:15 UTC](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614/20 "2018-08-08T06:15:33Z")

</div>

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

[Next page](https://discourse.nodered.org/t/ui-dropdown-get-result-from-sql/614.md?page=2)