So some background on what I have used node red for. I am currently a controls engineer and was trying to find a way to track data from water flows on different circuits on a machine over my companies network. This led me to node red! I was able to use a MQTT gateway to send data (water flows) over modbus protocol to the gateway and then use node red on a raspberry pi and send it to influxdb and then use grafana to graph the data in real time. It was very simple.
I currently have been tasked to find a way to track data on a new machine but this time preferably in table format and it will not be in real time.
The way the machine I built works is it is a water leak tester for hundreds of Casting Dies. I will have a MQTT In node on node red for "The number of Die" and a MQTT node for each water circuit. It comes out to be about 10 MQTT in nodes. My company wants to walk over and press a button mid cycle and snapshot the data for that particular time on that particular die. I have the ladder logic and gateway programed already.
My question is if anyone has an Idea what the best way to take these MQTT IN nodes and store them in a database, as well as plotting them in a table. I was thinking to MYSQL, and then to Excel. but im not sure how to take all these nodes and put them into the same database
As long as you have set up your MQTT topic hierarchy properly, you only need a single MQTT-in node to take the latest value as it is updated, you then split the topic to get an index field for your database update.
MySQL would probably actually be serious overkill for this unless you already have the service running for other purposes. Even with thousands of entries, SQLite would probably be more than sufficient. It doesn't appear to be a lot of data.
I would generally choose to use HTML as an output rather than Excel unless colleagues needed to do further analytics on the data for themselves.
You may, in fact, be able to get away without a database at all if the data all fits into memory comfortably. You might simply store it in a context variable. You will need the data in memory anyway in order to produce an output table.
To get the output, I would create a simple UI for the users to press the button and give any parameters if needed. I would feed the data to an HTML table.
Of course, I would use uibuilder to build and work with the UI - but then I might be considered slightly bias on that front! You may be able to do it using Dashboard if you really wanted to
My colleagues will use this data to reduce defects within the automobile parts that we produce. They also will use the data to create trends over the course of many months with specific flows within specific program numbers.
I want the data to be able to be saved to a table in a form of Program # and then the values of each individual flow and a timestamp.
I have my values coming in through MQTT from the PLC using Modbus and only changing when the value changes.
