Create a Report from MYSQL

[Shadow20X6]

Hello all.

I have a test MYSQL server that I use Node Red to Format CSVs I generate from my programs before it uploads to the database.

I'm interested in a method of generating PDF Reports partially based on this data.

Before I was using Word Mail Merge from A CSV node Red would make during the upload.

Now I've got a nearly finished Report in Microsoft Access that prompts for which batch you want a report on, then it generates a report for you.

Before i commit to doing all of my reports on the data in Access, I was looking for a solution from Node Red to automate the generation of the reports from Node Red

I.e., after it has uploaded all the data it runs some Exec or maybe a VBA palette to have Access create the reports, which get saved into a certain folder.

Once Node Red Receives all the files, it sends an alert that the task is done.

If this isn't possible in MS Access, are there any other programs for building Reports from a Database that would be better for automation with Node Red?

Also the reports need to be able to format for labels. Often times I'm generating 60 per page labels with data from the database.

Ideally I want the final reports to be PDFs, but if we can automate it, I'm a little more flexible.

[dceejay]

If you search the flows site there are several nodes - eg node-red-contrib-pdfmake (node) - Node-RED that can help you generate pdf directly. I personally haven't used them but sarching this forum you can find several other discussions.

[smanjunath211]

I have had to make a report from mysql database and have been successfully doing so after many interactions with this Forum , if you provide basic data, i can try to modify my flow to suit your need , unfortunately i cannot share my entire flow, but can try to make a simple flow and share, will take some time. I am definitely not fluent in this, but have managed to adapt the samples given by the pdfmake node and interacting with forum.

if you can paste the output of your mysql query, it will help me construct a simple flow for pdf.

[TotallyInformation]

Hmm, I'd forgotten about pdfmaker - just looking at their website, I find their examples use a config-driven approach remarkably similar to uibuilder's config-driven, low-code approach. So I think I need to recreate some of their examples.

It should, I believe, be possible to create web pages dynamically from uibuilder in the same way as pdfmaker.

[TotallyInformation]

And indeed I was correct! Not quite a complete copy yet of the tables example from the pdfmaker site but close enough to show off the possibilities. I'll finish it off when I can and submit to the Flows site.

But if you'd care to try it, this might be a useful alternative to pdfmaker since most OS's these days will let you print to PDF.

