# Data Base information on gauge graph

**URL:** <https://discourse.nodered.org/t/data-base-information-on-gauge-graph/40791>\
**Category:** General\
**Created:** [10 February 2021 23:48 UTC](https://discourse.nodered.org/t/data-base-information-on-gauge-graph/40791 "2021-02-10T23:48:56Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![tawny\_alexa](https://avatars.discourse-cdn.com/v4/letter/t/c6cbf5/32.png) [@tawny\_alexa](https://discourse.nodered.org/u/tawny_alexa)\
**Post date:** [10 February 2021 23:48 UTC](https://discourse.nodered.org/t/data-base-information-on-gauge-graph/40791/1 "2021-02-10T23:48:56Z")

</div>

First, let me say it's my first time using node-red and reading this forum has helped more than you can imagine. I knew nothing about node-red and now I'm about to finish my IoT project.

I'm managing hand sanitizer dispensers (my mqtt broker sends me a topic with the number of pumps, and then I send that data to mysql and calculate how much hand sanitizer is left).

So I have this table (view) on mysql that shows the last update from each device, with his full capacity and whats left.

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/e/de2da021c8a7e83e27e4534b1e909c8326cae665.png)

I need to translate this information on gauge graphs showing how much is left in each device. But I can't seem to find out how to do it.

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

I only have this

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/9/09dd8efc67c222f6b63de3f270e36c2efe359a2f.png)

on my function I wrote

```auto
msg.topic="SELECT full_capacity, (full_capacity-(nr_pumps*3)) FROM left_capacity;"
return msg;

```

but I know this is wrong, because node-red has no ideia which device to pull information, etc.

Any help would be much appreciated, thanks in advance.

---

<div class="post-metadata">

