Clear Level Graph

[tawny_alexa]

Hi, I created some text nodes and level graphs to display my data from mysql. It works fine and I got the result I wanted:

but when I delete the hand sanitizer dispenser 1 (on my data base) this is what I get:

The values on the text nodes clear, but my graph keeps displaying the last value he got, instead of 0ml. Is there something I can do?

[E1cid]

You could check incoming message for values of the display properties, and if null or undifined set payload to 0. Depending on how your flow works.

[tawny_alexa]

Hi thank you very much for you answer

I've already tried the following code on my template node

if (msg.payload.dispenser == undifined)
{
    msg.payload=0;
    return msg;
}
else{
Select available From left_capacity where dispenser=1;
}

but I have an error. My value format for my graph is {{msg.payload[0].available}}

[E1cid]

What are the label properties ml and %?
there the ones you need to check are null or undefined.

[hotNipi]

you have misspelled the undefined

[tawny_alexa]

Here's my flow

[{"id":"a17825bb.8450c8","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"5de27696.ff4898","type":"mysql","z":"a17825bb.8450c8","mydb":"b1fe5a2d.d1e8a8","name":"DB","x":530,"y":420,"wires":[["6ed4ebf9.258834"]]},{"id":"36704f96.9e557","type":"inject","z":"a17825bb.8450c8","name":"query","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"3","crontab":"","once":true,"onceDelay":"0.1","topic":"","payload":"","payloadType":"date","x":150,"y":420,"wires":[["a4eec39f.54aff"]]},{"id":"a4eec39f.54aff","type":"template","z":"a17825bb.8450c8","name":"Format query 1","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"Select dispenser From left_capacity;","output":"str","x":360,"y":420,"wires":[["5de27696.ff4898"]]},{"id":"6ed4ebf9.258834","type":"switch","z":"a17825bb.8450c8","name":"","property":"payload.dispenser","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"msg"},{"t":"eq","v":"2","vt":"msg"}],"checkall":"true","repair":false,"outputs":2,"x":730,"y":420,"wires":[["136fa0cc.3aa51f"],["7ba6e969.e9d258"]]},{"id":"3c0029ed.38c8e6","type":"ui_gauge","z":"a17825bb.8450c8","name":"Available Hand Sanitizer Dispenser 1","group":"19853017.10f43","order":3,"width":"6","height":"6","gtype":"wave","title":"Available Hand Sanitizer","label":"ml","format":"{{msg.payload[0].available}}","min":0,"max":"3000","colors":["#00b500","#e6e600","#ca3838"],"seg1":"500","seg2":"1500","x":1290,"y":240,"wires":[]},{"id":"4ac8b830.edddc8","type":"mysql","z":"a17825bb.8450c8","mydb":"b1fe5a2d.d1e8a8","name":"DB","x":1050,"y":240,"wires":[["3c0029ed.38c8e6"]]},{"id":"136fa0cc.3aa51f","type":"template","z":"a17825bb.8450c8","name":"Format query 1","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"Select available From left_capacity Where dispenser=1;","output":"str","x":880,"y":240,"wires":[["4ac8b830.edddc8"]]},{"id":"55c59219.6f86cc","type":"debug","z":"a17825bb.8450c8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1250,"y":360,"wires":[]},{"id":"fa0a2b79.466ba8","type":"ui_gauge","z":"a17825bb.8450c8","name":"Available Hand Sanitizer Dispenser 2","group":"9889b5c8.a71e68","order":3,"width":"6","height":"6","gtype":"wave","title":"Available Hand Sanitizer","label":"ml","format":"{{msg.payload[0].available}}","min":0,"max":"3000","colors":["#00b500","#e6e600","#ca3838"],"seg1":"500","seg2":"1500","x":1350,"y":400,"wires":[]},{"id":"351e1936.6ed0f6","type":"mysql","z":"a17825bb.8450c8","mydb":"b1fe5a2d.d1e8a8","name":"DB","x":1110,"y":420,"wires":[["fa0a2b79.466ba8","55c59219.6f86cc"]]},{"id":"7ba6e969.e9d258","type":"template","z":"a17825bb.8450c8","name":"Format query 1","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"Select available From left_capacity Where dispenser=2;","output":"str","x":960,"y":420,"wires":[["351e1936.6ed0f6"]]},{"id":"b1fe5a2d.d1e8a8","type":"MySQLdatabase","name":"projecto","host":"127.0.0.1","port":"3306","db":"projecto","tz":"nodered","charset":""},{"id":"19853017.10f43","type":"ui_group","name":"Dispensador 2","tab":"30837847.88be68","order":2,"disp":true,"width":"6","collapse":false},{"id":"9889b5c8.a71e68","type":"ui_group","name":"Dispensador 3","tab":"30837847.88be68","order":3,"disp":true,"width":"6","collapse":false},{"id":"30837847.88be68","type":"ui_tab","name":"Gráficos","icon":"dashboard","order":9,"disabled":false,"hidden":true}]

my code on my data base for this is:

CREATE VIEW left_capacity
AS
SELECT max(time_stamp) AS calendar,
       id_dispenser AS dispenser,
       full_capacity AS capacity,
       greatest(full_capacity - nr_pumps * 3, 0) AS available
       FROM records r
            INNER JOIN dispenser d
                       ON d.id_disp = r.id_dispenser
       GROUP by id_dispenser;


create view percentage_left_capacity AS
SELECT dispenser,
((left_capacity.available/left_capacity.capacity)*100) AS percentage
From left_capacity;

[tawny_alexa]

Thanks, but it's just misspelled in here, when I copied and re-writed everything because my original code is not in english. I just checked and I have it right on the original one

[E1cid]

Can we see a full debug message after the sql query before the gauge node of the deleted database entry?

[tawny_alexa]

After deleting dispenser 1 I get this on my debug message on the last sql query

[Colin]

Which node is that coming out of? I don't think that is coming from the database node.
What is coming from the database node?

[tawny_alexa]

sorry, my bad, out of the data base

I have this

[E1cid]

simple fix catch the error caused by empty db return
eg.

[{"id":"bfc7d213.f19168","type":"change","z":"5a245aa1.510164","name":"","rules":[{"t":"set","p":"payload[0].available","pt":"msg","to":"$count(payload) < 1 ? 0 : payload[0].available","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":310,"y":940,"wires":[["ebc37340.ddfd9","af19c8b9.373f08"]]},{"id":"6b2ccd77.4da4cc","type":"function","z":"5a245aa1.510164","name":"simulate bad result from db","func":"msg.payload = [];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":360,"y":880,"wires":[["af19c8b9.373f08","bfc7d213.f19168"]]},{"id":"ebc37340.ddfd9","type":"ui_gauge","z":"5a245aa1.510164","name":"Available Hand Sanitizer Dispenser 1","group":"8b5cde76.edd58","order":3,"width":"6","height":"6","gtype":"wave","title":"Available Hand Sanitizer","label":"ml","format":"{{msg.payload[0].available}}","min":0,"max":"3000","colors":["#00b500","#e6e600","#ca3838"],"seg1":"500","seg2":"1500","x":760,"y":940,"wires":[]},{"id":"af19c8b9.373f08","type":"debug","z":"5a245aa1.510164","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":730,"y":820,"wires":[]},{"id":"c45993.1bd30e7","type":"inject","z":"5a245aa1.510164","name":"bad request","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":880,"wires":[["6b2ccd77.4da4cc"]]},{"id":"2385f349.ef9804","type":"inject","z":"5a245aa1.510164","name":"set level 1100","props":[{"p":"payload[0].available","v":"1100","vt":"num"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":320,"y":1020,"wires":[["ebc37340.ddfd9"]]},{"id":"8b5cde76.edd58","type":"ui_group","name":"","tab":"8f03e639.85956","order":1,"disp":true,"width":"6","collapse":false},{"id":"8f03e639.85956","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

or

[{"id":"c45993.1bd30e7","type":"inject","z":"5a245aa1.510164","name":"bad request","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":130,"y":940,"wires":[["6b2ccd77.4da4cc"]]},{"id":"6b2ccd77.4da4cc","type":"function","z":"5a245aa1.510164","name":"simulate bad result from db","func":"msg.payload = [];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":360,"y":940,"wires":[["ebc37340.ddfd9","af19c8b9.373f08"]]},{"id":"ebc37340.ddfd9","type":"ui_gauge","z":"5a245aa1.510164","name":"Available Hand Sanitizer Dispenser 1","group":"8b5cde76.edd58","order":3,"width":"6","height":"6","gtype":"wave","title":"Available Hand Sanitizer","label":"ml","format":"{{msg.payload[0].available}}","min":0,"max":"3000","colors":["#00b500","#e6e600","#ca3838"],"seg1":"500","seg2":"1500","x":760,"y":940,"wires":[]},{"id":"af19c8b9.373f08","type":"debug","z":"5a245aa1.510164","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":730,"y":820,"wires":[]},{"id":"2385f349.ef9804","type":"inject","z":"5a245aa1.510164","name":"set level 1100","props":[{"p":"payload[0].available","v":"1100","vt":"num"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":320,"y":1020,"wires":[["ebc37340.ddfd9"]]},{"id":"1da1c840.e6bd6","type":"change","z":"5a245aa1.510164","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[{\"available\":0}]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":430,"y":860,"wires":[["af19c8b9.373f08","ebc37340.ddfd9"]]},{"id":"90121f6c.ad8ee","type":"catch","z":"5a245aa1.510164","name":"","scope":["ebc37340.ddfd9"],"uncaught":false,"x":240,"y":860,"wires":[["1da1c840.e6bd6"]]},{"id":"8b5cde76.edd58","type":"ui_group","name":"","tab":"8f03e639.85956","order":1,"disp":true,"width":"6","collapse":false},{"id":"8f03e639.85956","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

But in long term may be worth rethinking the flow set up.

[tawny_alexa]

Thank you so much, it works! I know my flow if far from perfect, but I've been using node-red for less than a week for an IoT university project. Can't thank you enough, I really had no ideia how to go around this problem.