SQL node to droplist

General
1

Hello :slight_smile:

I'm trying to do a droplist from data of a sql query. I saw on this forum that we must change the data after the SQL node (with the msg.options) but I don't how to do that. The SQL column name come in my droplist and not my data.
If anyone can help me

My droplist:
Capture2

Data from my sql node :
Capture3

My JSON code :

[
    {
        "id": "a363ca24a2531546",
        "type": "tab",
        "label": "Update Ordre",
        "disabled": false,
        "info": ""
    },
    {
        "id": "c8a90569939aa916",
        "type": "ui_dropdown",
        "z": "a363ca24a2531546",
        "name": "",
        "label": "Selectionner Nom Ordre",
        "tooltip": "",
        "place": "",
        "group": "77a6a8681bbac499",
        "order": 1,
        "width": 0,
        "height": 0,
        "passthru": false,
        "multiple": true,
        "options": [
            {
                "label": "",
                "value": "",
                "type": "str"
            }
        ],
        "payload": "",
        "topic": "topic",
        "topicType": "msg",
        "x": 1290,
        "y": 240,
        "wires": [
            []
        ]
    },
    {
        "id": "17c8979555a460a0",
        "type": "mysql",
        "z": "a363ca24a2531546",
        "mydb": "b6ea25d4ebdbea0f",
        "name": "Base de données",
        "x": 670,
        "y": 240,
        "wires": [
            [
                "d2d29ab7b018de03"
            ]
        ]
    },
    {
        "id": "4b5bd92e91346cb3",
        "type": "inject",
        "z": "a363ca24a2531546",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "0.5",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payloadType": "date",
        "x": 180,
        "y": 240,
        "wires": [
            []
        ]
    },
    {
        "id": "bdd120e7d0880d8d",
        "type": "function",
        "z": "a363ca24a2531546",
        "name": "SELECT",
        "func": "var query\n\nquery = \"SELECT ORD_Identifiant FROM ordre ORDER by ORD_Date DESC \"\n\n\nmsg.topic = query\n\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 400,
        "y": 240,
        "wires": [
            [
                "17c8979555a460a0"
            ]
        ]
    },
    {
        "id": "6d16b45ef8f3b2a6",
        "type": "debug",
        "z": "a363ca24a2531546",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "options",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1250,
        "y": 180,
        "wires": []
    },
    {
        "id": "d2d29ab7b018de03",
        "type": "function",
        "z": "a363ca24a2531546",
        "name": "Change data",
        "func": "msg.options = msg.payload\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 950,
        "y": 240,
        "wires": [
            [
                "c8a90569939aa916",
                "6d16b45ef8f3b2a6"
            ]
        ]
    },
    {
        "id": "77a6a8681bbac499",
        "type": "ui_group",
        "name": "Update",
        "tab": "3702ac9912314511",
        "order": 1,
        "disp": false,
        "width": "12",
        "collapse": false
    },
    {
        "id": "b6ea25d4ebdbea0f",
        "type": "MySQLdatabase",
        "name": "",
        "host": "127.0.0.1",
        "port": "3306",
        "db": "ordresmartfactory",
        "tz": "",
        "charset": "UTF8"
    },
    {
        "id": "3702ac9912314511",
        "type": "ui_tab",
        "name": "Update",
        "icon": "dashboard",
        "disabled": false,
        "hidden": false
    }
]
2

You supplied no data, and no one has access to you sql return , so i made my own for this example
hope it helps

[{"id":"4b5bd92e91346cb3","type":"inject","z":"a363ca24a2531546","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"ORD_identifiant\":\"blah1\"},{\"ORD_identifiant\":\"blah2\"},{\"ORD_identifiant\":\"blah3\"}]","payloadType":"json","x":410,"y":240,"wires":[["e6ee22a6.b4bea8"]]},{"id":"e6ee22a6.b4bea8","type":"change","z":"a363ca24a2531546","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"$$.payload.ORD_identifiant","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":660,"y":260,"wires":[["c8a90569939aa916","6d16b45ef8f3b2a6"]]},{"id":"c8a90569939aa916","type":"ui_dropdown","z":"a363ca24a2531546","name":"","label":"Selectionner Nom Ordre","tooltip":"","place":"","group":"77a6a8681bbac499","order":1,"width":0,"height":0,"passthru":false,"multiple":true,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"topic","topicType":"msg","x":970,"y":260,"wires":[[]]},{"id":"6d16b45ef8f3b2a6","type":"debug","z":"a363ca24a2531546","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"options","targetType":"msg","statusVal":"","statusType":"auto","x":920,"y":180,"wires":[]},{"id":"77a6a8681bbac499","type":"ui_group","name":"Update","tab":"3702ac9912314511","order":1,"disp":false,"width":"12","collapse":false},{"id":"3702ac9912314511","type":"ui_tab","name":"Update","icon":"dashboard","disabled":false,"hidden":false}]
1 Like
3

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