Data Base information on gauge graph

General
1

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

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
image756×759 31.9 KB

I only have this

image
image728×111 5.42 KB

on my function I wrote

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.

2

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

3

and here a quick example...

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

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

This is my current flow.

image
image734×144 5.73 KB

On my function node I have

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

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.

6

Try this... Not an expert yet but getting there! :smiley:

Do a function node

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

Then do a change node

image
image736×513 15.9 KB

7

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

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.

8

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}}.

9

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

image

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.

10

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.

11

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!

12

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