[{"id":"279f069488faedf5","type":"uibuilder","z":"a70b22bf53a7ea9c","name":"","topic":"","url":"ui-tests","fwdInMessages":false,"allowScripts":false,"allowStyles":false,"copyIndex":true,"templateFolder":"iife-blank-client","extTemplate":"","showfolder":false,"reload":true,"sourceFolder":"src","deployedVersion":"6.1.0","showMsgUib":true,"x":440,"y":160,"wires":[["2c2f7fac82a12c69"],["df4025edc7711edd"]],"info":"This example uses a blank template with\r\nthe IIFE build of the front-end client.\r\n\r\nIt does not use any front-end framework, just\r\npure HTML, CSS and JavaScript.\r\n\r\nThe IIFE build should be included using a link\r\ntag in your HTML."},{"id":"1b2196df2b92f409","type":"inject","z":"a70b22bf53a7ea9c","name":"Send a msg","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"A Message From Node-RED","payload":"","payloadType":"date","x":180,"y":180,"wires":[["d621dd0923f1e188"]],"info":"Send a simply msg to the front-end.\r\n\r\nThe default front-end template code will display the msg\r\nusing HTML formatting, no coding required."},{"id":"767ae0f52380b47d","type":"inject","z":"a70b22bf53a7ea9c","name":"Reload","props":[{"p":"_ui","v":"{\"method\":\"reload\"}","vt":"json"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"reload","x":160,"y":220,"wires":[["d621dd0923f1e188"]],"info":"Sends a pre-formatted msg to the front-end that\r\ncauses the page to reload itself."},{"id":"e835cac509084abe","type":"inject","z":"a70b22bf53a7ea9c","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"\"This is the payload from the inject node! Random number: \" & $formatInteger($random()*100, \"0\")","payloadType":"jsonata","x":125,"y":100,"wires":[["ada5575de6d2dc38"]],"l":false},{"id":"ada5575de6d2dc38","type":"function","z":"a70b22bf53a7ea9c","name":"Notification","func":"msg = {\n  \"_uib\": {\n    // This can actually be anything, if it doesn't exist, \n    // the toast will appear in the default location\n    \"componentRef\": \"globalNotification\",\n    // Note that most if not all of these are optional\n    \"options\": {\n      // These can contain HTML - note the inclusion of the payload from the upstram msg\n      \"title\": \"This is the <i>title</i>\",\n      \"content\": `This is content <span style=\\\"color:red;\\\">in addition to</span> the payload<p>${msg.payload}</p>`,\n            \n      // Use 1 of the following 2 - click msg if no auto hide:\n      \"autoHideDelay\": 2500,\n      // \"noAutoHide\": true,\n\n      // If false or not included, msgs stack above each other.\n      \"appendToast\": true,\n\n      // See \"Recommended surfaces\" in uib-brand.css. Normally\n      // 'primary', 'secondary', 'success', 'info', 'warn', 'warning', 'failure', 'error', 'danger'\n      \"variant\": \"info\",\n    }\n  }\n}\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":240,"y":100,"wires":[["d621dd0923f1e188"]],"info":"Overlays a message on top of your UI.\r\n\r\nThe message removes itself after a couple of seconds.\r\n\r\nYou can change the options property to change the look\r\nof the displayed message."},{"id":"96f771bbdcc816fa","type":"inject","z":"a70b22bf53a7ea9c","name":"","props":[],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":125,"y":140,"wires":[["961d42bbb9613525"]],"l":false},{"id":"961d42bbb9613525","type":"function","z":"a70b22bf53a7ea9c","name":"New Card","func":"let cardCounter = context.get('cardCounter') ?? 0\n\nmsg = {\n    \"_ui\": [\n        {\n            \"method\": \"remove\",\n            \"components\": [\n                \"#mycard\"\n            ]\n        },\n        {\n            \"method\": \"add\",\n            \"parent\": \"#more\",\n            \"components\": [\n                {\n                    \"type\": \"div\",\n                    \"attributes\": {\n                        \"id\": \"mycard\",\n                        \"title\": \"This is my Card\",\n                        \"style\": \"max-width: 20rem;border:solid silver 1px;margin-bottom:1rem;\",\n                    },\n                    \"components\": [\n                        {\n                            \"type\": \"h2\",\n                            \"slot\": \"A New Card\",\n                            \"attributes\": {\n                                \"class\": \"complementary\",\n                                \"style\": \"text-align:center;margin-top:0;\"\n                            }\n                        },\n                        {\n                            \"type\": \"p\",\n                            \"slot\": \"Some text in a paragraph.\"\n                        },\n                        {\n                            \"type\": \"p\",\n                            \"slot\": \"Another paragraph. Count: \" + ++cardCounter\n                        }\n                    ]\n                }\n            ],\n        }\n    ]\n}\ncontext.set('cardCounter', cardCounter)\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":230,"y":140,"wires":[["d621dd0923f1e188"]],"info":"Inserts a pure HTML \"card\" into a div called `#more`.\r\nIf that div does not exist, will add to the bottom of the HTML.\r\n\r\nFirstly attempts to remove the div so that you only ever have 1.\r\n\r\nAn example of using uibuilder's dynamic UI configuration-driven\r\nbuilding capabilities without the need for any fancy nodes or\r\nframeworks. Pure HTML. But you can still utilise the extra\r\nfeatures of your favourite framework too if you like!"},{"id":"2c2f7fac82a12c69","type":"debug","z":"a70b22bf53a7ea9c","name":"uibuilder standard output","active":false,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"","statusType":"counter","x":655,"y":120,"wires":[],"l":false,"info":"This shows the data coming out of the\r\nuibuilder node's Port #1 (top) which is\r\nthe standard output.\r\n\r\nHere you will see any standard msg sent from\r\nyour front-end code."},{"id":"df4025edc7711edd","type":"debug","z":"a70b22bf53a7ea9c","name":"uibuilder control output","active":false,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"","statusType":"counter","x":655,"y":180,"wires":[],"l":false,"info":"This shows the data coming out of the\r\nuibuilder node's Port #2 (bottom) which is\r\nthe control output.\r\n\r\nHere you will see any control msg either sent\r\nby the node itself or from the front-end library.\r\n\r\nFor example the \"client disconnect\" and\r\n\"client connect\" messages. Or the \"visibility\"\r\nmessages from the client.\r\n\r\nLoop the \"client connect\", \"cache replay\" and\r\n\"cache clear\" messages back to a `uib-cache`\r\nnode before the input to uibuilder in order\r\nto control the output of the cache."},{"id":"5b40492405adf766","type":"comment","z":"a70b22bf53a7ea9c","name":"Chk Description in each node","info":"","x":490,"y":100,"wires":[]},{"id":"e8f0471862ab3f9c","type":"uib-sender","z":"a70b22bf53a7ea9c","url":"ui-tests","name":"","topic":"","passthrough":false,"return":false,"outputs":0,"x":580,"y":280,"wires":[]},{"id":"73e6d30f1e961bab","type":"inject","z":"a70b22bf53a7ea9c","name":"","props":[{"p":"_ui","v":"[{\"method\":\"remove\",\"components\":[\"#ui-test\"]},{\"method\":\"add\",\"components\":[{\"type\":\"main\",\"id\":\"ui-test\",\"parent\":\"#more\",\"components\":[{\"type\":\"h2\",\"slot\":\"Tables\"},{\"type\":\"p\",\"slot\":\"This is an example of using uibuilder's low-code, config-driven page builder. It is based on the TABLES example from <a href='http://pdfmake.org/playground.html' target='_blank'>pdfmake</a>. This is partly to demonstrate that pdfmake and uibuilder use related principals for similar outcomes.\"},{\"type\":\"article\",\"components\":[{\"type\":\"h3\",\"slot\":\"A simple table (no style overrides)\"},{\"type\":\"p\",\"slot\":\"Nothing more than a couple of unstyled rows and columns. No headings.\"},{\"type\":\"table\",\"components\":[{\"type\":\"tr\",\"components\":[{\"type\":\"td\",\"slot\":\"Column 1\"},{\"type\":\"td\",\"slot\":\"Column 2\"},{\"type\":\"td\",\"slot\":\"Column 3\"}]},{\"type\":\"tr\",\"components\":[{\"type\":\"td\",\"slot\":\"One value goes here\"},{\"type\":\"td\",\"slot\":\"Another one here\"},{\"type\":\"td\",\"slot\":\"OK\"}]}]}]},{\"type\":\"article\",\"components\":[{\"type\":\"h3\",\"slot\":\"A simple table with nested elements\"},{\"type\":\"p\",\"slot\":\"It is of course possible to nest any other type of nodes available in <del>pdfmake</del> uibuilder/HTML inside table cells.\"},{\"type\":\"table\",\"components\":[{\"type\":\"tr\",\"components\":[{\"type\":\"td\",\"slot\":\"Column 1\"},{\"type\":\"td\",\"slot\":\"Column 2\"},{\"type\":\"td\",\"slot\":\"Column 3\"}]},{\"type\":\"tr\",\"components\":[{\"type\":\"td\",\"slot\":\"Let's try an unordered list\",\"components\":[{\"type\":\"ul\",\"components\":[{\"type\":\"li\",\"slot\":\"Item 1\"},{\"type\":\"li\",\"slot\":\"Item 2\"}]}]},{\"type\":\"td\",\"slot\":\"or a nested table\",\"components\":[{\"type\":\"table\",\"components\":[{\"type\":\"tr\",\"components\":[{\"type\":\"th\",\"slot\":\"Col 1\"},{\"type\":\"th\",\"slot\":\"Col 2\"},{\"type\":\"th\",\"slot\":\"Col 3\"}]},{\"type\":\"tr\",\"components\":[{\"type\":\"td\",\"slot\":\"R1C1\"},{\"type\":\"td\",\"slot\":\"R1C2\"},{\"type\":\"td\",\"slot\":\"R1C3\"}]},{\"type\":\"tr\",\"components\":[{\"type\":\"td\",\"slot\":\"R2C1\"},{\"type\":\"td\",\"slot\":\"R2C2\"},{\"type\":\"td\",\"slot\":\"R2C3\"}]}]}]},{\"type\":\"td\",\"slotMarkdown\":\"Inlines can be _styled_ easily as everywhere else. Even using Markdown!\"}]}]}]},{\"type\":\"article\",\"components\":[{\"type\":\"h3\",\"slot\":\"Defining column widths\"},{\"type\":\"p\",\"slotMarkdown\":\"~~Tables support the same width definitions as standard columns~~ HTML is different to pdfmaker here since styling is done using CSS.\"},{\"type\":\"table\",\"components\":[{\"type\":\"tr\",\"components\":[{\"type\":\"td\",\"attributes\":{\"style\":\"width:100em;\"},\"slot\":\"width:100em\"},{\"type\":\"td\",\"slot\":\"Unsized\"},{\"type\":\"td\",\"attributes\":{\"style\":\"width:25%;\"},\"slot\":\"width:25%\"},{\"type\":\"td\",\"slot\":\"Unsized\"}]},{\"type\":\"tr\",\"components\":[{\"type\":\"td\",\"slot\":\"fixed-width cells have exactly the specified width\"},{\"type\":\"td\",\"slotMarkdown\":\"_nothing interesting here_\"},{\"type\":\"td\",\"slotMarkdown\":\"_nothing interesting here_\"},{\"type\":\"td\",\"slotMarkdown\":\"_nothing interesting here_\"}]}]},{\"type\":\"table\",\"components\":[{\"type\":\"tr\",\"components\":[{\"type\":\"td\",\"attributes\":{\"style\":\"width:90%;\"},\"slotMarkdown\":\"This is a ~~star-sized~~ fixed % size column. The next column over, an auto-sized column, will wrap to accomodate all the text in this cell.\"},{\"type\":\"td\",\"slot\":\"I am auto sized.\"}]}]},{\"type\":\"table\",\"components\":[{\"type\":\"tr\",\"components\":[{\"type\":\"td\",\"slotMarkdown\":\"This is ~~a star-sized~~ an unsized column. The next column over, also auto-sized, will not wrap to accomodate all the text in this cell, because it has been given the noWrap style.\"},{\"type\":\"td\",\"attributes\":{\"style\":\"white-space: nowrap;\"},\"slot\":\"I am no-wrap auto sized.\"}]}]}]},{\"type\":\"article\",\"components\":[{\"type\":\"h3\",\"slot\":\"Defining row heights\"},{\"type\":\"table\",\"components\":[{\"type\":\"tr\",\"components\":[{\"type\":\"td\",\"attributes\":{\"style\":\"height:2em;\"},\"slot\":\"row 1 with height 2em\"},{\"type\":\"td\",\"slot\":\"Column B\"}]},{\"type\":\"tr\",\"components\":[{\"type\":\"td\",\"attributes\":{\"style\":\"height:5em;\"},\"slot\":\"row 2 with height 5em\"},{\"type\":\"td\",\"slot\":\"Column B\"}]},{\"type\":\"tr\",\"components\":[{\"type\":\"td\",\"attributes\":{\"style\":\"height:7em;\"},\"slot\":\"row 3 with height 7em\"},{\"type\":\"td\",\"slot\":\"Column B\"}]}]}]}]}]}]","vt":"json"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"tables","x":170,"y":280,"wires":[["e8f0471862ab3f9c"]]},{"id":"d621dd0923f1e188","type":"junction","z":"a70b22bf53a7ea9c","x":370,"y":160,"wires":[["279f069488faedf5"]]}]

Here showing in dark mode but you can force to light or simply change the dark/light setting on your browser. The example even includes both a safe HTML filter AND the ability to output content as Markdown not just HTML - both thanks to built-in support for 2 optional external libraries (dompurify and markdown-it).

[Shadow20X6]

I may have to switch over to this method. Which helps, since I won't have to rely on Access to make the Forms. Though I suppose there will be more coding to make accurate PDFs in the long run.

Thanks for the idea.

[TotallyInformation]

@Shadow20X6

accurate PDFs

That is a rabbit hole you might want to try and avoid if you can. What does "accurate" mean in this context? Also, PDF's have a major accessibility issue, at work for example, we are introducing a policy to move away from PDF's for this very reason. Whereas a well-formatted HTML document has excellent accessibility. And HMTL is open rather than the semi-closed nature of Adobe PDF. All sorts of good reasons to use HTML over PDF. The big draw of PDF used to be "accuracy" of reproduction on paper - but that is another thing we are trying to get away from since paper isn't only expensive both to produce as prints but also to store and is far from green having a massive environmental cost.

[Shadow20X6]

I meant I'm not familiar with the PDF Maker on Node-Red, but I'm assuming there will need to be a lot of coding involved to make my PDFs accurate.

The reason I was going for PDFs is because I do need them to be printed.

For full context, I'm creating Fixture Labels and DMX Paperwork for Entertainment Lighting.

Usually what happens is we rent Lighting Fixtures from a Rental shop. They need an Address and other mode settings applied to work accurately in our design. My Database system gathers the data from the design software, then formats sticker labels that we print, and label to the lighting fixtures. This way when we are troubleshooting the fixtures, they have a label with the settings they need to work properly.

The other reports printed are different methods of viewing the data, either by Channel or by DMX Address, By Position etc. All these reports do need to be printed at least once so there is paperwork on the show site to refer to when there are questions about the settings.

Switching to something like PDF make, I'm concerned how tricky it will be to create a format that works with the physical sticker labels I need to print on.

I was actually hoping someone had a method of automating tasks in Access instead, since my Access Reports that are formatted with the Database directly already has forms that I'm pretty happy with.

[TotallyInformation]

@Shadow20X6

I do need them to be printed

OK, I can see that.

@Shadow20X6

Switching to something like PDF make, I'm concerned how tricky it will be to create a format that works with the physical sticker labels I need to print on.

Right, getting label formats right is always tricky as I know from past experiences both professionally and personally.

The good news is that I don't think doing it in HTML is likely to be much more difficult than in PDF. The bad news is it won't be any easier either probably. Though done once it is done forever so there is that.

There are some simple examples on the web though, like this one: Label Print (codepen.io).

If you can come up with something close to the right format, I'd likely be happy to provide some help in making it into a uibuilder low-code example. I think it would doubtless help others in the future.

As for Access, I've done plenty of that over the decades though nothing in that last few years I'm afraid. External automation of Access is certainly possible. Unfortunately, it is only really possible from something that can handle .NET which means getting dirty with PowerShell in terms of scripting languages. Node-RED can drive a PowerShell script via its exec node but you would still need to do the PowerShell side. I'm afraid I've never externally managed Access other than via a full Visual Basic interface (and that was so long ago now, I barely remember what I did though I occasionally wonder whether Barclay's Credit are still using it ). Oh, and in case people start pointing out all of the node.js modules that talk to Access DB's - please note that you CAN certainly interact with the data. But that isn't what you need of course, you need to drive the forms and printing interfaces.