Ui_dropdown - "ERR: Invalid Options"

Dashboard
1

When the options only have one field the ui-dropdown reports "ERR: Invalid Options".

{"topic":"SELECT ttngateway FROM ttngateway;","_msgid":"5e0898bcd4c96cc8","payload":[{"ttngateway":"tech5-alverstone-02-dbn"}],"options":"tech5-alverstone-02-dbn"}

Yes I do understand that most people will have multiple options they require to select from, but I am using it dynamical. It is a value in field from a db it gives you the option to select from and over time it changes and become a multiple values.

{"topic":"SELECT application FROM tapplication;","_msgid":"02681087b0850ea5","payload":[{"application":"micro-tracker-abeeway"},{"application":"smart-badge"}],"options":["micro-tracker-abeeway","smart-badge"]}

Any one have a work around for this?

node-red-dashboard
V3.1.2

2

Both those options arrays work for me, Are you sure when you receive the error that msg.options is an array. How are you setting msg.options dynamically?
It will throw that error is it does not receive an array.

3

Those are the arrays that gets injected into the ui_dropdown node - copied straight from the debug node checking what the payload were.

4

You can see that the options in the first one isn't an array

image
image742Ă—108 8.19 KB

5

you need to enter the array in msg.option. if you set that json in an inject it will be msg.payload.option. if it is in msg.option, then as i said how do you generate the msg.option array.

6

I know, but looking if there is some cleaver workaround when there is only one object?

7

If you don't say how you generate the msg.options, how can we give you a workaround.

8

It's a call to a db.

