# Push MS SQL query data into dashboard list node

**URL:** <https://discourse.nodered.org/t/push-ms-sql-query-data-into-dashboard-list-node/77815>\
**Category:** Dashboard\
**Tags:** node-red-dashboard, database, dashboard\
**Created:** [22 April 2023 11:25 UTC](https://discourse.nodered.org/t/push-ms-sql-query-data-into-dashboard-list-node/77815 "2023-04-22T11:25:59Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![ardukus](https://avatars.discourse-cdn.com/v4/letter/a/779978/32.png) [@ardukus](https://discourse.nodered.org/u/ardukus)\
**Post date:** [22 April 2023 11:25 UTC](https://discourse.nodered.org/t/push-ms-sql-query-data-into-dashboard-list-node/77815/1 "2023-04-22T11:25:59Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/0/d09b00b2cb160f83e867d67bb6721e1ef3fe600c.png)

I use a template node which holds the query

```auto
SELECT typ, designation FROM t_bd_planes

```

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

```auto
[{"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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/8/68659fd7e979edd6ee2f7f19929576fd20af9d1e.png)

Json data:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/c/5c37e209ffb83db2188947e2a87d2dfdf8f63053.png)

The list then gets displayed on my dashboard  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/8/e8f8838c7972feb7eb78276233f96fbe039f4a2a.png)

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!

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [22 April 2023 11:44 UTC](https://discourse.nodered.org/t/push-ms-sql-query-data-into-dashboard-list-node/77815/2 "2023-04-22T11:44:42Z")

</div>

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

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

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

```auto
$$.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.

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

```

---

<div class="post-metadata">

**Author:** ![ardukus](https://avatars.discourse-cdn.com/v4/letter/a/779978/32.png) [@ardukus](https://discourse.nodered.org/u/ardukus)\
**Post date:** [22 April 2023 14:46 UTC](https://discourse.nodered.org/t/push-ms-sql-query-data-into-dashboard-list-node/77815/3 "2023-04-22T14:46:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [6 May 2023 14:46 UTC](https://discourse.nodered.org/t/push-ms-sql-query-data-into-dashboard-list-node/77815/4 "2023-05-06T14:46:47Z")

</div>

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