Restart Flow after changing data

Dashboard
1

Hello,

I am reading data from a database and in dependency to that I create buttons on the dashboard. By clicking the buttons I write data in the database again. In dependency to the new data the number of buttons change. But how do I start the flow again to read the changed data from database? I start the procedure by a timestamp injection node; to loop this doesn't helped. What's the best practice in this case? I fear that I misunderstood the functionality of Node Red in this point.

Here is my flow:

[{"id":"4908e9f4.3b0eb8","type":"tab","label":"Haushalt","disabled":false,"info":""},{"id":"ef4a66.cf651598","type":"sqlite","z":"4908e9f4.3b0eb8","mydb":"928d1f20.39874","sqlquery":"fixed","sql":"SELECT hId, hTodo, hIcon FROM haushalt where hLast < current_Date;","name":"SmartyPi getTodo","x":350,"y":100,"wires":[["e22adac3.7feb38"]]},{"id":"a6c876c9.41757","type":"ui_template","z":"4908e9f4.3b0eb8","group":"3674240e.411144","name":"Buttons Daily","order":0,"width":0,"height":0,"format":"<!-- Add icon library -->\n<link href=\"https://fonts.googleapis.com/icon?family=Material+Icons\" rel=\"stylesheet\">\n \n<!-- Style buttons -->\n <style>\n.btn {\n  background-color: #0094ce; \n  border: none; \n  color: white; \n  padding: 5px 5px; \n  font-size: 16px;\n  cursor: pointer; \n}\n\n.btn:hover {\n  background-color: #00aaed;\n}\n\n.nextButton {\n    float: left;\n    padding-left: 5px;\n}\n\n#setUid {\n    display: none;\n}\n</style>\n\n\n<div class=\"choose\" id=\"setTodo\">\n<div ng-repeat=\"row in msg.payload[[0]]\" class=\"nextButton\">\n<button class=\"btn\" title={{row.hTodo}}  style=\"height: 35px; width: 35px;\"><i class=\"material-icons\" ng-click=\"setTodo({todo: row.hId})\">{{row.hIcon}}</i></button>\n</div>\n</div>\n\n<div class=\"dialog\" id=\"setUid\">    \n<div ng-repeat=\"row in msg.payload[[1]]\" class=\"nextButton\">\n<button class=\"btn\" id=\"test2\" title={{row.uName}}  style=\"height: 35px; width: 35px;\"><i class=\"material-icons\" ng-click=\"setUser({user: row.uId})\">{{row.uIcon}}</i></button>\n</div>\n</div>\n\n\n<script>\n(function(scope) {\n    scope.payload = [];\n     scope.setTodo = function(getTodo) {\n        scope.payload.push(getTodo);\n        document.getElementById(\"setUid\").style.display = \"block\";\n        document.getElementById(\"setTodo\").style.display = \"none\";\n    }\n    scope.setUser = function(getUser) {\n        scope.payload.push(getUser);\n        scope.send({status: true, payload : scope.payload});\n    }\n})(scope);\n \n</script>\n","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":790,"y":100,"wires":[["af110667.329f78"]]},{"id":"cd41a4c6.5c9bf","type":"sqlite","z":"4908e9f4.3b0eb8","mydb":"928d1f20.39874","sqlquery":"fixed","sql":"SELECT uId, uName, uIcon FROM user;","name":"SmartyPi getUser","x":490,"y":40,"wires":[["e22adac3.7feb38"]]},{"id":"72855e8d.015038","type":"delay","z":"4908e9f4.3b0eb8","name":"","pauseType":"delay","timeout":"1","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":260,"y":40,"wires":[["cd41a4c6.5c9bf"]]},{"id":"e22adac3.7feb38","type":"join","z":"4908e9f4.3b0eb8","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":650,"y":100,"wires":[["a6c876c9.41757"]]},{"id":"33cc495c.4dcda6","type":"function","z":"4908e9f4.3b0eb8","name":"Update Haushalt","func":"backup = msg;\nmsg.topic = \"Update haushalt set hLast = current_date where hId= \" + backup.payload[0][\"todo\"] + \";Insert into done (uID, dDate, dTime) values(\" + backup.payload[1][\"user\"] + \", current_date, (SELECT hTime from haushalt where hId = \" + backup.payload[0][\"todo\"] + \"));\";\nreturn msg;","outputs":1,"noerr":0,"x":1090,"y":100,"wires":[["5e85ec73.1202fc"]]},{"id":"5e85ec73.1202fc","type":"sqlite","z":"4908e9f4.3b0eb8","mydb":"928d1f20.39874","sqlquery":"msg.topic","sql":"","name":"Update Haushalt","x":1290,"y":100,"wires":[[]]},{"id":"af110667.329f78","type":"switch","z":"4908e9f4.3b0eb8","name":"","property":"status","propertyType":"msg","rules":[{"t":"true"}],"checkall":"true","repair":false,"outputs":1,"x":930,"y":100,"wires":[["33cc495c.4dcda6"]]},{"id":"22d26df1.0d9f8a","type":"inject","z":"4908e9f4.3b0eb8","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"onceDelay":0.1,"x":130,"y":100,"wires":[["72855e8d.015038","ef4a66.cf651598"]]},{"id":"928d1f20.39874","type":"sqlitedb","z":"","db":"/home/pi/smartyPi/database/smartyPi.sqlite","mode":"RWC"},{"id":"3674240e.411144","type":"ui_group","z":"","name":"Daily Todo","tab":"f851193c.456508","order":1,"disp":true,"width":"6","collapse":false},{"id":"f851193c.456508","type":"ui_tab","z":"","name":"Haushalt","icon":"home","order":1,"disabled":false,"hidden":false}]

I hope you understand my problem and have an idea.

Thanks for help!
Sanne

2

But how do I start the flow again to read the changed data from database

You don't.

Looking at your flow (i dont have sqlite), you are trying to query the database and based on the data, fill the template node correct ? What is the output of the first sqlite node ? I think you don't need the join node (depending on the output).

3

The output is perfectly correct. I create two kinds of buttons, the join is needed to get them in one msg and process them in the template. Even the database update is correct. But after "Update Haushalt" it needs to load the buttons (the template) again and only in this moment it should start the injection again.

4

If the output is correct for the template node, then you could use a ui_control node to reload the tab.

5

I used the ui_control node with the output "Change tab or group events only" and the name {"tab":"Haushalt"} but it doesn't start with the injection again.

6

I had a little sucess with setting the timestamp to one second. It in this case shows the wrong buttons for just this second and then shows the right buttons. But why should I start hunderts of database queries when I only need one?

7

The inject node does not have an input, but you can connect your button to the ui_control node and connect that node to the beginning of your query, then it will reload perform the query, build the template and show it.

8

This time I really don't know if I understand what you mean. I connected an ui control with the end of my flow, set output to "Change tab..." and name to {"tab":"Haushalt"}. Then I connected this with the two sqlite nodes. Now the flow doesn't work once.

This is my flow:

[{"id":"4908e9f4.3b0eb8","type":"tab","label":"Haushalt","disabled":false,"info":""},{"id":"ef4a66.cf651598","type":"sqlite","z":"4908e9f4.3b0eb8","mydb":"928d1f20.39874","sqlquery":"fixed","sql":"SELECT hId, hTodo, hIcon FROM haushalt where hLast < current_Date;","name":"SmartyPi getTodo","x":350,"y":100,"wires":[["e22adac3.7feb38"]]},{"id":"a6c876c9.41757","type":"ui_template","z":"4908e9f4.3b0eb8","group":"3674240e.411144","name":"Buttons Daily","order":0,"width":0,"height":0,"format":"<!-- Add icon library -->\n<link href=\"https://fonts.googleapis.com/icon?family=Material+Icons\" rel=\"stylesheet\">\n \n<!-- Style buttons -->\n <style>\n.btn {\n  background-color: #0094ce; \n  border: none; \n  color: white; \n  padding: 5px 5px; \n  font-size: 16px;\n  cursor: pointer; \n}\n\n.btn:hover {\n  background-color: #00aaed;\n}\n\n.nextButton {\n    float: left;\n    padding-left: 5px;\n}\n\n#setUid {\n    display: none;\n}\n</style>\n\n\n<div class=\"choose\" id=\"setTodo\">\n<div ng-repeat=\"row in msg.payload[[0]]\" class=\"nextButton\">\n<button class=\"btn\" title={{row.hTodo}}  style=\"height: 35px; width: 35px;\"><i class=\"material-icons\" ng-click=\"setTodo({todo: row.hId})\">{{row.hIcon}}</i></button>\n</div>\n</div>\n\n<div class=\"dialog\" id=\"setUid\">    \n<div ng-repeat=\"row in msg.payload[[1]]\" class=\"nextButton\">\n<button class=\"btn\" id=\"test2\" title={{row.uName}}  style=\"height: 35px; width: 35px;\"><i class=\"material-icons\" ng-click=\"setUser({user: row.uId})\">{{row.uIcon}}</i></button>\n</div>\n</div>\n\n\n<script>\n(function(scope) {\n    scope.payload = [];\n     scope.setTodo = function(getTodo) {\n        scope.payload.push(getTodo);\n        document.getElementById(\"setUid\").style.display = \"block\";\n        document.getElementById(\"setTodo\").style.display = \"none\";\n    }\n    scope.setUser = function(getUser) {\n        scope.payload.push(getUser);\n        scope.send({status: true, payload : scope.payload});\n        document.getElementById(\"setUid\").style.display = \"none\";\n        document.getElementById(\"setTodo\").style.display = \"block\";\n    }\n})(scope);\n \n</script>\n","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":790,"y":100,"wires":[["af110667.329f78"]]},{"id":"cd41a4c6.5c9bf","type":"sqlite","z":"4908e9f4.3b0eb8","mydb":"928d1f20.39874","sqlquery":"fixed","sql":"SELECT uId, uName, uIcon FROM user;","name":"SmartyPi getUser","x":490,"y":40,"wires":[["e22adac3.7feb38"]]},{"id":"72855e8d.015038","type":"delay","z":"4908e9f4.3b0eb8","name":"","pauseType":"delay","timeout":"1","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":260,"y":40,"wires":[["cd41a4c6.5c9bf"]]},{"id":"e22adac3.7feb38","type":"join","z":"4908e9f4.3b0eb8","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":650,"y":100,"wires":[["a6c876c9.41757","1ef197f5.cf73c"]]},{"id":"33cc495c.4dcda6","type":"function","z":"4908e9f4.3b0eb8","name":"Update Haushalt","func":"backup = msg;\nmsg.topic = \"Update haushalt set hLast = current_date where hId= \" + backup.payload[0][\"todo\"] + \";Insert into done (uID, dDate, dTime) values(\" + backup.payload[1][\"user\"] + \", current_date, (SELECT hTime from haushalt where hId = \" + backup.payload[0][\"todo\"] + \"));\";\nreturn msg;","outputs":1,"noerr":0,"x":1090,"y":100,"wires":[["5e85ec73.1202fc"]]},{"id":"5e85ec73.1202fc","type":"sqlite","z":"4908e9f4.3b0eb8","mydb":"928d1f20.39874","sqlquery":"msg.topic","sql":"","name":"Update Haushalt","x":1290,"y":100,"wires":[["4caf4ce7.5db2dc"]]},{"id":"af110667.329f78","type":"switch","z":"4908e9f4.3b0eb8","name":"","property":"status","propertyType":"msg","rules":[{"t":"true"}],"checkall":"true","repair":false,"outputs":1,"x":930,"y":100,"wires":[["33cc495c.4dcda6"]]},{"id":"22d26df1.0d9f8a","type":"inject","z":"4908e9f4.3b0eb8","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"onceDelay":0.1,"x":130,"y":100,"wires":[["72855e8d.015038","ef4a66.cf651598"]]},{"id":"1ef197f5.cf73c","type":"debug","z":"4908e9f4.3b0eb8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":760,"y":200,"wires":[]},{"id":"4caf4ce7.5db2dc","type":"ui_ui_control","z":"4908e9f4.3b0eb8","name":"{\"tab\":\"Haushalt\"}","events":"change","x":350,"y":340,"wires":[["72855e8d.015038","ef4a66.cf651598"]]},{"id":"928d1f20.39874","type":"sqlitedb","z":"","db":"/home/pi/smartyPi/database/smartyPi.sqlite","mode":"RWC"},{"id":"3674240e.411144","type":"ui_group","z":"","name":"Daily Todo","tab":"f851193c.456508","order":1,"disp":true,"width":"6","collapse":false},{"id":"f851193c.456508","type":"ui_tab","z":"","name":"Haushalt","icon":"home","order":1,"disabled":false,"hidden":false}]

Thanks for trying to help!

9

I notice in your button node you have Pass through messages from input. checked. That means that the output of the join will be sent thru the button node to the switch node but the switch node is checking msg.status - where is that set?

When trying to debug, I add lots of debug nodes to see that is happening (remember to remove the when you figure it out)

10

To finish the process and update the database you need to click two buttons. The msg.status is set true (JS) when both buttons are clicked, so that the database update don't starts with only one selection.

I also use the debug nodes to see what's happening, I only removed the debug nodes where I'm sure that everything is alright.

11

For anyone else to test this, you need to provide the sqlite schema for the two tables and some test data.

12

That's my sql-schema and some test data:

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `user` (
        `uId`   integer PRIMARY KEY AUTOINCREMENT,
        `uName` Text,
        `uIcon` Text
);
INSERT INTO `user` VALUES (1,'Leonie','face');
INSERT INTO `user` VALUES (2,'Felix','build');
INSERT INTO `user` VALUES (3,'Sanne','school');
CREATE TABLE IF NOT EXISTS `haushalt` (
        `hId`   INteger PRIMARY KEY AUTOINCREMENT,
        `hTodo` text,
        `hIcon` text,
        `hTime` INteger,
        `hFreq` Integer,
        `hLast` Date DEFAULT current_date
);
INSERT INTO `haushalt` VALUES (1,'Tiere füttern','pets',5,1,'2020-05-20');
INSERT INTO `haushalt` VALUES (2,'Spühlmaschine','restaurant',10,1,'2020-05-20');
INSERT INTO `haushalt` VALUES (3,'Oberflächen Küche','fingerprint',5,1,'2020-05-20');
INSERT INTO `haushalt` VALUES (4,'Blumen gießen','local_florist',10,1,'2020-05-20');
INSERT INTO `haushalt` VALUES (5,'Badezimmer','wc',25,3,'2020-05-20');
INSERT INTO `haushalt` VALUES (6,'Fische','waves',20,7,'2020-05-20');
INSERT INTO `haushalt` VALUES (7,'Betten','hotel',12,14,'2020-05-20');
INSERT INTO `haushalt` VALUES (8,'Küche','kitchen',30,6,'2020-05-20');
INSERT INTO `haushalt` VALUES (9,'Wäsche','local_laundry_service',20,3,'2020-05-20');
INSERT INTO `haushalt` VALUES (10,'Sauger reinigen','android',15,7,'2020-05-20');
INSERT INTO `haushalt` VALUES (11,'Müll','delete',10,3,'2020-05-20');
INSERT INTO `haushalt` VALUES (12,'Saugen','settings_input_svideo',5,1,'2020-05-23');
INSERT INTO `haushalt` VALUES (13,'Staub wischen','brush',15,7,'2020-05-20');
INSERT INTO `haushalt` VALUES (14,'Wischen','local_drink',30,14,'2020-05-20');
CREATE TABLE IF NOT EXISTS `done` (
        `dId`   Integer PRIMARY KEY AUTOINCREMENT,
        `uID`   integer,
        `dTime` Integer,
        `dDate` text DEFAULT current_date
);
INSERT INTO `done` VALUES (1,3,5,'2020-05-22');
COMMIT;

Here is the flow I use at the moment:

[{"id":"4908e9f4.3b0eb8","type":"tab","label":"Haushalt","disabled":false,"info":""},{"id":"ef4a66.cf651598","type":"sqlite","z":"4908e9f4.3b0eb8","mydb":"928d1f20.39874","sqlquery":"fixed","sql":"SELECT hId, hTodo, hIcon FROM haushalt where hLast < current_Date and hFreq = 1;","name":"SmartyPi getTodo","x":350,"y":100,"wires":[["e22adac3.7feb38"]]},{"id":"a6c876c9.41757","type":"ui_template","z":"4908e9f4.3b0eb8","group":"3674240e.411144","name":"Buttons Daily","order":0,"width":0,"height":0,"format":"<!-- Add icon library -->\n<link href=\"https://fonts.googleapis.com/icon?family=Material+Icons\" rel=\"stylesheet\">\n \n<!-- Style buttons -->\n <style>\n.btn {\n  background-color: #0094ce; \n  border: none; \n  color: white; \n  padding: 5px 5px; \n  font-size: 16px;\n  cursor: pointer; \n}\n\n.btn:hover {\n  background-color: #00aaed;\n}\n\n.nextButton {\n    float: left;\n    padding-left: 5px;\n}\n\n#setUid {\n    display: none;\n}\n</style>\n\n\n<div class=\"choose\" id=\"setTodo\">\n<div ng-repeat=\"row in msg.payload[[0]]\" class=\"nextButton\">\n<button class=\"btn\" title={{row.hTodo}}  style=\"height: 35px; width: 35px;\"><i class=\"material-icons\" ng-click=\"setTodo({todo: row.hId})\">{{row.hIcon}}</i></button>\n</div>\n</div>\n\n<div class=\"dialog\" id=\"setUid\">    \n<div ng-repeat=\"row in msg.payload[[1]]\" class=\"nextButton\">\n<button class=\"btn\" id=\"test2\" title={{row.uName}}  style=\"height: 35px; width: 35px;\"><i class=\"material-icons\" ng-click=\"setUser({user: row.uId})\">{{row.uIcon}}</i></button>\n</div>\n</div>\n\n\n<script>\n(function(scope) {\n    scope.payload = [];\n     scope.setTodo = function(getTodo) {\n        scope.payload.push(getTodo);\n        document.getElementById(\"setUid\").style.display = \"block\";\n        document.getElementById(\"setTodo\").style.display = \"none\";\n    }\n    scope.setUser = function(getUser) {\n        scope.payload.push(getUser);\n        scope.send({status: true, payload : scope.payload});\n        document.getElementById(\"setUid\").style.display = \"none\";\n        document.getElementById(\"setTodo\").style.display = \"block\";\n    }\n})(scope);\n \n</script>\n","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":790,"y":100,"wires":[["af110667.329f78"]]},{"id":"cd41a4c6.5c9bf","type":"sqlite","z":"4908e9f4.3b0eb8","mydb":"928d1f20.39874","sqlquery":"fixed","sql":"SELECT uId, uName, uIcon FROM user;","name":"SmartyPi getUser","x":490,"y":40,"wires":[["e22adac3.7feb38"]]},{"id":"72855e8d.015038","type":"delay","z":"4908e9f4.3b0eb8","name":"","pauseType":"delay","timeout":"1","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":260,"y":40,"wires":[["cd41a4c6.5c9bf"]]},{"id":"e22adac3.7feb38","type":"join","z":"4908e9f4.3b0eb8","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":650,"y":100,"wires":[["a6c876c9.41757"]]},{"id":"33cc495c.4dcda6","type":"function","z":"4908e9f4.3b0eb8","name":"Update Haushalt","func":"backup = msg;\nmsg.topic = \"Update haushalt set hLast = current_date where hId= \" + backup.payload[0][\"todo\"] + \";Insert into done (uID, dDate, dTime) values(\" + backup.payload[1][\"user\"] + \", current_date, (SELECT hTime from haushalt where hId = \" + backup.payload[0][\"todo\"] + \"));\";\nreturn msg;","outputs":1,"noerr":0,"x":1090,"y":100,"wires":[["5e85ec73.1202fc"]]},{"id":"5e85ec73.1202fc","type":"sqlite","z":"4908e9f4.3b0eb8","mydb":"928d1f20.39874","sqlquery":"msg.topic","sql":"","name":"Update Haushalt","x":1290,"y":100,"wires":[[]]},{"id":"af110667.329f78","type":"switch","z":"4908e9f4.3b0eb8","name":"","property":"status","propertyType":"msg","rules":[{"t":"true"}],"checkall":"true","repair":false,"outputs":1,"x":930,"y":100,"wires":[["33cc495c.4dcda6"]]},{"id":"731f51b4.d95c3","type":"inject","z":"4908e9f4.3b0eb8","name":"","topic":"","payload":"","payloadType":"date","repeat":"1","crontab":"","once":true,"onceDelay":0.1,"x":110,"y":180,"wires":[["ef4a66.cf651598","72855e8d.015038"]]},{"id":"928d1f20.39874","type":"sqlitedb","z":"","db":"/home/pi/smartyPi/database/smartyPi.sqlite","mode":"RWC"},{"id":"3674240e.411144","type":"ui_group","z":"","name":"Daily Todo","tab":"f851193c.456508","order":1,"disp":true,"width":"6","collapse":false},{"id":"f851193c.456508","type":"ui_tab","z":"","name":"Haushalt","icon":"home","order":1,"disabled":false,"hidden":false}]

It works like this in some way, but I don't want to inject the flow every second because it's not necessary and I see the wrong buttons for a second after updating "Haushalt".

13

To see what is happening, set the inject node to only fire when you press it. Then put a debug node (set to display complete msg object) on the output of the join, button, switch and function node/

Deploy and press the inject once. What do you see?

14

That's exactly the way I debuged my flow and every output is right the way I expected it. Especially if I start the flow just one time. The problem is to restart the flow automatically after one database update. If I use a timestamp I need to set it to every second, so I produce hundreds of database request which are not necessary and I see a wrong output for one second after I updated the database till the next injection is provoked.

15

Here are the debug messages:

After join:

{"_msgid":"8d3d124c.be3ee","topic":"","payload":[[{"hId":1,"hTodo":"Tiere füttern","hIcon":"pets"},{"hId":4,"hTodo":"Blumen gießen","hIcon":"local_florist"}],[{"uId":1,"uName":"Leonie","uIcon":"face"},{"uId":2,"uName":"Felix","uIcon":"build"},{"uId":3,"uName":"Sanne","uIcon":"school"}]]}

after button1:

{"_msgid":"8d3d124c.be3ee","topic":"","payload":[[{"hId":1,"hTodo":"Tiere füttern","hIcon":"pets"},{"hId":4,"hTodo":"Blumen gießen","hIcon":"local_florist"}],[{"uId":1,"uName":"Leonie","uIcon":"face"},{"uId":2,"uName":"Felix","uIcon":"build"},{"uId":3,"uName":"Sanne","uIcon":"school"}]]}

after button2:

{"status":true,"payload":[{"todo":4},{"user":2}],"socketid":"Mv4rSshXN2eGxTm3AACK","_msgid":"7cf25639.288c48"}

after switch:

{"status":true,"payload":[{"todo":4},{"user":2}],"socketid":"Mv4rSshXN2eGxTm3AACK","_msgid":"7cf25639.288c48"}

after function:

{"status":true,"payload":[{"todo":4},{"user":2}],"socketid":"Mv4rSshXN2eGxTm3AACK","_msgid":"7cf25639.288c48","topic":"Update haushalt set hLast = current_date where hId= 4; Insert into done (uID, dDate, dTime) values(2, current_date, (SELECT hTime from haushalt where hId = 4));"}

Greetings!

16

That is interesting because when I press the inject I get output from the join and the button and that is it.

If I go to the dashboard, the button has a dog print on it. If I press the button, the icon changed to a face but nothing shows up in the debug.

If I press the button again, the icon goes blank and I get a debug from the button, a debug from the switch, two debugs from the function and one from the database.

If I press the injext again, I get output from the join and the button and that is it. In addition, no button shows up on the dashboard.

I don't understand what you are trying to do so it is very hard to determine what you need to do.

17

The problem is not the functionality of the buttons or the data stored in the database. The problem is what happens after "update haushalt"! It should start again with "smarty getTodo" and "smarty getUser" and then directly show the correct buttons in the dashboard. If I use a timestamp for this I need to request the database every second, even I know there is no change! Futhermore I have a delay between clicked the button and the next timestamp, so that I see wrong buttons for up to two seconds.

I tried to film it:

dailytodo
dailytodo854×480 248 KB

So I need the possibility for a simple loop which automatically starts the database queries once when finished the flow.

Best wishes Sandra

18

Hi Sandra,

Let's see if I can help.

Am I right in that after you write to a database, you want to loop back, re-read the database and update something on the dashboard?

Has your flow changed since first post? If so, post it again with a very concise stepwise explanation (perhaps even an annotated screenshot) and I'll show you how to do this. (I do something very similar where by I loop back to get more data from a plc to send to database when I see a flag change - quite similar to your requirements if I read it right)

19

Hello Steve,
thanks for trying to help! The flow only changed a little bit, because I need to split up the inserts/update into the database.

[{"id":"ef4a66.cf651598","type":"sqlite","z":"4908e9f4.3b0eb8","mydb":"928d1f20.39874","sqlquery":"fixed","sql":"SELECT hId, hTodo, hIcon FROM haushalt where hLast < current_Date and hFreq = 1;","name":"SmartyPi getTodo","x":350,"y":100,"wires":[["e22adac3.7feb38"]]},{"id":"a6c876c9.41757","type":"ui_template","z":"4908e9f4.3b0eb8","group":"3674240e.411144","name":"Buttons Daily","order":0,"width":0,"height":0,"format":"<!-- Add icon library -->\n<link href=\"https://fonts.googleapis.com/icon?family=Material+Icons\" rel=\"stylesheet\">\n \n<!-- Style buttons -->\n <style>\n.btn {\n  background-color: #0094ce; \n  border: none; \n  color: white; \n  padding: 5px 5px; \n  font-size: 16px;\n  cursor: pointer; \n}\n\n.btn:hover {\n  background-color: #00aaed;\n}\n\n.nextButton {\n    float: left;\n    padding-left: 5px;\n}\n\n#setUid {\n    display: none;\n}\n</style>\n\n\n<div class=\"choose\" id=\"setTodo\">\n<div ng-repeat=\"row in msg.payload[[0]]\" class=\"nextButton\">\n<button class=\"btn\" title={{row.hTodo}}  style=\"height: 35px; width: 35px;\"><i class=\"material-icons\" ng-click=\"setTodo({todo: row.hId})\">{{row.hIcon}}</i></button>\n</div>\n</div>\n\n<div class=\"dialog\" id=\"setUid\">    \n<div ng-repeat=\"row in msg.payload[[1]]\" class=\"nextButton\">\n<button class=\"btn\" title={{row.uName}}  style=\"height: 35px; width: 35px;\"><i class=\"material-icons\" ng-click=\"setUser({user: row.uId})\">{{row.uIcon}}</i></button>\n</div>\n<div style=\"float: right;\">\n<button class=\"btn\" title=\"zurück\" style=\"height: 35px; width: 35px;\"><i class=\"material-icons\" ng-click=\"getback()\">reply</i></button>\n</div>\n</div>\n\n\n<script>\n(function(scope) {\n    \n    scope.getback = function() {\n        scope.payload = [];\n        document.getElementById(\"setUid\").style.display = \"none\";\n        document.getElementById(\"setTodo\").style.display = \"block\";\n    }    \n    \n    scope.setTodo = function(getTodo) {\n        scope.payload = [];\n        scope.payload.push(getTodo);\n        document.getElementById(\"setUid\").style.display = \"block\";\n        document.getElementById(\"setTodo\").style.display = \"none\";\n    }\n    \n    scope.setUser = function(getUser) {\n        scope.payload.push(getUser);\n        scope.send({status: true, payload : scope.payload});\n        document.getElementById(\"setUid\").style.display = \"none\";\n        document.getElementById(\"setTodo\").style.display = \"block\";\n    }\n \n})(scope);\n \n</script>\n","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":790,"y":100,"wires":[["af110667.329f78"]]},{"id":"cd41a4c6.5c9bf","type":"sqlite","z":"4908e9f4.3b0eb8","mydb":"928d1f20.39874","sqlquery":"fixed","sql":"SELECT uId, uName, uIcon FROM user;","name":"SmartyPi getUser","x":490,"y":40,"wires":[["e22adac3.7feb38"]]},{"id":"72855e8d.015038","type":"delay","z":"4908e9f4.3b0eb8","name":"","pauseType":"delay","timeout":"1","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":260,"y":40,"wires":[["cd41a4c6.5c9bf"]]},{"id":"e22adac3.7feb38","type":"join","z":"4908e9f4.3b0eb8","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":650,"y":100,"wires":[["a6c876c9.41757"]]},{"id":"33cc495c.4dcda6","type":"function","z":"4908e9f4.3b0eb8","name":"Update Haushalt","func":"backup = msg;\nmsg.topic = \"Update haushalt set hLast = current_date where hId= \" + backup.payload[0][\"todo\"] + \";\";\nmsg.payload = \"\";\nreturn msg;","outputs":1,"noerr":0,"x":1110,"y":60,"wires":[["5e85ec73.1202fc"]]},{"id":"5e85ec73.1202fc","type":"sqlite","z":"4908e9f4.3b0eb8","mydb":"928d1f20.39874","sqlquery":"msg.topic","sql":"","name":"Update Haushalt","x":1310,"y":60,"wires":[[]]},{"id":"af110667.329f78","type":"switch","z":"4908e9f4.3b0eb8","name":"","property":"status","propertyType":"msg","rules":[{"t":"true"}],"checkall":"true","repair":false,"outputs":1,"x":930,"y":100,"wires":[["33cc495c.4dcda6","dbd89c0f.d558c8"]]},{"id":"731f51b4.d95c3","type":"inject","z":"4908e9f4.3b0eb8","name":"","topic":"","payload":"","payloadType":"date","repeat":"1","crontab":"","once":true,"onceDelay":0.1,"x":110,"y":140,"wires":[["ef4a66.cf651598","72855e8d.015038"]]},{"id":"dbd89c0f.d558c8","type":"function","z":"4908e9f4.3b0eb8","name":"Insert Done","func":"backup = msg;\nif (backup.payload[1][\"user\"] == 1) {\nmsg.topic = \"Insert into done (uID, dDate, dTime) values(\" + backup.payload[1][\"user\"] + \", current_date, (SELECT hTime from haushalt where hId = \" + backup.payload[0][\"todo\"] * 2 + \"));\";\n}\nelse {\n msg.topic = \"Insert into done (uID, dDate, dTime) values(\" + backup.payload[1][\"user\"] + \", current_date, (SELECT hTime from haushalt where hId = \" + backup.payload[0][\"todo\"] + \"));\";   \n}\nmsg.payload = \"\";\nreturn msg;","outputs":1,"noerr":0,"x":1090,"y":100,"wires":[["897f8bab.5279b8"]]},{"id":"897f8bab.5279b8","type":"sqlite","z":"4908e9f4.3b0eb8","mydb":"928d1f20.39874","sqlquery":"msg.topic","sql":"","name":"Update Done","x":1250,"y":100,"wires":[[]]},{"id":"928d1f20.39874","type":"sqlitedb","z":"","db":"/home/pi/smartyPi/database/smartyPi.sqlite","mode":"RWC"},{"id":"3674240e.411144","type":"ui_group","z":"","name":"Daily Todo","tab":"f851193c.456508","order":1,"disp":true,"width":"6","collapse":false},{"id":"f851193c.456508","type":"ui_tab","z":"","name":"Haushalt","icon":"home","order":1,"disabled":false,"hidden":false}]

I start the flow with an injection node (timestamp every second at the moment). I need two informations from the database: the userdata and the todos. To make sure that the queries are always send in the same order I need the 1s delay. The join connects the replays in msg.payload which is send to the template. Here I first show all todos (as a button), the user chooses a task (id stored in msg.payload), now I show all users and the user chooses his Icon (userId stored in msg.payload and msg.status is set true). The switch tests if both ids are stored (msg.status == true), now I create two queries for updating the database (update haushalt / update done) and execute them.

But now I need to request the database again! I hope the explanation is understandable. Your short explanation of my task (write to a database, loop back, re-read the database and update dashboard) is perfectly right.

20

Hi, so, firstly, I used a ui_control to trigger the DB read on Dashboard TAB change - so that you get up to date data when you switch TAB on dashboard (you can tweak the ui_control to suit - open it settings to see options)

Next, I put your database calls in series so that everything that needs to happen - happens in sequence (the join was unnecessary overhead here TBH). The key to achieving this is storing the results from each DB call in a separate property of msg. (see change nodes AFTER DB nodes)

As the data for users data is now in msg.userData and the todo data goes in msg.todoData, i had to adjust the ui_template to access the data from these 2 new properties of msg.

Penultimately, I switched off the option in the ui_template to "pass messages through" (so that we avoid an infinite loop when i add the loop-back after DB write)

Lastly, I added a pair of link nodes to loop the msg back to the beginning to re-trigger a re-read.

As i dont understand your language or operation of this data, it may not function as expected BUT it should put you on the right path.

the flow...

image
image1268×345 57.3 KB

the flow json... (NOTE: check the DB configuration is correct - i modified it for testing purposes)

[{"id":"d4a1a93a.990998","type":"ui_template","z":"8c2324d8.09ab88","group":"b0a16af2.4bdda8","name":"Buttons Daily","order":0,"width":0,"height":0,"format":"<!-- Add icon library -->\n<link href=\"https://fonts.googleapis.com/icon?family=Material+Icons\" rel=\"stylesheet\">\n \n<!-- Style buttons -->\n <style>\n.btn {\n  background-color: #0094ce; \n  border: none; \n  color: white; \n  padding: 5px 5px; \n  font-size: 16px;\n  cursor: pointer; \n}\n\n.btn:hover {\n  background-color: #00aaed;\n}\n\n.nextButton {\n    float: left;\n    padding-left: 5px;\n}\n\n#setUid {\n    display: none;\n}\n</style>\n\n\n<div class=\"choose\" id=\"setTodo\">\n<div ng-repeat=\"row in msg.todoData\" class=\"nextButton\">\n<button class=\"btn\" title={{row.hTodo}}  style=\"height: 35px; width: 35px;\"><i class=\"material-icons\" ng-click=\"setTodo({todo: row.hId})\">{{row.hIcon}}</i></button>\n</div>\n</div>\n\n<div class=\"dialog\" id=\"setUid\">    \n<div ng-repeat=\"row in msg.userData\" class=\"nextButton\">\n<button class=\"btn\" title={{row.uName}}  style=\"height: 35px; width: 35px;\"><i class=\"material-icons\" ng-click=\"setUser({user: row.uId})\">{{row.uIcon}}</i></button>\n</div>\n<div style=\"float: right;\">\n<button class=\"btn\" title=\"zurück\" style=\"height: 35px; width: 35px;\"><i class=\"material-icons\" ng-click=\"getback()\">reply</i></button>\n</div>\n</div>\n\n\n<script>\n(function(scope) {\n    \n    scope.getback = function() {\n        scope.payload = [];\n        document.getElementById(\"setUid\").style.display = \"none\";\n        document.getElementById(\"setTodo\").style.display = \"block\";\n    }    \n    \n    scope.setTodo = function(getTodo) {\n        scope.payload = [];\n        scope.payload.push(getTodo);\n        document.getElementById(\"setUid\").style.display = \"block\";\n        document.getElementById(\"setTodo\").style.display = \"none\";\n    }\n    \n    scope.setUser = function(getUser) {\n        scope.payload.push(getUser);\n        scope.send({status: true, payload : scope.payload});\n        document.getElementById(\"setUid\").style.display = \"none\";\n        document.getElementById(\"setTodo\").style.display = \"block\";\n    }\n \n})(scope);\n \n</script>\n","storeOutMessages":true,"fwdInMessages":false,"resendOnRefresh":true,"templateScope":"local","x":590,"y":260,"wires":[["b6c2748e.a5e528","16967d0c.218093"]]},{"id":"ab4c982b.d15068","type":"function","z":"8c2324d8.09ab88","name":"Update Haushalt","func":"msg.origPayload = msg.payload;\nmsg.topic = \"Update haushalt set hLast = current_date where hId= \" + msg.origPayload[0].todo + \";\";\nmsg.payload = \"\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":940,"y":220,"wires":[["5aa0c794.7f8678"]]},{"id":"b6c2748e.a5e528","type":"switch","z":"8c2324d8.09ab88","name":"","property":"status","propertyType":"msg","rules":[{"t":"true"}],"checkall":"true","repair":false,"outputs":1,"x":760,"y":260,"wires":[["ab4c982b.d15068"]]},{"id":"2959bf53.0526c","type":"function","z":"8c2324d8.09ab88","name":"Insert Done","func":"\nif (msg.origPayload[1][\"user\"] == 1) {\nmsg.topic = \"Insert into done (uID, dDate, dTime) values(\" + msg.origPayload[1][\"user\"] + \", current_date, (SELECT hTime from haushalt where hId = \" + msg.origPayload[0][\"todo\"] * 2 + \"));\";\n}\nelse {\n msg.topic = \"Insert into done (uID, dDate, dTime) values(\" + msg.origPayload[1][\"user\"] + \", current_date, (SELECT hTime from haushalt where hId = \" + msg.origPayload[0][\"todo\"] + \"));\";   \n}\nmsg.payload = \"\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":930,"y":280,"wires":[["f1a803dd.0949b"]]},{"id":"a48ba5f2.c7ad28","type":"sqlite","z":"8c2324d8.09ab88","mydb":"869f5abd.567588","sqlquery":"fixed","sql":"SELECT hId, hTodo, hIcon FROM haushalt where hLast < current_Date and hFreq = 1;","name":"SmartyPi getTodo","x":150,"y":200,"wires":[["ae38c6bd.5a34e8","b9082a1.18de5d8"]]},{"id":"77aff20f.93ddcc","type":"sqlite","z":"8c2324d8.09ab88","mydb":"869f5abd.567588","sqlquery":"fixed","sql":"SELECT uId, uName, uIcon FROM user;","name":"SmartyPi getUser","x":170,"y":260,"wires":[["9883345.2b169c8","b2f2e66.8edee18"]]},{"id":"5aa0c794.7f8678","type":"sqlite","z":"8c2324d8.09ab88","mydb":"869f5abd.567588","sqlquery":"msg.topic","sql":"","name":"Update Haushalt","x":1140,"y":220,"wires":[["2959bf53.0526c"]]},{"id":"f1a803dd.0949b","type":"sqlite","z":"8c2324d8.09ab88","mydb":"869f5abd.567588","sqlquery":"msg.topic","sql":"","name":"Update Done","x":1130,"y":280,"wires":[["46ed7360.32406c"]]},{"id":"ae38c6bd.5a34e8","type":"change","z":"8c2324d8.09ab88","name":"","rules":[{"t":"set","p":"todoData","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":390,"y":200,"wires":[["77aff20f.93ddcc"]]},{"id":"9883345.2b169c8","type":"change","z":"8c2324d8.09ab88","name":"","rules":[{"t":"set","p":"userData","pt":"msg","to":"payload","tot":"msg"},{"t":"set","p":"topic","pt":"msg","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":260,"wires":[["d4a1a93a.990998"]]},{"id":"16967d0c.218093","type":"debug","z":"8c2324d8.09ab88","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":610,"y":300,"wires":[]},{"id":"b9082a1.18de5d8","type":"debug","z":"8c2324d8.09ab88","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":350,"y":160,"wires":[]},{"id":"b2f2e66.8edee18","type":"debug","z":"8c2324d8.09ab88","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":350,"y":300,"wires":[]},{"id":"cdaade15.9878","type":"link in","z":"8c2324d8.09ab88","name":"","links":["46ed7360.32406c"],"x":75,"y":160,"wires":[["a48ba5f2.c7ad28"]]},{"id":"2d13714f.2b485e","type":"ui_ui_control","z":"8c2324d8.09ab88","name":"","events":"change","x":120,"y":100,"wires":[["a48ba5f2.c7ad28"]]},{"id":"46ed7360.32406c","type":"link out","z":"8c2324d8.09ab88","name":"","links":["cdaade15.9878"],"x":1175,"y":320,"wires":[]},{"id":"1541b57f.a02aeb","type":"inject","z":"8c2324d8.09ab88","name":"start up only","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":130,"y":40,"wires":[["a48ba5f2.c7ad28"]]},{"id":"b0a16af2.4bdda8","type":"ui_group","z":"","name":"Daily Todo","tab":"ed1cfd8a.279a9","order":1,"disp":true,"width":"6","collapse":false},{"id":"869f5abd.567588","type":"sqlitedb","z":"8c2324d8.09ab88","db":"smartyPi.sqlite","mode":"RWC"},{"id":"ed1cfd8a.279a9","type":"ui_tab","z":"","name":"Haushalt","icon":"home","order":1,"disabled":false,"hidden":false}]