[
    {
        "id": "5c9a8bc72c31fb9b",
        "type": "mysql",
        "z": "6151ad3d1b9f5d2b",
        "mydb": "e15aca1763c788ca",
        "name": "",
        "x": 1290,
        "y": 80,
        "wires": [
            [
                "b50966c9f3402ebb"
            ]
        ]
    },
    {
        "id": "781c6dcce21d66c0",
        "type": "ui_dropdown",
        "z": "6151ad3d1b9f5d2b",
        "name": "",
        "label": "Device",
        "tooltip": "",
        "place": "Select option",
        "group": "f5110ba5bc3bd478",
        "order": 1,
        "width": 12,
        "height": 1,
        "passthru": false,
        "multiple": false,
        "options": [
            {
                "label": "",
                "value": "",
                "type": "str"
            }
        ],
        "payload": "",
        "topic": "topic",
        "topicType": "msg",
        "className": "",
        "x": 1620,
        "y": 80,
        "wires": [
            [
                "1e4a3d0df17758a9"
            ]
        ]
    },
    {
        "id": "b50966c9f3402ebb",
        "type": "change",
        "z": "6151ad3d1b9f5d2b",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "options",
                "pt": "msg",
                "to": "$.payload.device",
                "tot": "jsonata"
            },
            {
                "t": "delete",
                "p": "payload",
                "pt": "msg"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 1440,
        "y": 80,
        "wires": [
            [
                "781c6dcce21d66c0"
            ]
        ]
    },
    {
        "id": "0672735324f36a3a",
        "type": "mysql",
        "z": "6151ad3d1b9f5d2b",
        "mydb": "e15aca1763c788ca",
        "name": "",
        "x": 1290,
        "y": 180,
        "wires": [
            [
                "3ab056e3333b020d"
            ]
        ]
    },
    {
        "id": "a6ed8e2b4f54998e",
        "type": "ui_dropdown",
        "z": "6151ad3d1b9f5d2b",
        "name": "",
        "label": "Application",
        "tooltip": "",
        "place": "Select option",
        "group": "e9693e324fca96f2",
        "order": 1,
        "width": 12,
        "height": 1,
        "passthru": false,
        "multiple": false,
        "options": [
            {
                "label": "",
                "value": "",
                "type": "str"
            }
        ],
        "payload": "",
        "topic": "topic",
        "topicType": "msg",
        "className": "",
        "x": 1630,
        "y": 180,
        "wires": [
            [
                "f5cbc76a106f2eac"
            ]
        ]
    },
    {
        "id": "3ab056e3333b020d",
        "type": "change",
        "z": "6151ad3d1b9f5d2b",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "options",
                "pt": "msg",
                "to": "$.payload.application",
                "tot": "jsonata"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 1440,
        "y": 180,
        "wires": [
            [
                "a6ed8e2b4f54998e"
            ]
        ]
    },
    {
        "id": "fe83e3bd0a40a1c1",
        "type": "mysql",
        "z": "6151ad3d1b9f5d2b",
        "mydb": "e15aca1763c788ca",
        "name": "",
        "x": 1290,
        "y": 280,
        "wires": [
            [
                "87b661f1951127a8"
            ]
        ]
    },
    {
        "id": "c233f2bf99bad8f0",
        "type": "ui_dropdown",
        "z": "6151ad3d1b9f5d2b",
        "name": "",
        "label": "Gateway",
        "tooltip": "",
        "place": "Select option",
        "group": "4aed5d542edfe9c6",
        "order": 1,
        "width": 12,
        "height": 1,
        "passthru": false,
        "multiple": false,
        "options": [
            {
                "label": "",
                "value": "",
                "type": "str"
            }
        ],
        "payload": "",
        "topic": "topic",
        "topicType": "msg",
        "className": "",
        "x": 1620,
        "y": 280,
        "wires": [
            [
                "0b69920c10efdeed"
            ]
        ]
    },
    {
        "id": "87b661f1951127a8",
        "type": "change",
        "z": "6151ad3d1b9f5d2b",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "options",
                "pt": "msg",
                "to": "$.payload.ttngateway",
                "tot": "jsonata"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 1440,
        "y": 280,
        "wires": [
            [
                "c233f2bf99bad8f0"
            ]
        ]
    },
    {
        "id": "1e4a3d0df17758a9",
        "type": "change",
        "z": "6151ad3d1b9f5d2b",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "device",
                "pt": "flow",
                "to": "payload",
                "tot": "msg"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 1780,
        "y": 80,
        "wires": [
            []
        ]
    },
    {
        "id": "f5cbc76a106f2eac",
        "type": "change",
        "z": "6151ad3d1b9f5d2b",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "application",
                "pt": "flow",
                "to": "payload",
                "tot": "msg"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 1810,
        "y": 180,
        "wires": [
            []
        ]
    },
    {
        "id": "0b69920c10efdeed",
        "type": "change",
        "z": "6151ad3d1b9f5d2b",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "ttngateway",
                "pt": "flow",
                "to": "payload",
                "tot": "msg"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 1790,
        "y": 280,
        "wires": [
            []
        ]
    },
    {
        "id": "4e05824c8e10af76",
        "type": "comment",
        "z": "6151ad3d1b9f5d2b",
        "name": "Dashboard - Device",
        "info": "",
        "x": 1330,
        "y": 40,
        "wires": []
    },
    {
        "id": "fbf648ed3bc62f38",
        "type": "comment",
        "z": "6151ad3d1b9f5d2b",
        "name": "Dashboard - Application",
        "info": "",
        "x": 1340,
        "y": 140,
        "wires": []
    },
    {
        "id": "343d3d97510588b0",
        "type": "comment",
        "z": "6151ad3d1b9f5d2b",
        "name": "Dashboard - Gateway",
        "info": "",
        "x": 1340,
        "y": 240,
        "wires": []
    },
    {
        "id": "48b18d4fa64343b5",
        "type": "function",
        "z": "6151ad3d1b9f5d2b",
        "name": "",
        "func": "flow.set('db', msg.payload)\n\n\nmsg1 = {topic :\"SELECT device FROM tdevice;\"}\n\nmsg2 = {topic :\"SELECT application FROM tapplication;\"}\n\nmsg3 = {topic :\"SELECT ttngateway FROM ttngateway;\"}\n\n\n\nreturn [msg1, msg2, msg3];",
        "outputs": 3,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 1120,
        "y": 180,
        "wires": [
            [
                "5c9a8bc72c31fb9b"
            ],
            [
                "0672735324f36a3a"
            ],
            [
                "fe83e3bd0a40a1c1"
            ]
        ]
    },
    {
        "id": "e15aca1763c788ca",
        "type": "MySQLdatabase",
        "name": "",
        "host": "dbsoiot.civilwbkm1wy.eu-west-1.rds.amazonaws.com",
        "port": "3306",
        "db": "soiot",
        "tz": "",
        "charset": "UTF8"
    },
    {
        "id": "f5110ba5bc3bd478",
        "type": "ui_group",
        "name": "Device",
        "tab": "2a4dc6a148102b1a",
        "order": 1,
        "disp": true,
        "width": "12",
        "collapse": false,
        "className": ""
    },
    {
        "id": "e9693e324fca96f2",
        "type": "ui_group",
        "name": "Application;",
        "tab": "2a4dc6a148102b1a",
        "order": 2,
        "disp": true,
        "width": "12",
        "collapse": false,
        "className": ""
    },
    {
        "id": "4aed5d542edfe9c6",
        "type": "ui_group",
        "name": "Gateway",
        "tab": "2a4dc6a148102b1a",
        "order": 3,
        "disp": true,
        "width": "12",
        "collapse": false,
        "className": ""
    },
    {
        "id": "2a4dc6a148102b1a",
        "type": "ui_tab",
        "name": "Node Map",
        "icon": "dashboard",
        "order": 5,
        "disabled": false,
        "hidden": false
    }
]
9

which version of the mysql node ?

10

use [ ] around your JSONata expression, it will force an array when only one item is returned by the query
e.g.

