i have one more question 
how can i limit/change latitude, longitude, accuracy to 2 decimal places in the report?
i had the same problem with some tempratures that i was saving in my db as a full float
decided that it would be cleaner to do it from the sqlite sql query
Example :
msg.topic = `SELECT round(latitude, 2) as lat, round(longitude, 2) as lon FROM yourTable LIMIT 10`
1 Like
Many thanks.
Works perfectly and yes it is a better idea to limit it with the query.
Thanks a lot.
Though 2decimal places for lat and lon isn’t very accurate
Good Morning.
Can you kindly assist me again please. I have added a second table to the report that i want to populate with a second SQL query.
How do i get the second SQL query's data to the second table?
Again many thanks.
Run your sql queries, saving each to a separate msg property or context, then you would add each saved sql return to the table as shown above. Instead of msg.payload use the saved msg property, i.e msg.sqlquery1[0] and msg.sqlquery1, where sqlquery1 is where you saved the sql query result.
That's exactly the part that i cant figure out
I use a "Edit sqlite node" to run the queries. How do i change the msg.payloads. The change node does not want to work.
sql query 1 > move msg.payload to msg.query1 > sql query 2 > create pdf use msg.query1 for first table, use msg.payload for second table.
If your change node will not move the payload then you should report an issue.
If msg.query1 is missing after second query then try saving to context instead, and report that the sql node is overwriting the original msg to the node author
I think i have figured out why i cant get the second table working
How do i modify this portion of the code to accommodate msg.query1 with msg.payload and any additional tables that i want to create?
...
};
Object.values(msg.payload).forEach(obj =>{
payload.content[1].table.body.push(Object.values(obj));
})
msg.payload = payload;
return msg;
...
IF you are pushing the the table rows to the content table body, it would be something like this
//push query1 to content[1]
Object.values(msg.query1).forEach(obj =>{
payload.content[1].table.body.push(Object.values(obj));
})
//push payload to content[ index of second table
// i have guessed as to it's location [6]
Object.values(msg.payload).forEach(obj => {
payload.content[6].table.body.push(Object.values(obj));
})
msg.payload = payload;
return msg;
1 Like
I tried this, and may be something was wrong in my adaptation to my flow, and it didn't work,
i will figure it out,
but here the question is, if a pdfmake fails for some reason, is it normal that is stops the flow the error is stopping my windows service, is this expected?
No. The node should handle it's errors and not kill node-red.
You should raise an issue on the repository and providers much detail as possible (there will be an error stack log in the console when the process crashes. I realise you are running as a service so you may not have visibility of the console or logs saved to file. So instead of running the service, you should run node red in a terminal so that you can see the error stack and provide this on the repository issue that you raise)
Example of two separate queries joined with a Join node and modified code in Function node
(Topic could not be used to join the messages as that was used for the sql query .. used key instead )
[{"id":"ec950ec457b45f9e","type":"function","z":"54efb553244c241f","name":"data to pdf","func":"let tableData = {\n\n pageSize: 'A4',\n pageOrientation: 'landscape',\n content: [\n\n { text: 'This is my Sqlite Table from Query 1:', fontSize: 14, bold: true, margin: [0, 20, 0, 8] },\n\n {\n style: 'tableExample',\n table: {\n headerRows: 1,\n body: [\n Object.keys(msg.payload.query1[0]).map(key => { return { text: key, style: 'tableHeader' } }),\n ...msg.payload.query1.map(obj => Object.values(obj))\n ]\n },\n layout: 'lightHorizontalLines'\n },\n\n { text: 'This is my Sqlite Table from Query 2:', fontSize: 14, bold: true, margin: [0, 20, 0, 8] },\n \n {\n style: 'tableExample',\n table: {\n headerRows: 1,\n body: [\n Object.keys(msg.payload.query2[0]).map(key => { return { text: key, style: 'tableHeader' } }),\n ...msg.payload.query2.map(obj => Object.values(obj))\n ]\n },\n layout: 'lightHorizontalLines'\n }\n ],\n styles: {\n header: {\n fontSize: 18,\n bold: true,\n margin: [0, 0, 0, 10]\n },\n subheader: {\n fontSize: 16,\n bold: true,\n margin: [0, 10, 0, 5]\n },\n tableExample: {\n margin: [0, 5, 0, 15]\n },\n tableHeader: {\n bold: true,\n fontSize: 13,\n color: 'black'\n }\n },\n defaultStyle: {\n // alignment: 'justify'\n }\n\n};\n\nmsg.payload = tableData;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":830,"y":720,"wires":[["e2dc81e103eb3abd","2c4abf85948ac92c"]]},{"id":"a29425b024fb291c","type":"join","z":"54efb553244c241f","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"key","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":670,"y":720,"wires":[["9dfdbc67b342674b","ec950ec457b45f9e"]]},{"id":"e2dc81e103eb3abd","type":"debug","z":"54efb553244c241f","name":"debug 10","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":960,"y":660,"wires":[]},{"id":"2c4abf85948ac92c","type":"pdfmake","z":"54efb553244c241f","name":"","outputType":"Buffer","inputProperty":"payload","options":"{}","outputProperty":"payload","x":1020,"y":720,"wires":[["3f2acb163d057cda"]]},{"id":"61b2b64dbecdd02f","type":"sqlite","z":"54efb553244c241f","mydb":"3901692ba9c81a0f","sqlquery":"msg.topic","sql":"","name":"Sqlite","x":530,"y":720,"wires":[["a29425b024fb291c"]]},{"id":"9dfdbc67b342674b","type":"debug","z":"54efb553244c241f","name":"debug 14","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":720,"y":640,"wires":[]},{"id":"3f2acb163d057cda","type":"file","z":"54efb553244c241f","name":"","filename":"c:/share/pdfTest.pdf","filenameType":"str","appendNewline":false,"createDir":false,"overwriteFile":"true","encoding":"none","x":1220,"y":720,"wires":[[]]},{"id":"184a494dca2081dc","type":"function","z":"54efb553244c241f","name":"sql query1","func":"\nlet columns = [\"TS01\", \"TS02\", \"TS03\", \"TS04\", \"TS05\", \"TS06\", \"TS07\", \"TS08\", \"TS09\", \"TS10\", \"TS11\", \"TS12\"]\ncolumns = columns.map(el => `round(${el}, 2) as ${el}`).join(\",\")\n\nmsg.topic = `SELECT dt, ${columns} FROM Temp LIMIT 10`\nmsg.key = \"query1\"\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"moment","module":"moment"}],"x":350,"y":680,"wires":[["61b2b64dbecdd02f"]]},{"id":"ac089368684fb955","type":"function","z":"54efb553244c241f","name":"sql query2","func":"\nlet columns = [\"FM01\", \"FM02\", \"FM03\", \"FM04\", \"FM05\", \"FM06\", \"FM07\"]\ncolumns = columns.map(el => `round(${el}, 2) as ${el}`).join(\",\")\n\nmsg.topic = `SELECT dt, ${columns} FROM Flow LIMIT 10`\nmsg.key = \"query2\"\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"moment","module":"moment"}],"x":350,"y":780,"wires":[["61b2b64dbecdd02f"]]},{"id":"0f33788a7b027c5a","type":"inject","z":"54efb553244c241f","name":"","props":[{"p":"time","v":"","vt":"date"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":150,"y":680,"wires":[["184a494dca2081dc"]]},{"id":"18da12bf2f07d832","type":"inject","z":"54efb553244c241f","name":"","props":[{"p":"time","v":"","vt":"date"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":150,"y":780,"wires":[["ac089368684fb955"]]},{"id":"3901692ba9c81a0f","type":"sqlitedb","db":"C:\\Share\\easyio.db","mode":"RWC"}]
1 Like
Thanks for this. I was struggling to get TWO tables into a PDF.
I am also having one more issue, unable to pass on a dynamic value into the page title. I want to get a report date to the header of the PDF page, there are several examples in the forum to get a static value (hard coded into function node), but i want a message passed on into the pdfmake say with an inject or a change node . for example msg.header = (Report Date &<selected_date>).
the answer is there in the below image, but i cannot understand it.
This is working,
Many thanks this works
1 Like
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
