# Clear Level Graph

**URL:** <https://discourse.nodered.org/t/clear-level-graph/40940>\
**Category:** General\
**Created:** [13 February 2021 19:37 UTC](https://discourse.nodered.org/t/clear-level-graph/40940 "2021-02-13T19:37:17Z")\
**Posts on this page:** 14\
**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:** [13 February 2021 19:37 UTC](https://discourse.nodered.org/t/clear-level-graph/40940/1 "2021-02-13T19:37:17Z")

</div>

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:

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

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

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

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?

---

<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:** [13 February 2021 19:43 UTC](https://discourse.nodered.org/t/clear-level-graph/40940/2 "2021-02-13T19:43:51Z")

</div>

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.

---

<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:** [13 February 2021 20:22 UTC](https://discourse.nodered.org/t/clear-level-graph/40940/3 "2021-02-13T20:22:21Z")

</div>

Hi thank you very much for you answer

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

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

---

<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:** [13 February 2021 20:25 UTC](https://discourse.nodered.org/t/clear-level-graph/40940/4 "2021-02-13T20:25:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![hotNipi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/hotnipi/32/383_2.png) [@hotNipi](https://discourse.nodered.org/u/hotNipi)\
**Post date:** [13 February 2021 20:37 UTC](https://discourse.nodered.org/t/clear-level-graph/40940/5 "2021-02-13T20:37:11Z")

</div>

you have misspelled the undefined

---

<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:** [13 February 2021 20:45 UTC](https://discourse.nodered.org/t/clear-level-graph/40940/6 "2021-02-13T20:45:17Z")

</div>

Here's my flow

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

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

```

---

<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:** [13 February 2021 20:47 UTC](https://discourse.nodered.org/t/clear-level-graph/40940/7 "2021-02-13T20:47:48Z")

</div>

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 🙂

---

<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:** [13 February 2021 20:56 UTC](https://discourse.nodered.org/t/clear-level-graph/40940/8 "2021-02-13T20:56:20Z")

</div>

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

---

<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:** [13 February 2021 21:51 UTC](https://discourse.nodered.org/t/clear-level-graph/40940/9 "2021-02-13T21:51:05Z")

</div>

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

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

---

<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:** [13 February 2021 22:08 UTC](https://discourse.nodered.org/t/clear-level-graph/40940/10 "2021-02-13T22:08:14Z")

</div>

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?

---

<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:** [13 February 2021 22:21 UTC](https://discourse.nodered.org/t/clear-level-graph/40940/11 "2021-02-13T22:21:17Z")

</div>

sorry, my bad, out of the data base

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/1/0134f362607c5814de7da01505c620e9d004cfd2.png)

I have this

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/1/6143f91a09389042cdb781521580c9caf0a436d7.png)

---

<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:** [13 February 2021 22:30 UTC](https://discourse.nodered.org/t/clear-level-graph/40940/12 "2021-02-13T22:30:40Z")

</div>

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

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

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

---

<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:** [13 February 2021 22:49 UTC](https://discourse.nodered.org/t/clear-level-graph/40940/13 "2021-02-13T22:49:52Z")

</div>

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.

---

<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:** [27 February 2021 22:50 UTC](https://discourse.nodered.org/t/clear-level-graph/40940/14 "2021-02-27T22:50:43Z")

</div>

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