UI Button with dynamic topic based on state

Hello All
I have been slowly integrating all my flows into databases. This has been great below is one of my standard flows. To update/insert into the datebase I have to create a msg.topic put the SQL statement in , it is then pushed usually to a functoin node , I do some processing with the msg.payload and put it into the SQL msg.topic and pass it to the database.

Is there anyway to simplify this? My thought was within a UI Switch node. if it can dynamically update the topic portion of it when I click the button. This would cut out me having to mess with function nodes to combine it all together
For for example
I have a Switch node with topic "UPDATE schedule SET weekend_off = "switchnodepayload" WHEREID` = '0'

When I turn it on it would output msg.topic as "UPDATE schedule SET weekend_off =1WHEREID` = '0'

or when it is off
"UPDATE schedule SET weekend_off =0WHEREID` = '0'"

A previous thread someone suggested jsonata which helped me with database items. Can Jsonata be used like this? or is there another solution?
Not a huge deal its mearly me just trying to optimise the fows :slight_smile:

Edit: forgot flow

[{"id":"e5837169.54004","type":"ui_text_input","z":"9b0017f2.d74bd8","name":"","label":"One Off Time Off","tooltip":"","group":"3fa1d099.a3e7c","order":20,"width":0,"height":0,"passthru":false,"mode":"time","delay":"500","topic":"","x":570,"y":2280,"wires":[["355d89fe.d4ad26"]]},{"id":"ef0e6a9a.f5f2d8","type":"mysql","z":"9b0017f2.d74bd8","mydb":"9c584dac.bb0a6","name":"","x":290,"y":2280,"wires":[["cd07c0e7.a73f5"]]},{"id":"cd07c0e7.a73f5","type":"function","z":"9b0017f2.d74bd8","name":"","func":"var off = msg.payload[0].oneoff_off ;\nmsg.payload = off ;\nreturn msg;","outputs":1,"noerr":0,"x":410,"y":2280,"wires":[["83b240e5.f9216"]]},{"id":"7c1f0dc1.da8cd4","type":"inject","z":"9b0017f2.d74bd8","name":"1 hour ","topic":"SELECT `oneoff_off`FROM `schedule` WHERE `ID` = 0","payload":"","payloadType":"date","repeat":"3600","crontab":"","once":true,"onceDelay":0.1,"x":160,"y":2280,"wires":[["ef0e6a9a.f5f2d8"]]},{"id":"355d89fe.d4ad26","type":"function","z":"9b0017f2.d74bd8","name":"Time convert","func":"  msgTemp = msg.payload;\n    time = msgTemp / 1000;\n    // calculates hour from seconds input\n    h = Math.floor(time / 3600); \n    // calculates minutes from remainder \n    m = Math.floor(time % 3600 / 60); \n    // ontime formatted for Schedex standards\n    ontime = ('0' + h).slice(-2) + \":\" + ('0' + m).slice(-2);\n    //setting ontime\nvar sqlstart = \"UPDATE `schedule` SET `oneoff_off`='\"\nvar sqlend = \"' WHERE `ID` = '0'\"\n    msg.topic = sqlstart + ontime +sqlend;\n    //setting onpayload\n    global.set(\"oneofftime\", ontime);\n       msg.payload = ontime;\n    return msg;","outputs":1,"noerr":0,"x":750,"y":2280,"wires":[["4fdd6563.685d6c","905b7717.9b8a58"]]},{"id":"4fdd6563.685d6c","type":"link out","z":"9b0017f2.d74bd8","name":"","links":["8927a47d.9e4468"],"x":915,"y":2360,"wires":[]},{"id":"3fa1d099.a3e7c","type":"ui_group","z":"","name":"Espresso","tab":"84da1155.5e744","order":2,"disp":true,"width":"6","collapse":false},{"id":"9c584dac.bb0a6","type":"MySQLdatabase","z":"","host":"","port":"3306","db":"coffee","tz":""},{"id":"84da1155.5e744","type":"ui_tab","z":"","name":"Kitchen","icon":"dashboard","order":5,"disabled":false,"hidden":false}]

You might find the template node useful.


UPDATE schedule SET weekend_off = {{payload}} WHERE ID = '{{flow.my_id}}'

You can access msg, flow and global variables. Read the help info on the side bar

1 Like