Push MS SQL query data into dashboard list node

Dashboard
, ,
1

Hi all,
currently I have no idea how to feed a list node on my dashboard.

What I am trying to achieve:
I do have a ms sql database wich gives me simple basic data (two coloumns).
The dashboard should display this data in a list node.

In my opinion following steps are needed:

1. fetch data from db:

image
image761Ă—105 5.38 KB

I use a template node which holds the query

SELECT typ, designation FROM t_bd_planes

This works quite well and I get the data as payload object
Full message:

[{"typ":"D-5909","designation":"Arcus"},{"typ":"D-4002","designation":"Ventus 2cx"},{"typ":"OE-9513","designation":"DG-1001M"},{"typ":"D-5809","designation":"Duo Discus"},{"typ":"OE-5663","designation":"FOX"},{"typ":"OE-5280","designation":"ASK 21"},{"typ":"OE-5664","designation":"LS8"},{"typ":"D-6521","designation":"LS1"},{"typ":"D-3578","designation":"LS4"},{"typ":"OE-5338","designation":"LS4"},{"typ":"OE-5563","designation":"LS4"},{"typ":"OE-5211","designation":"ASK 13"},{"typ":"D-3987","designation":"ASK 13"},{"typ":"D-8525","designation":"Ka 8"},{"typ":"OE-5287","designation":"Ka 8"},{"typ":"OE-5295","designation":"Ka 8"},{"typ":"OE-0772","designation":"Ka 8"},{"typ":"D-KNAM","designation":"SF-25C Falke"},{"typ":"OE-ARD","designation":"Husky"}]

2. Feed the list node
What my problem no is, how do I convert the payload to an readable list node array.

To split the problem I tried to feed the node manually by injecting data in json format.
Therefore i set the injection mode to json
image

Json data:

image
image938Ă—608 25.5 KB

The list then gets displayed on my dashboard
image

Is it possible to push the data directly from the query part to the list node by converting the payload object to a json object?

THX for guiding hints!

2

You could return the query results as title and description
e.g.

SELECT typ AS title, designation AS description FROM t_bd_planes

Or you can convert them in node-red using a change node and JSONata
e.g.