[{"id":"5c9a8bc72c31fb9b","type":"mysql","z":"c791cbc0.84f648","mydb":"e15aca1763c788ca","name":"","x":246,"y":455.99998474121094,"wires":[["b50966c9f3402ebb"]]},{"id":"b50966c9f3402ebb","type":"change","z":"c791cbc0.84f648","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"[$.payload.device]","tot":"jsonata"},{"t":"delete","p":"payload","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":396,"y":455.99998474121094,"wires":[["781c6dcce21d66c0"]]},{"id":"48b18d4fa64343b5","type":"function","z":"c791cbc0.84f648","name":"","func":"flow.set('db', msg.payload)\n\n\nmsg1 = {topic :\"SELECT device FROM tdevice;\"}\n\nmsg2 = {topic :\"SELECT application FROM tapplication;\"}\n\nmsg3 = {topic :\"SELECT ttngateway FROM ttngateway;\"}\n\n\n\nreturn [msg1, msg2, msg3];","outputs":3,"noerr":0,"initialize":"","finalize":"","x":76,"y":555.9999847412109,"wires":[["5c9a8bc72c31fb9b"],["0672735324f36a3a"],["fe83e3bd0a40a1c1"]]},{"id":"781c6dcce21d66c0","type":"ui_dropdown","z":"c791cbc0.84f648","name":"","label":"Device","tooltip":"","place":"Select option","group":"f5110ba5bc3bd478","order":1,"width":12,"height":1,"passthru":false,"multiple":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"topic","topicType":"msg","x":576,"y":455.99998474121094,"wires":[["1e4a3d0df17758a9"]]},{"id":"0672735324f36a3a","type":"mysql","z":"c791cbc0.84f648","mydb":"e15aca1763c788ca","name":"","x":246,"y":555.9999847412109,"wires":[["3ab056e3333b020d"]]},{"id":"fe83e3bd0a40a1c1","type":"mysql","z":"c791cbc0.84f648","mydb":"e15aca1763c788ca","name":"","x":246,"y":655.9999847412109,"wires":[["87b661f1951127a8"]]},{"id":"1e4a3d0df17758a9","type":"change","z":"c791cbc0.84f648","name":"","rules":[{"t":"set","p":"device","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":736,"y":455.99998474121094,"wires":[[]]},{"id":"3ab056e3333b020d","type":"change","z":"c791cbc0.84f648","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"[$.payload.application]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":386,"y":535.9999847412109,"wires":[["a6ed8e2b4f54998e"]]},{"id":"87b661f1951127a8","type":"change","z":"c791cbc0.84f648","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"[$.payload.ttngateway]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":396,"y":655.9999847412109,"wires":[["c233f2bf99bad8f0"]]},{"id":"a6ed8e2b4f54998e","type":"ui_dropdown","z":"c791cbc0.84f648","name":"","label":"Application","tooltip":"","place":"Select option","group":"e9693e324fca96f2","order":1,"width":12,"height":1,"passthru":false,"multiple":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"topic","topicType":"msg","x":586,"y":555.9999847412109,"wires":[["f5cbc76a106f2eac"]]},{"id":"c233f2bf99bad8f0","type":"ui_dropdown","z":"c791cbc0.84f648","name":"","label":"Gateway","tooltip":"","place":"Select option","group":"4aed5d542edfe9c6","order":1,"width":12,"height":1,"passthru":false,"multiple":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"topic","topicType":"msg","x":576,"y":655.9999847412109,"wires":[["0b69920c10efdeed"]]},{"id":"f5cbc76a106f2eac","type":"change","z":"c791cbc0.84f648","name":"","rules":[{"t":"set","p":"application","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":766,"y":555.9999847412109,"wires":[[]]},{"id":"0b69920c10efdeed","type":"change","z":"c791cbc0.84f648","name":"","rules":[{"t":"set","p":"ttngateway","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":746,"y":655.9999847412109,"wires":[[]]},{"id":"4e05824c8e10af76","type":"comment","z":"c791cbc0.84f648","name":"Dashboard - Device","info":"","x":280,"y":420,"wires":[]},{"id":"fbf648ed3bc62f38","type":"comment","z":"c791cbc0.84f648","name":"Dashboard - Application","info":"","x":290,"y":520,"wires":[]},{"id":"343d3d97510588b0","type":"comment","z":"c791cbc0.84f648","name":"Dashboard - Gateway","info":"","x":290,"y":620,"wires":[]},{"id":"f5110ba5bc3bd478","type":"ui_group","name":"Device","tab":"2a4dc6a148102b1a","order":1,"disp":true,"width":"12","collapse":false},{"id":"e9693e324fca96f2","type":"ui_group","name":"Application;","tab":"2a4dc6a148102b1a","order":2,"disp":true,"width":"12","collapse":false},{"id":"4aed5d542edfe9c6","type":"ui_group","name":"Gateway","tab":"2a4dc6a148102b1a","order":3,"disp":true,"width":"12","collapse":false},{"id":"2a4dc6a148102b1a","type":"ui_tab","name":"Node Map","icon":"dashboard","order":5,"disabled":false,"hidden":false}]

[$.payload.ttngateway]
1 Like
11

node-red-node-mysql

0.3.0

12

Thank @E1cid you that work 100%

1 Like
13

If you have an environment suitable for test - I would be most grateful if you could test node-red-node-mysql@next for me... to see if it gives the same result for the single item.... I must admit I thought the queries would always return an array.

14

dceejay there is no issue with the returned array from the sql query, it is always an array as you thought. The issue arises as JSONata will return a single value as a none array, unless you tell it to always return an array.

1 Like
15

Aha. Phew. Thanks

16

What V0.3.0?

That is what I am running..

image

17

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.