**Author:** ![Andrei](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/andrei/32/10446_2.png) [@Andrei](https://discourse.nodered.org/u/Andrei)\
**Post date:** [11 February 2021 00:08 UTC](https://discourse.nodered.org/t/data-base-information-on-gauge-graph/40791/2 "2021-02-11T00:08:31Z")

</div>

Have a look on below documentation to understand how to customize the gauge properties.

> <https://github.com/node-red/node-red-dashboard/blob/master/config-fields.md>

---

<div class="post-metadata">

**Author:** ![Andrei](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/andrei/32/10446_2.png) [@Andrei](https://discourse.nodered.org/u/Andrei)\
**Post date:** [11 February 2021 00:12 UTC](https://discourse.nodered.org/t/data-base-information-on-gauge-graph/40791/3 "2021-02-11T00:12:22Z")

</div>

and here a quick example...

```auto
[{"id":"d300cd16.81ac","type":"ui_gauge","z":"4e99d0cd.b60a1","name":"","group":"df1e821.a662d8","order":0,"width":"6","height":"6","gtype":"gage","title":"","label":"","format":"{{value}}","min":0,"max":10,"colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":570,"y":260,"wires":[]},{"id":"c446ea17.8d8588","type":"debug","z":"4e99d0cd.b60a1","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":565,"y":201,"wires":[]},{"id":"b4436546.565038","type":"change","z":"4e99d0cd.b60a1","name":"","rules":[{"t":"set","p":"ui_control","pt":"msg","to":"{\"gtype\":\"donut\",\"min\":0,\"max\":100,\"seg1\":30,\"seg2\":60,\"colors\":[\"blue\",\"orange\",\"lime\"],\"options\":{\"width\":50,\"valueFontFamily\":\"D14C\",\"gaugeWidthScale\":0.5,\"labelFontColor\":\"#d11d3e\",\"label\":\"yourunit\"}}","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":330.999942779541,"y":250.7999963760376,"wires":[["d300cd16.81ac","c446ea17.8d8588"]]},{"id":"bf3beb9e.bd7dd8","type":"inject","z":"4e99d0cd.b60a1","name":"","repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"10","payloadType":"num","x":126.99994277954102,"y":248.79999446868896,"wires":[["b4436546.565038"]]},{"id":"f23d31f2.99384","type":"ui_slider","z":"4e99d0cd.b60a1","name":"","label":"slider","group":"df1e821.a662d8","order":0,"width":0,"height":0,"passthru":true,"topic":"","min":0,"max":"100","step":"10","x":430.99995040893555,"y":348.4000015258789,"wires":[["d300cd16.81ac"]]},{"id":"df1e821.a662d8","type":"ui_group","z":"","name":"LED-3","tab":"ecbd446f.119948","order":3,"disp":false,"width":"6","collapse":false},{"id":"ecbd446f.119948","type":"ui_tab","z":"","name":"LED","icon":"fa-fire","order":18}]

```

---

<div class="post-metadata">

**Author:** ![tawny\_alexa](https://avatars.discourse-cdn.com/v4/letter/t/c6cbf5/32.png) [@tawny\_alexa](https://discourse.nodered.org/u/tawny_alexa)\
**Post date:** [11 February 2021 04:54 UTC](https://discourse.nodered.org/t/data-base-information-on-gauge-graph/40791/5 "2021-02-11T04:54:12Z")

</div>

I've been for hours trying to resolve this but just can't figure this out.

This is my current flow.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/2/b216d3cf1bf7bf9351fdee3d002b2e0aaf245db7.png)

On my function node I have

```auto
var result;
result= "SELECT available From table_left_capacity WHERE device_id=1"
msg.topic=result;
return msg;

```

And this is what I got

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/6/86d1469e776fe755a4930c6e16a188468fb121ab.png)

now I don't know how to send this value to the gauge graph, as it is an object and not a number. I tried Json and even writing a couple functions, but nothing seems to work.

---

<div class="post-metadata">

**Author:** ![leftymuller](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/leftymuller/32/102326_2.png) [@leftymuller](https://discourse.nodered.org/u/leftymuller)\
**Post date:** [11 February 2021 07:12 UTC](https://discourse.nodered.org/t/data-base-information-on-gauge-graph/40791/6 "2021-02-11T07:12:58Z")

</div>

Try this... Not an expert yet but getting there! 😃

Do a function node

```auto
msg.payload = msg.payload[1];
return msg;

```

Then do a change node

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [11 February 2021 07:23 UTC](https://discourse.nodered.org/t/data-base-information-on-gauge-graph/40791/7 "2021-02-11T07:23:01Z")

</div>

> [@tawny\_alexa](#):
>
> And this is what I got
> 
> ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/6/86d1469e776fe755a4930c6e16a188468fb121ab.png)

You're almost there.

To get the value into the chart, you need to get that value into `msg.payload`

Use a change node to copy that value to `msg.payload`

There’s a great page in the docs that will explain how to use the debug panel to find the right path to any data item.

[https://nodered.org/docs/user-guide/messages](https://nodered.org/docs/user-guide/messages)

Pay particular attention to the but about the copy path button that appears under your cursor when you hover over a variable in the debug window.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [11 February 2021 10:13 UTC](https://discourse.nodered.org/t/data-base-information-on-gauge-graph/40791/8 "2021-02-11T10:13:47Z")

</div>

You can tell the gauge to use that value directly. Feed it straight into the gauge node and in the Value Format field put `{{msg.payload[0].available}}`.

---

<div class="post-metadata">

**Author:** ![tawny\_alexa](https://avatars.discourse-cdn.com/v4/letter/t/c6cbf5/32.png) [@tawny\_alexa](https://discourse.nodered.org/u/tawny_alexa)\
**Post date:** [11 February 2021 11:26 UTC](https://discourse.nodered.org/t/data-base-information-on-gauge-graph/40791/9 "2021-02-11T11:26:40Z")

</div>

Thank you so much! I now have a working graph, can't believe I pulled an all nighter and it was so simple.

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/2/e2d5c653d8dc6e71182d3968fb44a44cf2582210.png)

I don't want to push it, but I'd like to ask 2 more questions.

1- Is is possible to automatically do a gauge graph for each device on my data base? Instead of having the "WHERE device\_id=1", "WHERE device\_id=2", etc. Because more devices can be added at any given moment, so that would be the best.

2- As I said before this is a project about hand sanitizer dispensers, and they may vary in capacity, device one may have 1000ml full, device two 2000ml, etc. So is there a way to make this values (that yet again may vary, but updated values are stored at my data base) my max value for the gauge graph, instead of saying I want a pre defined value?

Thank you in advance.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [11 February 2021 13:54 UTC](https://discourse.nodered.org/t/data-base-information-on-gauge-graph/40791/10 "2021-02-11T13:54:35Z")

</div>

I don't know the answer to those questions. But I remember that the question of dynamically created gauges was discussed here recently. Try searching the forum.

---

<div class="post-metadata">

**Author:** ![tawny\_alexa](https://avatars.discourse-cdn.com/v4/letter/t/c6cbf5/32.png) [@tawny\_alexa](https://discourse.nodered.org/u/tawny_alexa)\
**Post date:** [11 February 2021 15:26 UTC](https://discourse.nodered.org/t/data-base-information-on-gauge-graph/40791/11 "2021-02-11T15:26:46Z")

</div>

Thank you very much, as for question 1 I "solved" my problem with a switch node. I have a limited number of devices I can add, but that's enouth for what I'm looking for.

Thank you!

---

<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:** [25 February 2021 15:26 UTC](https://discourse.nodered.org/t/data-base-information-on-gauge-graph/40791/12 "2021-02-25T15:26:48Z")

</div>

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