[{"id":"aa8dbc4e579d61b8","type":"inject","z":"bf622e623503117a","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"typ\":\"D-5909\",\"designation\":\"Arcus\"},{\"typ\":\"D-4002\",\"designation\":\"Ventus 2cx\"},{\"typ\":\"OE-9513\",\"designation\":\"DG-1001M\"},{\"typ\":\"D-5809\",\"designation\":\"Duo Discus\"},{\"typ\":\"OE-5663\",\"designation\":\"FOX\"},{\"typ\":\"OE-5280\",\"designation\":\"ASK 21\"},{\"typ\":\"OE-5664\",\"designation\":\"LS8\"},{\"typ\":\"D-6521\",\"designation\":\"LS1\"},{\"typ\":\"D-3578\",\"designation\":\"LS4\"},{\"typ\":\"OE-5338\",\"designation\":\"LS4\"},{\"typ\":\"OE-5563\",\"designation\":\"LS4\"},{\"typ\":\"OE-5211\",\"designation\":\"ASK 13\"},{\"typ\":\"D-3987\",\"designation\":\"ASK 13\"},{\"typ\":\"D-8525\",\"designation\":\"Ka 8\"},{\"typ\":\"OE-5287\",\"designation\":\"Ka 8\"},{\"typ\":\"OE-5295\",\"designation\":\"Ka 8\"},{\"typ\":\"OE-0772\",\"designation\":\"Ka 8\"},{\"typ\":\"D-KNAM\",\"designation\":\"SF-25C Falke\"},{\"typ\":\"OE-ARD\",\"designation\":\"Husky\"}]","payloadType":"json","x":210,"y":500,"wires":[["a4c9f3544e8dda11"]]},{"id":"a4c9f3544e8dda11","type":"change","z":"bf622e623503117a","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.{\"title\": $.typ, \"description\":$.designation, \"icon\": \"icon link\"}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":280,"y":560,"wires":[["eae82cbf44ee5289","8d3d9027c15d88fc"]]},{"id":"eae82cbf44ee5289","type":"ui_list","z":"bf622e623503117a","group":"2d4fe667.28f8ba","name":"","order":27,"width":0,"height":0,"lineType":"three","actionType":"none","allowHTML":false,"outputs":0,"topic":"","x":370,"y":500,"wires":[]},{"id":"8d3d9027c15d88fc","type":"debug","z":"bf622e623503117a","name":"debug 264","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":490,"y":560,"wires":[]},{"id":"2d4fe667.28f8ba","type":"ui_group","name":"demo","tab":"1caa8458.b17814","order":2,"disp":true,"width":"12","collapse":false,"className":""},{"id":"1caa8458.b17814","type":"ui_tab","name":"Demo","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

Expression

$$.payload.{
   "title": $.typ,
   "description":$.designation,
   "icon": "icon link"
}

And here is both with html <b> and adding icon to db return with title and description.

[{"id":"aa8dbc4e579d61b8","type":"inject","z":"bf622e623503117a","name":"inject db return typ, designation","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"typ\":\"D-5909\",\"designation\":\"Arcus\"},{\"typ\":\"D-4002\",\"designation\":\"Ventus 2cx\"},{\"typ\":\"OE-9513\",\"designation\":\"DG-1001M\"},{\"typ\":\"D-5809\",\"designation\":\"Duo Discus\"},{\"typ\":\"OE-5663\",\"designation\":\"FOX\"},{\"typ\":\"OE-5280\",\"designation\":\"ASK 21\"},{\"typ\":\"OE-5664\",\"designation\":\"LS8\"},{\"typ\":\"D-6521\",\"designation\":\"LS1\"},{\"typ\":\"D-3578\",\"designation\":\"LS4\"},{\"typ\":\"OE-5338\",\"designation\":\"LS4\"},{\"typ\":\"OE-5563\",\"designation\":\"LS4\"},{\"typ\":\"OE-5211\",\"designation\":\"ASK 13\"},{\"typ\":\"D-3987\",\"designation\":\"ASK 13\"},{\"typ\":\"D-8525\",\"designation\":\"Ka 8\"},{\"typ\":\"OE-5287\",\"designation\":\"Ka 8\"},{\"typ\":\"OE-5295\",\"designation\":\"Ka 8\"},{\"typ\":\"OE-0772\",\"designation\":\"Ka 8\"},{\"typ\":\"D-KNAM\",\"designation\":\"SF-25C Falke\"},{\"typ\":\"OE-ARD\",\"designation\":\"Husky\"}]","payloadType":"json","x":270,"y":500,"wires":[["a4c9f3544e8dda11"]]},{"id":"a4c9f3544e8dda11","type":"change","z":"bf622e623503117a","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.{\t   \"title\": \"<b>\" & $.typ & \"</b>\",\t   \"description\":$.designation,\t   \"icon\": \"icon link\"\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":520,"y":520,"wires":[["eae82cbf44ee5289","8d3d9027c15d88fc"]]},{"id":"eae82cbf44ee5289","type":"ui_list","z":"bf622e623503117a","group":"2d4fe667.28f8ba","name":"","order":27,"width":0,"height":0,"lineType":"three","actionType":"none","allowHTML":true,"outputs":0,"topic":"","x":710,"y":560,"wires":[]},{"id":"8d3d9027c15d88fc","type":"debug","z":"bf622e623503117a","name":"debug 264","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":730,"y":520,"wires":[]},{"id":"e9a87236dcb6fb1d","type":"change","z":"bf622e623503117a","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload ~> |$|{\"title\": \"<b>\" & $.title & \"</b>\", \"icon\": \"iconlink\"}|","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":500,"y":580,"wires":[["eae82cbf44ee5289"]]},{"id":"eaa1e35511e7cc40","type":"inject","z":"bf622e623503117a","name":"inject db return title, description","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"title\":\"D-5909\",\"description\":\"Arcus\",\"icon\":\"icon link\"},{\"title\":\"D-4002\",\"description\":\"Ventus 2cx\",\"icon\":\"icon link\"},{\"title\":\"OE-9513\",\"description\":\"DG-1001M\",\"icon\":\"icon link\"},{\"title\":\"D-5809\",\"description\":\"Duo Discus\",\"icon\":\"icon link\"},{\"title\":\"OE-5663\",\"description\":\"FOX\",\"icon\":\"icon link\"},{\"title\":\"OE-5280\",\"description\":\"ASK 21\",\"icon\":\"icon link\"},{\"title\":\"OE-5664\",\"description\":\"LS8\",\"icon\":\"icon link\"},{\"title\":\"D-6521\",\"description\":\"LS1\",\"icon\":\"icon link\"},{\"title\":\"D-3578\",\"description\":\"LS4\",\"icon\":\"icon link\"},{\"title\":\"OE-5338\",\"description\":\"LS4\",\"icon\":\"icon link\"},{\"title\":\"OE-5563\",\"description\":\"LS4\",\"icon\":\"icon link\"},{\"title\":\"OE-5211\",\"description\":\"ASK 13\",\"icon\":\"icon link\"},{\"title\":\"D-3987\",\"description\":\"ASK 13\",\"icon\":\"icon link\"},{\"title\":\"D-8525\",\"description\":\"Ka 8\",\"icon\":\"icon link\"},{\"title\":\"OE-5287\",\"description\":\"Ka 8\",\"icon\":\"icon link\"},{\"title\":\"OE-5295\",\"description\":\"Ka 8\",\"icon\":\"icon link\"},{\"title\":\"OE-0772\",\"description\":\"Ka 8\",\"icon\":\"icon link\"},{\"title\":\"D-KNAM\",\"description\":\"SF-25C Falke\",\"icon\":\"icon link\"},{\"title\":\"OE-ARD\",\"description\":\"Husky\",\"icon\":\"icon link\"}]","payloadType":"json","x":270,"y":580,"wires":[["e9a87236dcb6fb1d"]]},{"id":"2d4fe667.28f8ba","type":"ui_group","name":"demo","tab":"1caa8458.b17814","order":2,"disp":true,"width":"12","collapse":false,"className":""},{"id":"1caa8458.b17814","type":"ui_tab","name":"Demo","icon":"dashboard","order":1,"disabled":false,"hidden":false}]
1 Like
3

Thank you I edited the query as you suggested and it worked on first try.
THX have a good one!

4

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