Downloading csv file generated by MySQL database after pressing a form button

General
1

Hello!
I want to download the file from the node-red dashborad from a remote computer, into the download folder of it. Right now Im using an static IP but not when the product is launched. I have used the flow @Steve-Mcl posted here ( https://flows.nodered.org/flow/db68bd4934cf46f39e6e453a348bc419). I know there are similar threads around the forum about the same topic, but I don´t get to find the right answer. I have tried it but when I press the link, a blank page opens with {}, but nothing happens. Probably I just need to fill something up but I really don´t know what.

The flow I constructed it´s the following:

[{"id":"7b63b1a9.f2278","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"3ec88aa3.2502c6","type":"csv","z":"7b63b1a9.f2278","name":"","sep":",","hdrin":"","hdrout":"once","multi":"mult","ret":"\\r\\n","temp":"","skip":"0","strings":false,"include_empty_strings":"","include_null_values":"","x":1510,"y":460,"wires":[["133ca772.428de9","f3645657.895c78"]]},{"id":"b40d6105.bccc4","type":"function","z":"7b63b1a9.f2278","name":"Extraer info para los desplegables.","func":"msg.topic = \"SELECT ID_pac,Nombre FROM Pacientes\"\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":340,"y":300,"wires":[["483bf186.b482e"]]},{"id":"483bf186.b482e","type":"mysql","z":"7b63b1a9.f2278","mydb":"2becde9d.a8a7b2","name":"","x":330,"y":360,"wires":[["43c10180.ac1ee"]]},{"id":"43c10180.ac1ee","type":"change","z":"7b63b1a9.f2278","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"$.payload.{Nombre:ID_pac}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":360,"wires":[["d9c3db67.055a28"]]},{"id":"296d6046.05bd4","type":"inject","z":"7b63b1a9.f2278","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"","payloadType":"str","x":250,"y":240,"wires":[["b40d6105.bccc4"]]},{"id":"d9c3db67.055a28","type":"ui_dropdown","z":"7b63b1a9.f2278","name":"Desplegable de pacientes","label":"Paciente:","tooltip":"","place":"Select option","group":"a7de1769.1bc388","order":3,"width":0,"height":0,"passthru":false,"multiple":false,"options":[{"label":"","value":0,"type":"num"}],"payload":"","topic":"","x":750,"y":360,"wires":[["f0ff4b77.ad8f88"]]},{"id":"92f3f2c5.276f1","type":"ui_form","z":"7b63b1a9.f2278","name":"Selección de fechas","label":"","group":"a7de1769.1bc388","order":0,"width":0,"height":0,"options":[{"label":"Inicio:","value":"ini","type":"date","required":true,"rows":null},{"label":"Final:","value":"fin","type":"date","required":false,"rows":null}],"formValue":{"ini":"","fin":""},"payload":"","submit":"submit","cancel":"cancel","topic":"","x":740,"y":460,"wires":[["f0ff4b77.ad8f88"]]},{"id":"f0ff4b77.ad8f88","type":"join","z":"7b63b1a9.f2278","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":990,"y":420,"wires":[["cdb8b55f.9ac2e8","47e78e58.8c0dd"]]},{"id":"cdb8b55f.9ac2e8","type":"function","z":"7b63b1a9.f2278","name":"prueba","func":"var idp = msg.payload[0];\n\n\nmsg.topic = \"SELECT * FROM Sensores WHERE ID_and_s IN (SELECT ID_and FROM Andadores WHERE ID_pac = \"+idp+\")\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1220,"y":420,"wires":[["662ea2b7.6c556c"]]},{"id":"662ea2b7.6c556c","type":"mysql","z":"7b63b1a9.f2278","mydb":"2becde9d.a8a7b2","name":"","x":1390,"y":380,"wires":[["3ec88aa3.2502c6"]]},{"id":"133ca772.428de9","type":"file","z":"7b63b1a9.f2278","name":"","filename":"","appendNewline":true,"createDir":false,"overwriteFile":"true","encoding":"none","x":1530,"y":540,"wires":[["afafe7e6.c13188"]]},{"id":"4fab1e76.22975","type":"function","z":"7b63b1a9.f2278","name":"Set base path","func":"//restrict to c:\\temp\\\nvar basePath = \"c:\\\\temp\\\\\";\nvar filename = msg.req.params.fn;\n\n\nif(filename.includes(\"..\\\\\")){\n    msg.payload = \"Illegal file path\";\n    msg.statusCode = 405;//not allowed\n    return [null, msg];//fire output 2\n} else if(filename.includes(\"../\")){\n    msg.payload = \"Illegal file path\";\n    msg.statusCode = 405;//not allowed\n    return [null, msg];//fire output 2\n} \n//TODO: add more checks\n\nmsg.filename = basePath + filename;\nreturn [msg, null];//fire output 1\n\n\n","outputs":2,"noerr":0,"initialize":"","finalize":"","x":1320,"y":560,"wires":[["133ca772.428de9"],["afafe7e6.c13188"]]},{"id":"afafe7e6.c13188","type":"http response","z":"7b63b1a9.f2278","name":"","statusCode":"","headers":{},"x":1670,"y":620,"wires":[]},{"id":"d42c4a68.7d5b58","type":"catch","z":"7b63b1a9.f2278","name":"","scope":null,"uncaught":false,"x":1140,"y":660,"wires":[["87bb4e8.e7cd7b","a80a28e9.136668"]]},{"id":"87bb4e8.e7cd7b","type":"function","z":"7b63b1a9.f2278","name":"Set 404","func":"msg.payload = msg.error;\nmsg.statusCode = 404;//resource not found\nreturn msg;","outputs":1,"noerr":0,"x":1520,"y":660,"wires":[["afafe7e6.c13188"]]},{"id":"a80a28e9.136668","type":"debug","z":"7b63b1a9.f2278","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1170,"y":700,"wires":[]},{"id":"fc3512b8.8d883","type":"ui_template","z":"7b63b1a9.f2278","group":"a7de1769.1bc388","name":"ui_temlplate - present download links on dashboard","order":0,"width":0,"height":0,"format":"<div >\n    <a href=\"/files/log.log\">HAZ CLICK PARA DESCARGAR</a>\n</div>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":1430,"y":760,"wires":[[]]},{"id":"24ef48e2.7bc1d8","type":"http in","z":"7b63b1a9.f2278","name":"","url":"/files/:fn","method":"get","upload":false,"swaggerDoc":"","x":1150,"y":580,"wires":[["4fab1e76.22975"]]},{"id":"f3645657.895c78","type":"debug","z":"7b63b1a9.f2278","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1670,"y":420,"wires":[]},{"id":"9f6a1492.235998","type":"ui_toast","z":"7b63b1a9.f2278","position":"dialog","displayTime":"2","highlight":"","sendall":false,"outputs":1,"ok":"OK","cancel":"","raw":true,"topic":"DATOS EXTRAIDOS DE LA BASE DE DATOS.","name":"","x":1230,"y":300,"wires":[[]]},{"id":"47e78e58.8c0dd","type":"change","z":"7b63b1a9.f2278","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"Haz click en el enlace para descargar.","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1100,"y":360,"wires":[["9f6a1492.235998"]]},{"id":"2becde9d.a8a7b2","type":"MySQLdatabase","z":"","name":"","host":"127.0.0.1","port":"3306","db":"test","tz":""},{"id":"a7de1769.1bc388","type":"ui_group","z":"","name":"DESCARGAR DATOS A EXCEL:","tab":"8cc1fd64.8863","order":4,"disp":true,"width":"12","collapse":false},{"id":"8cc1fd64.8863","type":"ui_tab","z":"","name":"Interacción con los datos","icon":"dashboard","disabled":false,"hidden":false}]

It should be pretty easy to solve, I just don´t know where to look at.

Thanks in advance!

1 Like
2

It doesnt look like you want to "download the file" to me. It looks like you have merged a MySQL DB call with a "file server" & expect a file to be sent - without ever writing any files (not that you need to - but it wont work how you have written it)

For anyone not willing to load up the flow - this is a screen shot (with annotations).

image
image1285×481 48.8 KB

Easy to solve? I'm afraid not - not without data (no one except you has access to the MySQL data). I suggest you attach debug nodes set to "show complete message" & then using the debug copy button, put the data in the flow to "fake it"

3

Hi!
Thanks for the quick response. Yes, I am pretty noob at this, Im just trying to follow along all the threads from the forum haha. I thought I had to place the csv generated into a file (I thought it was made to save files , but apparently its just meant to read them).

I will skip all the flow and just send again the flow with data that comes out from the last query. That will do right? Also, you mean just copying the values and setting them into the inject node?

As soon as you reply I will do it and send it.

Thanks!

4

.
If you are happy with the database part, yes, that should be enough.

To be clear, what is the goal? For the dashboard to permit a user to select some options then click a button to download data in CSV format?

5

Yes, the csv data is correct so no problem, it works.
The end goal is for the user to download the data from the sensors linked to that person within the selected range of time (beggining,end) so it can be used for further analysis.

[{"id":"7b63b1a9.f2278","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"33c08d98.3a5fb2","type":"csv","z":"7b63b1a9.f2278","name":"","sep":",","hdrin":"","hdrout":"once","multi":"mult","ret":"\\r\\n","temp":"","skip":"0","strings":false,"include_empty_strings":"","include_null_values":"","x":610,"y":140,"wires":[["ef6a09d1.5153a8","394e60f3.3897d"]]},{"id":"ef6a09d1.5153a8","type":"file","z":"7b63b1a9.f2278","name":"","filename":"","appendNewline":true,"createDir":false,"overwriteFile":"true","encoding":"none","x":630,"y":220,"wires":[["cc5b4c35.4da74"]]},{"id":"39bc2fca.f3cf9","type":"function","z":"7b63b1a9.f2278","name":"Set base path","func":"//restrict to c:\\temp\\\nvar basePath = \"c:\\\\temp\\\\\";\nvar filename = msg.req.params.fn;\n\n\nif(filename.includes(\"..\\\\\")){\n    msg.payload = \"Illegal file path\";\n    msg.statusCode = 405;//not allowed\n    return [null, msg];//fire output 2\n} else if(filename.includes(\"../\")){\n    msg.payload = \"Illegal file path\";\n    msg.statusCode = 405;//not allowed\n    return [null, msg];//fire output 2\n} \n//TODO: add more checks\n\nmsg.filename = basePath + filename;\nreturn [msg, null];//fire output 1\n\n\n","outputs":2,"noerr":0,"initialize":"","finalize":"","x":420,"y":240,"wires":[["ef6a09d1.5153a8"],["cc5b4c35.4da74"]]},{"id":"cc5b4c35.4da74","type":"http response","z":"7b63b1a9.f2278","name":"","statusCode":"","headers":{},"x":770,"y":300,"wires":[]},{"id":"811fbfc.e5b384","type":"catch","z":"7b63b1a9.f2278","name":"","scope":null,"uncaught":false,"x":240,"y":340,"wires":[["7bae7380.4d9eac","6d362a3d.a600c4"]]},{"id":"7bae7380.4d9eac","type":"function","z":"7b63b1a9.f2278","name":"Set 404","func":"msg.payload = msg.error;\nmsg.statusCode = 404;//resource not found\nreturn msg;","outputs":1,"noerr":0,"x":620,"y":340,"wires":[["cc5b4c35.4da74"]]},{"id":"6d362a3d.a600c4","type":"debug","z":"7b63b1a9.f2278","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":270,"y":380,"wires":[]},{"id":"ff632fd3.f7985","type":"ui_template","z":"7b63b1a9.f2278","group":"a7de1769.1bc388","name":"ui_temlplate - present download links on dashboard","order":0,"width":0,"height":0,"format":"<div >\n    <a href=\"/files/log.log\">HAZ CLICK PARA DESCARGAR</a>\n</div>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":530,"y":440,"wires":[[]]},{"id":"3eeeb15a.390c8e","type":"http in","z":"7b63b1a9.f2278","name":"","url":"/files/:fn","method":"get","upload":false,"swaggerDoc":"","x":250,"y":260,"wires":[["39bc2fca.f3cf9"]]},{"id":"bf1bb8fd.6d71b8","type":"inject","z":"7b63b1a9.f2278","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"","payloadType":"str","x":300,"y":140,"wires":[["706a5efa.627a1"]]},{"id":"706a5efa.627a1","type":"function","z":"7b63b1a9.f2278","name":"","func":"msg.payload =[{\"ID_and_s\":1,\"V_izq\":66,\"V_der\":10,\"F_izq\":130,\"F_der\":109,\"tiempo_mili\":1597228949544,\"timestamp\":\"2020-08-12T10:42:29.000Z\"},{\"ID_and_s\":1,\"V_izq\":66,\"V_der\":10,\"F_izq\":62,\"F_der\":109,\"tiempo_mili\":1597228949544,\"timestamp\":\"2020-08-12T10:42:29.000Z\"},{\"ID_and_s\":1,\"V_izq\":58,\"V_der\":10,\"F_izq\":62,\"F_der\":109,\"tiempo_mili\":1597228949547,\"timestamp\":\"2020-08-12T10:42:29.000Z\"},{\"ID_and_s\":1,\"V_izq\":58,\"V_der\":10,\"F_izq\":62,\"F_der\":68,\"tiempo_mili\":1597228949687,\"timestamp\":\"2020-08-12T10:42:29.000Z\"},{\"ID_and_s\":1,\"V_izq\":58,\"V_der\":10,\"F_izq\":62,\"F_der\":45,\"tiempo_mili\":1597228950690,\"timestamp\":\"2020-08-12T10:42:30.000Z\"},{\"ID_and_s\":1,\"V_izq\":58,\"V_der\":10,\"F_izq\":62,\"F_der\":45,\"tiempo_mili\":1597228951545,\"timestamp\":\"2020-08-12T10:42:31.000Z\"},{\"ID_and_s\":1,\"V_izq\":58,\"V_der\":10,\"F_izq\":58,\"F_der\":45,\"tiempo_mili\":1597228951546,\"timestamp\":\"2020-08-12T10:42:31.000Z\"},{\"ID_and_s\":1,\"V_izq\":22,\"V_der\":10,\"F_izq\":58,\"F_der\":45,\"tiempo_mili\":1597228951548,\"timestamp\":\"2020-08-12T10:42:31.000Z\"},{\"ID_and_s\":1,\"V_izq\":22,\"V_der\":10,\"F_izq\":58,\"F_der\":83,\"tiempo_mili\":1597228951692,\"timestamp\":\"2020-08-12T10:42:31.000Z\"},{\"ID_and_s\":1,\"V_izq\":22,\"V_der\":10,\"F_izq\":58,\"F_der\":73,\"tiempo_mili\":1597228952695,\"timestamp\":\"2020-08-12T10:42:32.000Z\"},{\"ID_and_s\":1,\"V_izq\":22,\"V_der\":10,\"F_izq\":58,\"F_der\":73,\"tiempo_mili\":1597228953548,\"timestamp\":\"2020-08-12T10:42:33.000Z\"},{\"ID_and_s\":1,\"V_izq\":22,\"V_der\":10,\"F_izq\":79,\"F_der\":73,\"tiempo_mili\":1597228953548,\"timestamp\":\"2020-08-12T10:42:33.000Z\"},{\"ID_and_s\":1,\"V_izq\":97,\"V_der\":10,\"F_izq\":79,\"F_der\":73,\"tiempo_mili\":1597228953550,\"timestamp\":\"2020-08-12T10:42:33.000Z\"},{\"ID_and_s\":1,\"V_izq\":97,\"V_der\":10,\"F_izq\":79,\"F_der\":114,\"tiempo_mili\":1597228953696,\"timestamp\":\"2020-08-12T10:42:33.000Z\"},{\"ID_and_s\":1,\"V_izq\":97,\"V_der\":10,\"F_izq\":79,\"F_der\":126,\"tiempo_mili\":1597228954701,\"timestamp\":\"2020-08-12T10:42:34.000Z\"},{\"ID_and_s\":1,\"V_izq\":97,\"V_der\":10,\"F_izq\":79,\"F_der\":126,\"tiempo_mili\":1597228955549,\"timestamp\":\"2020-08-12T10:42:35.000Z\"},{\"ID_and_s\":1,\"V_izq\":97,\"V_der\":10,\"F_izq\":124,\"F_der\":126,\"tiempo_mili\":1597228955550,\"timestamp\":\"2020-08-12T10:42:35.000Z\"},{\"ID_and_s\":1,\"V_izq\":68,\"V_der\":10,\"F_izq\":124,\"F_der\":126,\"tiempo_mili\":1597228955552,\"timestamp\":\"2020-08-12T10:42:35.000Z\"},{\"ID_and_s\":1,\"V_izq\":68,\"V_der\":10,\"F_izq\":124,\"F_der\":101,\"tiempo_mili\":1597228955702,\"timestamp\":\"2020-08-12T10:42:35.000Z\"},{\"ID_and_s\":1,\"V_izq\":68,\"V_der\":10,\"F_izq\":124,\"F_der\":69,\"tiempo_mili\":1597228956706,\"timestamp\":\"2020-08-12T10:42:36.000Z\"},{\"ID_and_s\":1,\"V_izq\":68,\"V_der\":10,\"F_izq\":124,\"F_der\":69,\"tiempo_mili\":1597228957553,\"timestamp\":\"2020-08-12T10:42:37.000Z\"},{\"ID_and_s\":1,\"V_izq\":68,\"V_der\":10,\"F_izq\":57,\"F_der\":69,\"tiempo_mili\":1597228957555,\"timestamp\":\"2020-08-12T10:42:37.000Z\"},{\"ID_and_s\":1,\"V_izq\":144,\"V_der\":10,\"F_izq\":57,\"F_der\":69,\"tiempo_mili\":1597228957556,\"timestamp\":\"2020-08-12T10:42:37.000Z\"},{\"ID_and_s\":1,\"V_izq\":144,\"V_der\":10,\"F_izq\":57,\"F_der\":31,\"tiempo_mili\":1597228957707,\"timestamp\":\"2020-08-12T10:42:37.000Z\"},{\"ID_and_s\":1,\"V_izq\":144,\"V_der\":10,\"F_izq\":57,\"F_der\":121,\"tiempo_mili\":1597228958710,\"timestamp\":\"2020-08-12T10:42:38.000Z\"},{\"ID_and_s\":1,\"V_izq\":144,\"V_der\":10,\"F_izq\":57,\"F_der\":121,\"tiempo_mili\":1597228959554,\"timestamp\":\"2020-08-12T10:42:39.000Z\"},{\"ID_and_s\":1,\"V_izq\":144,\"V_der\":10,\"F_izq\":94,\"F_der\":121,\"tiempo_mili\":1597228959556,\"timestamp\":\"2020-08-12T10:42:39.000Z\"},{\"ID_and_s\":1,\"V_izq\":49,\"V_der\":10,\"F_izq\":94,\"F_der\":121,\"tiempo_mili\":1597228959556,\"timestamp\":\"2020-08-12T10:42:39.000Z\"},{\"ID_and_s\":1,\"V_izq\":49,\"V_der\":10,\"F_izq\":94,\"F_der\":144,\"tiempo_mili\":1597228959712,\"timestamp\":\"2020-08-12T10:42:39.000Z\"},{\"ID_and_s\":1,\"V_izq\":49,\"V_der\":10,\"F_izq\":94,\"F_der\":49,\"tiempo_mili\":1597228960715,\"timestamp\":\"2020-08-12T10:42:40.000Z\"},{\"ID_and_s\":1,\"V_izq\":49,\"V_der\":10,\"F_izq\":94,\"F_der\":49,\"tiempo_mili\":1597228961556,\"timestamp\":\"2020-08-12T10:42:41.000Z\"},{\"ID_and_s\":1,\"V_izq\":49,\"V_der\":10,\"F_izq\":112,\"F_der\":49,\"tiempo_mili\":1597228961558,\"timestamp\":\"2020-08-12T10:42:41.000Z\"},{\"ID_and_s\":1,\"V_izq\":102,\"V_der\":10,\"F_izq\":112,\"F_der\":49,\"tiempo_mili\":1597228961558,\"timestamp\":\"2020-08-12T10:42:41.000Z\"},{\"ID_and_s\":1,\"V_izq\":102,\"V_der\":10,\"F_izq\":112,\"F_der\":106,\"tiempo_mili\":1597228961715,\"timestamp\":\"2020-08-12T10:42:41.000Z\"},{\"ID_and_s\":1,\"V_izq\":102,\"V_der\":10,\"F_izq\":112,\"F_der\":77,\"tiempo_mili\":1597228962720,\"timestamp\":\"2020-08-12T10:42:42.000Z\"},{\"ID_and_s\":1,\"V_izq\":102,\"V_der\":10,\"F_izq\":112,\"F_der\":77,\"tiempo_mili\":1597228963557,\"timestamp\":\"2020-08-12T10:42:43.000Z\"},{\"ID_and_s\":1,\"V_izq\":102,\"V_der\":10,\"F_izq\":50,\"F_der\":77,\"tiempo_mili\":1597228963557,\"timestamp\":\"2020-08-12T10:42:43.000Z\"},{\"ID_and_s\":1,\"V_izq\":90,\"V_der\":10,\"F_izq\":50,\"F_der\":77,\"tiempo_mili\":1597228963559,\"timestamp\":\"2020-08-12T10:42:43.000Z\"},{\"ID_and_s\":1,\"V_izq\":90,\"V_der\":10,\"F_izq\":50,\"F_der\":121,\"tiempo_mili\":1597228963721,\"timestamp\":\"2020-08-12T10:42:43.000Z\"},{\"ID_and_s\":1,\"V_izq\":90,\"V_der\":10,\"F_izq\":50,\"F_der\":133,\"tiempo_mili\":1597228964725,\"timestamp\":\"2020-08-12T10:42:44.000Z\"},{\"ID_and_s\":1,\"V_izq\":90,\"V_der\":10,\"F_izq\":50,\"F_der\":133,\"tiempo_mili\":1597228965559,\"timestamp\":\"2020-08-12T10:42:45.000Z\"},{\"ID_and_s\":1,\"V_izq\":90,\"V_der\":10,\"F_izq\":122,\"F_der\":133,\"tiempo_mili\":1597228965559,\"timestamp\":\"2020-08-12T10:42:45.000Z\"},{\"ID_and_s\":1,\"V_izq\":124,\"V_der\":10,\"F_izq\":122,\"F_der\":133,\"tiempo_mili\":1597228965561,\"timestamp\":\"2020-08-12T10:42:45.000Z\"},{\"ID_and_s\":1,\"V_izq\":124,\"V_der\":10,\"F_izq\":122,\"F_der\":118,\"tiempo_mili\":1597228965726,\"timestamp\":\"2020-08-12T10:42:45.000Z\"},{\"ID_and_s\":1,\"V_izq\":124,\"V_der\":10,\"F_izq\":122,\"F_der\":42,\"tiempo_mili\":1597228966730,\"timestamp\":\"2020-08-12T10:42:46.000Z\"},{\"ID_and_s\":1,\"V_izq\":124,\"V_der\":10,\"F_izq\":122,\"F_der\":42,\"tiempo_mili\":1597228967561,\"timestamp\":\"2020-08-12T10:42:47.000Z\"},{\"ID_and_s\":1,\"V_izq\":124,\"V_der\":10,\"F_izq\":135,\"F_der\":42,\"tiempo_mili\":1597228967561,\"timestamp\":\"2020-08-12T10:42:47.000Z\"},{\"ID_and_s\":1,\"V_izq\":111,\"V_der\":10,\"F_izq\":135,\"F_der\":42,\"tiempo_mili\":1597228967563,\"timestamp\":\"2020-08-12T10:42:47.000Z\"},{\"ID_and_s\":1,\"V_izq\":111,\"V_der\":10,\"F_izq\":135,\"F_der\":118,\"tiempo_mili\":1597228967733,\"timestamp\":\"2020-08-12T10:42:47.000Z\"},{\"ID_and_s\":1,\"V_izq\":111,\"V_der\":10,\"F_izq\":135,\"F_der\":62,\"tiempo_mili\":1597228968736,\"timestamp\":\"2020-08-12T10:42:48.000Z\"},{\"ID_and_s\":1,\"V_izq\":111,\"V_der\":10,\"F_izq\":135,\"F_der\":62,\"tiempo_mili\":1597228969563,\"timestamp\":\"2020-08-12T10:42:49.000Z\"},{\"ID_and_s\":1,\"V_izq\":111,\"V_der\":10,\"F_izq\":68,\"F_der\":62,\"tiempo_mili\":1597228969563,\"timestamp\":\"2020-08-12T10:42:49.000Z\"},{\"ID_and_s\":1,\"V_izq\":79,\"V_der\":10,\"F_izq\":68,\"F_der\":62,\"tiempo_mili\":1597228969565,\"timestamp\":\"2020-08-12T10:42:49.000Z\"},{\"ID_and_s\":1,\"V_izq\":79,\"V_der\":10,\"F_izq\":68,\"F_der\":53,\"tiempo_mili\":1597228969737,\"timestamp\":\"2020-08-12T10:42:49.000Z\"},{\"ID_and_s\":1,\"V_izq\":79,\"V_der\":10,\"F_izq\":68,\"F_der\":56,\"tiempo_mili\":1597228970741,\"timestamp\":\"2020-08-12T10:42:50.000Z\"},{\"ID_and_s\":1,\"V_izq\":79,\"V_der\":10,\"F_izq\":68,\"F_der\":56,\"tiempo_mili\":1597228971564,\"timestamp\":\"2020-08-12T10:42:51.000Z\"},{\"ID_and_s\":1,\"V_izq\":79,\"V_der\":10,\"F_izq\":131,\"F_der\":56,\"tiempo_mili\":1597228971565,\"timestamp\":\"2020-08-12T10:42:51.000Z\"},{\"ID_and_s\":1,\"V_izq\":34,\"V_der\":10,\"F_izq\":131,\"F_der\":56,\"tiempo_mili\":1597228971589,\"timestamp\":\"2020-08-12T10:42:51.000Z\"},{\"ID_and_s\":1,\"V_izq\":34,\"V_der\":10,\"F_izq\":131,\"F_der\":83,\"tiempo_mili\":1597228971743,\"timestamp\":\"2020-08-12T10:42:51.000Z\"},{\"ID_and_s\":1,\"V_izq\":34,\"V_der\":10,\"F_izq\":131,\"F_der\":61,\"tiempo_mili\":1597228972747,\"timestamp\":\"2020-08-12T10:42:52.000Z\"},{\"ID_and_s\":1,\"V_izq\":34,\"V_der\":10,\"F_izq\":131,\"F_der\":61,\"tiempo_mili\":1597228973569,\"timestamp\":\"2020-08-12T10:42:53.000Z\"},{\"ID_and_s\":1,\"V_izq\":34,\"V_der\":10,\"F_izq\":115,\"F_der\":61,\"tiempo_mili\":1597228973569,\"timestamp\":\"2020-08-12T10:42:53.000Z\"},{\"ID_and_s\":1,\"V_izq\":95,\"V_der\":10,\"F_izq\":115,\"F_der\":61,\"tiempo_mili\":1597228973573,\"timestamp\":\"2020-08-12T10:42:53.000Z\"},{\"ID_and_s\":1,\"V_izq\":95,\"V_der\":10,\"F_izq\":115,\"F_der\":53,\"tiempo_mili\":1597228973750,\"timestamp\":\"2020-08-12T10:42:53.000Z\"},{\"ID_and_s\":1,\"V_izq\":95,\"V_der\":10,\"F_izq\":115,\"F_der\":44,\"tiempo_mili\":1597228974752,\"timestamp\":\"2020-08-12T10:42:54.000Z\"}]\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":460,"y":140,"wires":[["33c08d98.3a5fb2"]]},{"id":"394e60f3.3897d","type":"debug","z":"7b63b1a9.f2278","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":870,"y":80,"wires":[]},{"id":"a7de1769.1bc388","type":"ui_group","z":"","name":"DESCARGAR DATOS A EXCEL:","tab":"74ecdcd3.dd6614","order":4,"disp":true,"width":"12","collapse":false},{"id":"74ecdcd3.dd6614","type":"ui_tab","z":"","name":"DESCARGAR DATOS","icon":"dashboard","order":1,"disabled":false,"hidden":false}]
6

See what you make of this...

image
image928×600 50.5 KB

Below is a working flow - showing you how to download a CSV file (without actually saving it - i.e. straight from a database)...

[{"id":"44c05d20.061c54","type":"csv","z":"5a19214e.b8927","name":"","sep":",","hdrin":"","hdrout":true,"multi":"mult","ret":"\\r\\n","temp":"ID_and_s,V_izq,V_der,F_izq,F_der,tiempo_mili,timestamp","skip":"0","strings":false,"x":630,"y":260,"wires":[["8dc0605f.24598"]]},{"id":"34a65b72.79dea4","type":"http response","z":"5a19214e.b8927","name":"","statusCode":"","headers":{},"x":1010,"y":260,"wires":[]},{"id":"7239ac2b.0780d4","type":"catch","z":"5a19214e.b8927","name":"","scope":null,"uncaught":false,"x":240,"y":320,"wires":[["77f0bc63.66e014","36eadc67.10b2c4"]]},{"id":"77f0bc63.66e014","type":"function","z":"5a19214e.b8927","name":"Set 404","func":"msg.payload = msg.error;\nmsg.statusCode = 404;//resource not found\nreturn msg;","outputs":1,"noerr":0,"x":800,"y":320,"wires":[["34a65b72.79dea4"]]},{"id":"36eadc67.10b2c4","type":"debug","z":"5a19214e.b8927","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":270,"y":380,"wires":[]},{"id":"1bf4dcb6.c14383","type":"ui_template","z":"5a19214e.b8927","group":"754a23fa.82988c","name":"ui_temlplate - present download link on dashboard","order":0,"width":0,"height":0,"format":"<div >\n    <a href=\"/data\">HAZ CLICK PARA DESCARGAR</a>\n</div>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":610,"y":380,"wires":[[]]},{"id":"f5c7c9b1.bdf808","type":"http in","z":"5a19214e.b8927","name":"","url":"/data","method":"get","upload":false,"swaggerDoc":"","x":240,"y":260,"wires":[["20cff092.556bc"]]},{"id":"20cff092.556bc","type":"function","z":"5a19214e.b8927","name":"** Database Call **","func":"msg.payload =[{\"ID_and_s\":1,\"V_izq\":66,\"V_der\":10,\"F_izq\":130,\"F_der\":109,\"tiempo_mili\":1597228949544,\"timestamp\":\"2020-08-12T10:42:29.000Z\"},{\"ID_and_s\":1,\"V_izq\":66,\"V_der\":10,\"F_izq\":62,\"F_der\":109,\"tiempo_mili\":1597228949544,\"timestamp\":\"2020-08-12T10:42:29.000Z\"},{\"ID_and_s\":1,\"V_izq\":58,\"V_der\":10,\"F_izq\":62,\"F_der\":109,\"tiempo_mili\":1597228949547,\"timestamp\":\"2020-08-12T10:42:29.000Z\"},{\"ID_and_s\":1,\"V_izq\":58,\"V_der\":10,\"F_izq\":62,\"F_der\":68,\"tiempo_mili\":1597228949687,\"timestamp\":\"2020-08-12T10:42:29.000Z\"},{\"ID_and_s\":1,\"V_izq\":58,\"V_der\":10,\"F_izq\":62,\"F_der\":45,\"tiempo_mili\":1597228950690,\"timestamp\":\"2020-08-12T10:42:30.000Z\"},{\"ID_and_s\":1,\"V_izq\":58,\"V_der\":10,\"F_izq\":62,\"F_der\":45,\"tiempo_mili\":1597228951545,\"timestamp\":\"2020-08-12T10:42:31.000Z\"},{\"ID_and_s\":1,\"V_izq\":58,\"V_der\":10,\"F_izq\":58,\"F_der\":45,\"tiempo_mili\":1597228951546,\"timestamp\":\"2020-08-12T10:42:31.000Z\"},{\"ID_and_s\":1,\"V_izq\":22,\"V_der\":10,\"F_izq\":58,\"F_der\":45,\"tiempo_mili\":1597228951548,\"timestamp\":\"2020-08-12T10:42:31.000Z\"},{\"ID_and_s\":1,\"V_izq\":22,\"V_der\":10,\"F_izq\":58,\"F_der\":83,\"tiempo_mili\":1597228951692,\"timestamp\":\"2020-08-12T10:42:31.000Z\"},{\"ID_and_s\":1,\"V_izq\":22,\"V_der\":10,\"F_izq\":58,\"F_der\":73,\"tiempo_mili\":1597228952695,\"timestamp\":\"2020-08-12T10:42:32.000Z\"},{\"ID_and_s\":1,\"V_izq\":22,\"V_der\":10,\"F_izq\":58,\"F_der\":73,\"tiempo_mili\":1597228953548,\"timestamp\":\"2020-08-12T10:42:33.000Z\"},{\"ID_and_s\":1,\"V_izq\":22,\"V_der\":10,\"F_izq\":79,\"F_der\":73,\"tiempo_mili\":1597228953548,\"timestamp\":\"2020-08-12T10:42:33.000Z\"},{\"ID_and_s\":1,\"V_izq\":97,\"V_der\":10,\"F_izq\":79,\"F_der\":73,\"tiempo_mili\":1597228953550,\"timestamp\":\"2020-08-12T10:42:33.000Z\"},{\"ID_and_s\":1,\"V_izq\":97,\"V_der\":10,\"F_izq\":79,\"F_der\":114,\"tiempo_mili\":1597228953696,\"timestamp\":\"2020-08-12T10:42:33.000Z\"},{\"ID_and_s\":1,\"V_izq\":97,\"V_der\":10,\"F_izq\":79,\"F_der\":126,\"tiempo_mili\":1597228954701,\"timestamp\":\"2020-08-12T10:42:34.000Z\"},{\"ID_and_s\":1,\"V_izq\":97,\"V_der\":10,\"F_izq\":79,\"F_der\":126,\"tiempo_mili\":1597228955549,\"timestamp\":\"2020-08-12T10:42:35.000Z\"},{\"ID_and_s\":1,\"V_izq\":97,\"V_der\":10,\"F_izq\":124,\"F_der\":126,\"tiempo_mili\":1597228955550,\"timestamp\":\"2020-08-12T10:42:35.000Z\"},{\"ID_and_s\":1,\"V_izq\":68,\"V_der\":10,\"F_izq\":124,\"F_der\":126,\"tiempo_mili\":1597228955552,\"timestamp\":\"2020-08-12T10:42:35.000Z\"},{\"ID_and_s\":1,\"V_izq\":68,\"V_der\":10,\"F_izq\":124,\"F_der\":101,\"tiempo_mili\":1597228955702,\"timestamp\":\"2020-08-12T10:42:35.000Z\"},{\"ID_and_s\":1,\"V_izq\":68,\"V_der\":10,\"F_izq\":124,\"F_der\":69,\"tiempo_mili\":1597228956706,\"timestamp\":\"2020-08-12T10:42:36.000Z\"},{\"ID_and_s\":1,\"V_izq\":68,\"V_der\":10,\"F_izq\":124,\"F_der\":69,\"tiempo_mili\":1597228957553,\"timestamp\":\"2020-08-12T10:42:37.000Z\"},{\"ID_and_s\":1,\"V_izq\":68,\"V_der\":10,\"F_izq\":57,\"F_der\":69,\"tiempo_mili\":1597228957555,\"timestamp\":\"2020-08-12T10:42:37.000Z\"},{\"ID_and_s\":1,\"V_izq\":144,\"V_der\":10,\"F_izq\":57,\"F_der\":69,\"tiempo_mili\":1597228957556,\"timestamp\":\"2020-08-12T10:42:37.000Z\"},{\"ID_and_s\":1,\"V_izq\":144,\"V_der\":10,\"F_izq\":57,\"F_der\":31,\"tiempo_mili\":1597228957707,\"timestamp\":\"2020-08-12T10:42:37.000Z\"},{\"ID_and_s\":1,\"V_izq\":144,\"V_der\":10,\"F_izq\":57,\"F_der\":121,\"tiempo_mili\":1597228958710,\"timestamp\":\"2020-08-12T10:42:38.000Z\"},{\"ID_and_s\":1,\"V_izq\":144,\"V_der\":10,\"F_izq\":57,\"F_der\":121,\"tiempo_mili\":1597228959554,\"timestamp\":\"2020-08-12T10:42:39.000Z\"},{\"ID_and_s\":1,\"V_izq\":144,\"V_der\":10,\"F_izq\":94,\"F_der\":121,\"tiempo_mili\":1597228959556,\"timestamp\":\"2020-08-12T10:42:39.000Z\"},{\"ID_and_s\":1,\"V_izq\":49,\"V_der\":10,\"F_izq\":94,\"F_der\":121,\"tiempo_mili\":1597228959556,\"timestamp\":\"2020-08-12T10:42:39.000Z\"},{\"ID_and_s\":1,\"V_izq\":49,\"V_der\":10,\"F_izq\":94,\"F_der\":144,\"tiempo_mili\":1597228959712,\"timestamp\":\"2020-08-12T10:42:39.000Z\"},{\"ID_and_s\":1,\"V_izq\":49,\"V_der\":10,\"F_izq\":94,\"F_der\":49,\"tiempo_mili\":1597228960715,\"timestamp\":\"2020-08-12T10:42:40.000Z\"},{\"ID_and_s\":1,\"V_izq\":49,\"V_der\":10,\"F_izq\":94,\"F_der\":49,\"tiempo_mili\":1597228961556,\"timestamp\":\"2020-08-12T10:42:41.000Z\"},{\"ID_and_s\":1,\"V_izq\":49,\"V_der\":10,\"F_izq\":112,\"F_der\":49,\"tiempo_mili\":1597228961558,\"timestamp\":\"2020-08-12T10:42:41.000Z\"},{\"ID_and_s\":1,\"V_izq\":102,\"V_der\":10,\"F_izq\":112,\"F_der\":49,\"tiempo_mili\":1597228961558,\"timestamp\":\"2020-08-12T10:42:41.000Z\"},{\"ID_and_s\":1,\"V_izq\":102,\"V_der\":10,\"F_izq\":112,\"F_der\":106,\"tiempo_mili\":1597228961715,\"timestamp\":\"2020-08-12T10:42:41.000Z\"},{\"ID_and_s\":1,\"V_izq\":102,\"V_der\":10,\"F_izq\":112,\"F_der\":77,\"tiempo_mili\":1597228962720,\"timestamp\":\"2020-08-12T10:42:42.000Z\"},{\"ID_and_s\":1,\"V_izq\":102,\"V_der\":10,\"F_izq\":112,\"F_der\":77,\"tiempo_mili\":1597228963557,\"timestamp\":\"2020-08-12T10:42:43.000Z\"},{\"ID_and_s\":1,\"V_izq\":102,\"V_der\":10,\"F_izq\":50,\"F_der\":77,\"tiempo_mili\":1597228963557,\"timestamp\":\"2020-08-12T10:42:43.000Z\"},{\"ID_and_s\":1,\"V_izq\":90,\"V_der\":10,\"F_izq\":50,\"F_der\":77,\"tiempo_mili\":1597228963559,\"timestamp\":\"2020-08-12T10:42:43.000Z\"},{\"ID_and_s\":1,\"V_izq\":90,\"V_der\":10,\"F_izq\":50,\"F_der\":121,\"tiempo_mili\":1597228963721,\"timestamp\":\"2020-08-12T10:42:43.000Z\"},{\"ID_and_s\":1,\"V_izq\":90,\"V_der\":10,\"F_izq\":50,\"F_der\":133,\"tiempo_mili\":1597228964725,\"timestamp\":\"2020-08-12T10:42:44.000Z\"},{\"ID_and_s\":1,\"V_izq\":90,\"V_der\":10,\"F_izq\":50,\"F_der\":133,\"tiempo_mili\":1597228965559,\"timestamp\":\"2020-08-12T10:42:45.000Z\"},{\"ID_and_s\":1,\"V_izq\":90,\"V_der\":10,\"F_izq\":122,\"F_der\":133,\"tiempo_mili\":1597228965559,\"timestamp\":\"2020-08-12T10:42:45.000Z\"},{\"ID_and_s\":1,\"V_izq\":124,\"V_der\":10,\"F_izq\":122,\"F_der\":133,\"tiempo_mili\":1597228965561,\"timestamp\":\"2020-08-12T10:42:45.000Z\"},{\"ID_and_s\":1,\"V_izq\":124,\"V_der\":10,\"F_izq\":122,\"F_der\":118,\"tiempo_mili\":1597228965726,\"timestamp\":\"2020-08-12T10:42:45.000Z\"},{\"ID_and_s\":1,\"V_izq\":124,\"V_der\":10,\"F_izq\":122,\"F_der\":42,\"tiempo_mili\":1597228966730,\"timestamp\":\"2020-08-12T10:42:46.000Z\"},{\"ID_and_s\":1,\"V_izq\":124,\"V_der\":10,\"F_izq\":122,\"F_der\":42,\"tiempo_mili\":1597228967561,\"timestamp\":\"2020-08-12T10:42:47.000Z\"},{\"ID_and_s\":1,\"V_izq\":124,\"V_der\":10,\"F_izq\":135,\"F_der\":42,\"tiempo_mili\":1597228967561,\"timestamp\":\"2020-08-12T10:42:47.000Z\"},{\"ID_and_s\":1,\"V_izq\":111,\"V_der\":10,\"F_izq\":135,\"F_der\":42,\"tiempo_mili\":1597228967563,\"timestamp\":\"2020-08-12T10:42:47.000Z\"},{\"ID_and_s\":1,\"V_izq\":111,\"V_der\":10,\"F_izq\":135,\"F_der\":118,\"tiempo_mili\":1597228967733,\"timestamp\":\"2020-08-12T10:42:47.000Z\"},{\"ID_and_s\":1,\"V_izq\":111,\"V_der\":10,\"F_izq\":135,\"F_der\":62,\"tiempo_mili\":1597228968736,\"timestamp\":\"2020-08-12T10:42:48.000Z\"},{\"ID_and_s\":1,\"V_izq\":111,\"V_der\":10,\"F_izq\":135,\"F_der\":62,\"tiempo_mili\":1597228969563,\"timestamp\":\"2020-08-12T10:42:49.000Z\"},{\"ID_and_s\":1,\"V_izq\":111,\"V_der\":10,\"F_izq\":68,\"F_der\":62,\"tiempo_mili\":1597228969563,\"timestamp\":\"2020-08-12T10:42:49.000Z\"},{\"ID_and_s\":1,\"V_izq\":79,\"V_der\":10,\"F_izq\":68,\"F_der\":62,\"tiempo_mili\":1597228969565,\"timestamp\":\"2020-08-12T10:42:49.000Z\"},{\"ID_and_s\":1,\"V_izq\":79,\"V_der\":10,\"F_izq\":68,\"F_der\":53,\"tiempo_mili\":1597228969737,\"timestamp\":\"2020-08-12T10:42:49.000Z\"},{\"ID_and_s\":1,\"V_izq\":79,\"V_der\":10,\"F_izq\":68,\"F_der\":56,\"tiempo_mili\":1597228970741,\"timestamp\":\"2020-08-12T10:42:50.000Z\"},{\"ID_and_s\":1,\"V_izq\":79,\"V_der\":10,\"F_izq\":68,\"F_der\":56,\"tiempo_mili\":1597228971564,\"timestamp\":\"2020-08-12T10:42:51.000Z\"},{\"ID_and_s\":1,\"V_izq\":79,\"V_der\":10,\"F_izq\":131,\"F_der\":56,\"tiempo_mili\":1597228971565,\"timestamp\":\"2020-08-12T10:42:51.000Z\"},{\"ID_and_s\":1,\"V_izq\":34,\"V_der\":10,\"F_izq\":131,\"F_der\":56,\"tiempo_mili\":1597228971589,\"timestamp\":\"2020-08-12T10:42:51.000Z\"},{\"ID_and_s\":1,\"V_izq\":34,\"V_der\":10,\"F_izq\":131,\"F_der\":83,\"tiempo_mili\":1597228971743,\"timestamp\":\"2020-08-12T10:42:51.000Z\"},{\"ID_and_s\":1,\"V_izq\":34,\"V_der\":10,\"F_izq\":131,\"F_der\":61,\"tiempo_mili\":1597228972747,\"timestamp\":\"2020-08-12T10:42:52.000Z\"},{\"ID_and_s\":1,\"V_izq\":34,\"V_der\":10,\"F_izq\":131,\"F_der\":61,\"tiempo_mili\":1597228973569,\"timestamp\":\"2020-08-12T10:42:53.000Z\"},{\"ID_and_s\":1,\"V_izq\":34,\"V_der\":10,\"F_izq\":115,\"F_der\":61,\"tiempo_mili\":1597228973569,\"timestamp\":\"2020-08-12T10:42:53.000Z\"},{\"ID_and_s\":1,\"V_izq\":95,\"V_der\":10,\"F_izq\":115,\"F_der\":61,\"tiempo_mili\":1597228973573,\"timestamp\":\"2020-08-12T10:42:53.000Z\"},{\"ID_and_s\":1,\"V_izq\":95,\"V_der\":10,\"F_izq\":115,\"F_der\":53,\"tiempo_mili\":1597228973750,\"timestamp\":\"2020-08-12T10:42:53.000Z\"},{\"ID_and_s\":1,\"V_izq\":95,\"V_der\":10,\"F_izq\":115,\"F_der\":44,\"tiempo_mili\":1597228974752,\"timestamp\":\"2020-08-12T10:42:54.000Z\"}]\nreturn msg;","outputs":1,"noerr":0,"x":430,"y":260,"wires":[["44c05d20.061c54"]]},{"id":"8dc0605f.24598","type":"function","z":"5a19214e.b8927","name":"Set Headers","func":"msg.headers = {}\nmsg.headers[\"Content-type\"] = \"text/csv\";\nmsg.headers[\"Content-Disposition\"] = \"attachment; filename=data.csv\";\nmsg.headers[\"Pragma\"] = \"no-cache\";\nmsg.headers[\"Expires\"] = \"0\";\nreturn msg;","outputs":1,"noerr":0,"x":790,"y":260,"wires":[["34a65b72.79dea4"]]},{"id":"ee46742a.c9c988","type":"comment","z":"5a19214e.b8927","name":"Replace \"Database Call\" with your database calls like i detailed in the screenshot on the forum","info":"","x":560,"y":200,"wires":[]},{"id":"754a23fa.82988c","type":"ui_group","z":"","name":"DESCARGAR DATOS A EXCEL:","tab":"be2f7523.3a90c8","order":4,"disp":true,"width":"12","collapse":false},{"id":"be2f7523.3a90c8","type":"ui_tab","z":"","name":"DESCARGAR DATOS","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

You will need to integrate this with your database calls - as I describe in the screen shot comments.

7

Thanks!
I will work it out tomorrow morning and let you know :slight_smile:

8

Good morning!

Thanks very much, it works. So, as far as I understand, filling up the form will storage the data in the flow variables, and its not until the user presses the link when the query its made and the csv is created. Am I right?

And how can I make the file be automatically downloaded when the user presses the "DOWNLOAD" button that its included in the form (called "Selección de fechas"). I have used the debug node so I could see how to interact with that button but I don´t see anything. My guess is that I need to use a change node to change some property of the button for the download link itself so the http request triggers, but I dont see how.

9

That is not so easy (though not impossible but would require some "hacks" at the client side via a ui_template and some JS to intercept the button click)

Consider this...

  • The button on the "form" triggers a server side (node-red) event.
  • The download link is a standard html anchor that causes your browser to go fetch the data from your endpoint

You would be better off replacing the form node (Seleccion de fechas) with 2 separate ui_date_picker fields. You can also use CSS styling in the ui_template to style the download <a> anchor to look like a "download button"

This is how you could use separate date pickers & store the values in flow....
image

10

Ok, I will work it out :slight_smile:

Just one last thing, it is completly off-topic but I don´t see the point on creating a new thread for this bc is more JS related than node-red related.

Im getting the following error when trying to insert the dates after picking them with the form in my data base:

ER_TRUNCATED_WRONG_VALUE : Incorrect date value : '2020-10-10T06:50:04.006Z for column 'Inicio' at row 3
(I don´t know why it shows the hour since the form doesn´t ask for it).

My database column is set on "date".
I have been researching about it for 2 days and trying different stuff, but I don´t get to find the right solution and so since this thread is being so helpful, would you mind giving me some hints? And excuse me if I shouldn´t ask this here.

Once answered, from my side I consider this thread closed :slight_smile:
Thanks for everything!

11

Your date should probably be formatted as YYYY-MM-DD

Depending on your node-red version you can use a change node with JSONata and $moment (but I dont know how to do that).

Instead, in a function node (just before you set the flow.startDate and flow.endDate) you could add a function that does something like...

const dt = new Date(msg.payload);//create a date object from the payload of the datepicker
const dd = dt.getDay(); //get the day of month
const mm = dt.getMonth() + 1; // get month (starts at 0 so we have to add 1).
const yyyy = dt.getUTCFullYear(); // get year
msg.payload = `${yyyy}-${mm}-${dd}`; //format the string as "yyyy-mm-dd" suitable for DB INSERT
return msg;

NOTE: this is untested - use debug nodes to check your work :slight_smile:

12

Okey, thanks!
I will let you know if it works.

Have a good day!

13

Hello!

It throws 'NaN-NaN-NaN' string. The $ simbol is meant to access variable eventhough you are writing a string, right?

Correction: I was just missing the correct name of my payload (i didn´t realized I had a topic asigned, so it was not msg.payload but msg.payload.fin)

It works, thanks!

14

Hello again.

I have an issue regarding the flow variable, as when I call it from the function that generates the query, instead of the value that should carry, its content its [object OBJECT]. Why this can be happenning? beacuse if I call the flow variable from another functions it gets the proper value.

Just simulated the flow here:

[{"id":"7dbf39ab.a7ef58","type":"tab","label":"Flow 3","disabled":false,"info":""},{"id":"78bff6e7.9f22c8","type":"change","z":"7dbf39ab.a7ef58","name":"","rules":[{"t":"set","p":"flow","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":350,"y":240,"wires":[[]]},{"id":"b7c2a152.fa233","type":"function","z":"7dbf39ab.a7ef58","name":"Asignación del paciente y el andador","func":"\nvar id_and = msg.payload.and;\nvar idpacs = flow.get(\"flow\")\n\nconst dt = new Date(msg.payload.fecha); //create a date object from the payload of the datepicker\nconst dd = dt.getDay(); //get the day of month\nconst mm = dt.getMonth() + 1; // get month (starts at 0 so we have to add 1).\nconst yyyy = dt.getUTCFullYear(); // get year\n\nvar inicio = `${yyyy}-${mm}-${dd}`;\n\n\nmsg.topic = \"INSERT INTO Andadores (ID_and,ID_pac,Inicio)\"+ \"VALUES (\"+id_and+\",\"+idpacs+\",\"+inicio+\")\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":450,"y":340,"wires":[["2fcabd42.17b822"]]},{"id":"2fcabd42.17b822","type":"debug","z":"7dbf39ab.a7ef58","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":710,"y":340,"wires":[]},{"id":"7f0216d6.f990a8","type":"inject","z":"7dbf39ab.a7ef58","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"nombre\":\"Pablo\",\"dni\":\"7273781R\",\"and\":54,\"lesion\":\"Cadera\",\"fecha\":\"2020-07-26T22:00:00.000Z\"}","payloadType":"jsonata","x":210,"y":340,"wires":[["b7c2a152.fa233"]]},{"id":"68b26c81.1f1a84","type":"inject","z":"7dbf39ab.a7ef58","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"payload\":[{\"ID_pac\":26}],\"topic\":\"SELECT ID_pac FROM Pacientes WHERE Nombre = 'Pablo' AND DNI = '7273781R' \",\"socketid\":\"dJZuenEH9bDaQUimAAAA\",\"_msgid\":\"d4320f63.d5e99\"}","payloadType":"jsonata","x":190,"y":240,"wires":[["78bff6e7.9f22c8"]]},{"id":"cafa4b9d.cde428","type":"function","z":"7dbf39ab.a7ef58","name":"Asignación del paciente y el andador","func":"msg.payload = flow.get(\"flow\");\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":570,"y":140,"wires":[["ce7b1d9.61cc7e"]]},{"id":"d5d80ce8.ab531","type":"inject","z":"7dbf39ab.a7ef58","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":330,"y":140,"wires":[["cafa4b9d.cde428"]]},{"id":"ce7b1d9.61cc7e","type":"debug","z":"7dbf39ab.a7ef58","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":810,"y":140,"wires":[]}]

FLOW

1
11147×477 19.3 KB

If I call the variable from the function that does the query:
2
If I call it from a function that just returns its value:
3

15

Pablo, repost your last flow because it only includes a debug node :wink:

you must have selected a single node and only that selected one was exported

16

Ups. Done. Thanks for the warning.

17

Because in this 'test' flow you are setting payload.payload and also in an array just for storing ID_pac in context ? (with the second inject node) Is this the structure of your data ?

18

Yes it is the structure on the real flow. I don´t know why it gets stored that way.
My flow is the following:

d
d1633×634 72 KB

That weird data structure comes from the part within the circle. Basically, I have a form and due to the structure of the DB, I have to store first the user info. ID is autoincremental so it gets generated automatically. For that reason, I have to query it to know which is the ID, so I can use it for storing the other part of the data in another table... And that is the variable set in context.

19

You can access it like this :


var id_and = msg.payload.and;
var idpacs = flow.get("flow")  
idpacs = idpacs.payload[0].ID_pac   // here
...
var inicio = `${yyyy}-${mm}-${dd}`;


msg.topic = "INSERT INTO Andadores (ID_and,ID_pac,Inicio)"+ "VALUES ("+id_and+","+idpacs+","+inicio+")";
return msg;

because the stucture of your context is :

Screenshot_2020-08-21 Node-RED 192 168 1 160

20

Yes makes sense,but I have deployed a debug node after the function node and if I do that, the function doesn´t return the mesage.