Save values on a table / multidimensional array

britzz · 14 December 2018 07:44

Dear all,

First of all, I apologize for this dumb question for most of you, but my programming background relies mostly on embedded C and I am now taking my first steps in Node-red, Json, MQTT, etc.

I am working on Dashboard that shows some data that I get from MQTT protocol.

The information I get comes with the following structure: PAR1;PAR2;PAR3;PAR4. where PAR1 is the sensor number which if I have to create a database would be my primary key

I then get it passed by a CSV node and can display the information with msg.payload.col1,

msg.payload.col2, msg.payload.col3,msg.payload.col4.

So far, for my small application it's working really well. However, now I would like to create a button just to save to a file (I was thinking a .csv file to open later with excel). I tried using the file-out node with the append option, but it's not what I need. since by injecting, for example: 1;10;12;20/2;10;12;20/1;10;12;30, I get:

1;10;12;20

2;10;12;20

1;10;12;30 and I want

1;10;12;30

2;10;12;20

which is basically the last message received for each sensor number (msg.payload.col1)

So, in your opinion which would be the easiest way to do it? Should I dynamically update a multidimensional array and save this array values when button is pressed or do I really need to implement a database?

Is there any way to get the current value of a textbox?

Thanks in advance for your help!

zenofmud · 14 December 2018 10:35

I'm confused by tyour use of semicolons ; and your explaination of the data you receive.

Please put a debug node on the output of the mqtt-in node and show us what that data is
put a debug node on the output of the CSV node and show us the data
please explain your file-out example better.
-- Do the semi-colins represent commas?
-- does the / represent a new line?
are you saying that the 1;10 is the identifier of the data andyou want to update it if it already exists?

I'm sure there are multiple solutions to achieve what you want, the choice will depend on many factors like how much data will be in the end result (i.e. are we talking about 10 items or 10,000,000? How ofter will the data change? does the data have to be recoverable in the event of a power outage or system restart?

Colin · 14 December 2018 10:49

In addition to @zenofmud's question is it that you are trying to save the on screen data to a file? If so then in your situation I would keep a copy of the on screen data in the flow and then output it to the file from there when the button is pressed, rather than from the dashboard. However it is also no doubt possible to do it from the dashboard, but I for one would find that more difficult.

britzz · 14 December 2018 11:52

Thank you @zenofmud and @colin for your help so far!

Here is my current code if want to better understand my implementation.

[
    {
        "id": "5230c72.fadcf38",
        "type": "tab",
        "label": "Sheet 1",
        "disabled": false,
        "info": ""
    },
    {
        "id": "56312c48.8f0e6c",
        "type": "csv",
        "z": "5230c72.fadcf38",
        "name": "CSV",
        "sep": ";",
        "hdrin": "",
        "hdrout": "",
        "multi": "one",
        "ret": "\\n",
        "temp": "",
        "skip": "0",
        "x": 590,
        "y": 360,
        "wires": [
            [
                "160cad.9edb9353"
            ]
        ]
    },
    {
        "id": "160cad.9edb9353",
        "type": "switch",
        "z": "5230c72.fadcf38",
        "name": "",
        "property": "payload.col1",
        "propertyType": "msg",
        "rules": [
            {
                "t": "eq",
                "v": "1",
                "vt": "str"
            },
            {
                "t": "eq",
                "v": "2",
                "vt": "str"
            },
            {
                "t": "eq",
                "v": "3",
                "vt": "str"
            },
            {
                "t": "eq",
                "v": "4",
                "vt": "str"
            },
            {
                "t": "eq",
                "v": "5",
                "vt": "str"
            },
            {
                "t": "eq",
                "v": "6",
                "vt": "str"
            },
            {
                "t": "eq",
                "v": "7",
                "vt": "str"
            },
            {
                "t": "eq",
                "v": "8",
                "vt": "str"
            },
            {
                "t": "eq",
                "v": "9",
                "vt": "str"
            },
            {
                "t": "eq",
                "v": "10",
                "vt": "str"
            },
            {
                "t": "eq",
                "v": "",
                "vt": "str"
            }
        ],
        "checkall": "true",
        "repair": false,
        "outputs": 11,
        "x": 780,
        "y": 360,
        "wires": [
            [
                "6ddda6b4.aaf7b8"
            ],
            [
                "b6e9d176.0eb6d8"
            ],
            [],
            [],
            [],
            [],
            [],
            [],
            [],
            [],
            []
        ]
    },
    {
        "id": "29d7e93d.23936e",
        "type": "mqtt in",
        "z": "5230c72.fadcf38",
        "name": "",
        "topic": "test/t1",
        "qos": "2",
        "broker": "59cb4e43.e42fb",
        "x": 210,
        "y": 360,
        "wires": [
            [
                "97ed4c25.d1a7c8"
            ]
        ]
    },
    {
        "id": "53dbb778.2f0b08",
        "type": "ui_text",
        "z": "5230c72.fadcf38",
        "group": "e638b37.019e95",
        "order": 2,
        "width": "2",
        "height": "1",
        "name": "",
        "label": "",
        "format": "{{msg.payload.col3}}",
        "layout": "col-center",
        "x": 1430,
        "y": 200,
        "wires": []
    },
    {
        "id": "32832b61.5bba9c",
        "type": "inject",
        "z": "5230c72.fadcf38",
        "name": "",
        "topic": "",
        "payload": "1;A;B;200",
        "payloadType": "str",
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "x": 200,
        "y": 480,
        "wires": [
            [
                "97ed4c25.d1a7c8"
            ]
        ]
    },
    {
        "id": "deea77d3.2c814",
        "type": "inject",
        "z": "5230c72.fadcf38",
        "name": "",
        "topic": "",
        "payload": "2;C;D;400",
        "payloadType": "str",
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "x": 200,
        "y": 520,
        "wires": [
            [
                "97ed4c25.d1a7c8"
            ]
        ]
    },
    {
        "id": "7409000e.9243e8",
        "type": "ui_text",
        "z": "5230c72.fadcf38",
        "group": "e638b37.019e95",
        "order": 3,
        "width": "2",
        "height": "1",
        "name": "",
        "label": "",
        "format": "{{msg.payload.col2}}",
        "layout": "col-center",
        "x": 1290,
        "y": 180,
        "wires": []
    },
    {
        "id": "808c8415.6bc02",
        "type": "ui_text",
        "z": "5230c72.fadcf38",
        "group": "8c992532.5fe8e8",
        "order": 0,
        "width": "0",
        "height": "0",
        "name": "",
        "label": "",
        "format": "{{msg.payload.col1}}",
        "layout": "col-center",
        "x": 1170,
        "y": 280,
        "wires": []
    },
    {
        "id": "1513e89d.dee047",
        "type": "ui_text",
        "z": "5230c72.fadcf38",
        "group": "8c992532.5fe8e8",
        "order": 0,
        "width": "2",
        "height": "1",
        "name": "",
        "label": "",
        "format": "{{msg.payload.col3}}",
        "layout": "col-center",
        "x": 1430,
        "y": 320,
        "wires": []
    },
    {
        "id": "c7ab36ea.ffa0b8",
        "type": "ui_text",
        "z": "5230c72.fadcf38",
        "group": "8c992532.5fe8e8",
        "order": 0,
        "width": "0",
        "height": "0",
        "name": "",
        "label": "",
        "format": "{{msg.payload.col2}}",
        "layout": "col-center",
        "x": 1290,
        "y": 300,
        "wires": []
    },
    {
        "id": "bbb3d26d.3212e8",
        "type": "ui_text_input",
        "z": "5230c72.fadcf38",
        "name": "",
        "label": "Max value",
        "group": "5751eef7.ce4d1",
        "order": 0,
        "width": "4",
        "height": "1",
        "passthru": true,
        "mode": "text",
        "delay": "0",
        "topic": "ui_gauge.max",
        "x": 1190,
        "y": 560,
        "wires": [
            [
                "b84d275d.4a5a38"
            ]
        ]
    },
    {
        "id": "8faaab0b.bbb8d8",
        "type": "ui_gauge",
        "z": "5230c72.fadcf38",
        "name": "",
        "group": "8c992532.5fe8e8",
        "order": 0,
        "width": 0,
        "height": 0,
        "gtype": "gage",
        "title": "",
        "label": "",
        "format": "{{msg.payload.col4}}",
        "min": 0,
        "max": "10000",
        "colors": [
            "#b3022f",
            "#e6e600",
            "#56fa07"
        ],
        "seg1": "",
        "seg2": "",
        "x": 1570,
        "y": 340,
        "wires": []
    },
    {
        "id": "b84d275d.4a5a38",
        "type": "function",
        "z": "5230c72.fadcf38",
        "name": "",
        "func": "var x=msg.payload\nmsg.ui_control = {\"min\":0,\"max\":x}\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "x": 1390,
        "y": 560,
        "wires": [
            [
                "8faaab0b.bbb8d8",
                "dc05a1ae.070ef8"
            ]
        ]
    },
    {
        "id": "dc05a1ae.070ef8",
        "type": "ui_gauge",
        "z": "5230c72.fadcf38",
        "name": "",
        "group": "e638b37.019e95",
        "order": 5,
        "width": "2",
        "height": "2",
        "gtype": "wave",
        "title": "",
        "label": "",
        "format": "{{msg.payload.col4}}",
        "min": 0,
        "max": "10000",
        "colors": [
            "#b3022f",
            "#e6e600",
            "#56fa07"
        ],
        "seg1": "",
        "seg2": "",
        "x": 1570,
        "y": 220,
        "wires": []
    },
    {
        "id": "438d53d0.20f9f4",
        "type": "ui_text",
        "z": "5230c72.fadcf38",
        "group": "e638b37.019e95",
        "order": 1,
        "width": "2",
        "height": "1",
        "name": "",
        "label": "",
        "format": "{{msg.payload.col1}}",
        "layout": "col-center",
        "x": 1170,
        "y": 160,
        "wires": []
    },
    {
        "id": "97ed4c25.d1a7c8",
        "type": "function",
        "z": "5230c72.fadcf38",
        "name": "",
        "func": "\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "x": 450,
        "y": 360,
        "wires": [
            [
                "56312c48.8f0e6c"
            ]
        ]
    },
    {
        "id": "6ddda6b4.aaf7b8",
        "type": "function",
        "z": "5230c72.fadcf38",
        "name": "",
        "func": "\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "x": 1030,
        "y": 160,
        "wires": [
            [
                "438d53d0.20f9f4",
                "7409000e.9243e8",
                "53dbb778.2f0b08",
                "dc05a1ae.070ef8"
            ]
        ]
    },
    {
        "id": "b6e9d176.0eb6d8",
        "type": "function",
        "z": "5230c72.fadcf38",
        "name": "",
        "func": "\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "x": 1030,
        "y": 280,
        "wires": [
            [
                "808c8415.6bc02",
                "c7ab36ea.ffa0b8",
                "1513e89d.dee047",
                "8faaab0b.bbb8d8"
            ]
        ]
    },
    {
        "id": "83c555f2.9042d",
        "type": "inject",
        "z": "5230c72.fadcf38",
        "name": "",
        "topic": "",
        "payload": "1;E;F;300",
        "payloadType": "str",
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "x": 200,
        "y": 580,
        "wires": [
            [
                "97ed4c25.d1a7c8"
            ]
        ]
    },
    {
        "id": "138d9a7c.1cadc6",
        "type": "inject",
        "z": "5230c72.fadcf38",
        "name": "",
        "topic": "",
        "payload": "2;G;H;400",
        "payloadType": "str",
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "x": 200,
        "y": 620,
        "wires": [
            [
                "97ed4c25.d1a7c8"
            ]
        ]
    },
    {
        "id": "5d41fd9c.cf4584",
        "type": "comment",
        "z": "5230c72.fadcf38",
        "name": "I did the switch to divide the msg.payload according to msg.payload.col1",
        "info": "",
        "x": 580,
        "y": 220,
        "wires": []
    },
    {
        "id": "91fec1c2.a94e",
        "type": "comment",
        "z": "5230c72.fadcf38",
        "name": "Display the values on textboxes where each dashboard group corresponds to msg.payload.col1",
        "info": "",
        "x": 1300,
        "y": 120,
        "wires": []
    },
    {
        "id": "cc622d36.d44948",
        "type": "comment",
        "z": "5230c72.fadcf38",
        "name": "To emulate the MQTT messages",
        "info": "",
        "x": 150,
        "y": 420,
        "wires": []
    },
    {
        "id": "59cb4e43.e42fb",
        "type": "mqtt-broker",
        "z": "",
        "name": "",
        "broker": "test.mosquitto.org",
        "port": "1883",
        "clientid": "",
        "usetls": false,
        "compatmode": true,
        "keepalive": "60",
        "cleansession": true,
        "birthTopic": "",
        "birthQos": "0",
        "birthPayload": "",
        "closeTopic": "",
        "closeQos": "0",
        "closePayload": "",
        "willTopic": "",
        "willQos": "0",
        "willPayload": ""
    },
    {
        "id": "e638b37.019e95",
        "type": "ui_group",
        "z": "5230c72.fadcf38",
        "name": "Posto1",
        "tab": "84499b6b.8ac208",
        "order": 2,
        "disp": false,
        "width": "2",
        "collapse": false
    },
    {
        "id": "8c992532.5fe8e8",
        "type": "ui_group",
        "z": "",
        "name": "Posto2",
        "tab": "84499b6b.8ac208",
        "order": 3,
        "disp": false,
        "width": "2",
        "collapse": false
    },
    {
        "id": "5751eef7.ce4d1",
        "type": "ui_group",
        "z": "",
        "name": "ADMIN",
        "tab": "ce27b287.88fe38",
        "order": 1,
        "disp": true,
        "width": "6",
        "collapse": false
    },
    {
        "id": "84499b6b.8ac208",
        "type": "ui_tab",
        "z": "5230c72.fadcf38",
        "name": "Home",
        "icon": "assessment",
        "order": "1"
    },
    {
        "id": "ce27b287.88fe38",
        "type": "ui_tab",
        "z": "",
        "name": "ADMIN",
        "icon": "dashboard",
        "order": 2
    }
]

@Colin, this is exactly what I intend. To save my current dashboard data to a file but I do not know which strategy should I use. Could you please give me an idea?

Thanks again!

zenofmud · 14 December 2018 13:43

How will the table be? i.e. will it be a 5 element table or a 200,000 element table?

britzz · 14 December 2018 13:51

@zenofmud, It willb be fixed to a 30 elements.

My semicolon is used as a divider. My msg.payload arrives as PAR1;PAR2;PAR3;PAR4 and on my csv node it is transformed to:
msg.payload.col1 = PAR1
msg.payload.col2 = PAR2
msg.payload.col3 = PAR3
msg.payload.col4 = PAR4

and I use this values (msg.payload.col# ) to display on textboxes on my dashboard.
What I intend is to by pressing a button save the current dashboard data on a file like:

1;PAR2;PAR3;PAR4
2;PAR2;PAR3;PAR4
3;PAR2;PAR3;PAR4
(...)
28;PAR2;PAR3;PAR4
29;PAR2;PAR3;PAR4
30;PAR2;PAR3;PAR4

zenofmud · 14 December 2018 14:00

Why not use a sqlite db and save the data as it comes in and then display it to the dashboard. Every time a new2 vaue comes in for an existing inked, you just update the row.

That way you can retrive the data in different ways, depeding on how you define it, allowing for future reporting. You could even save each peice of data with a timestamp so you could go back and sa someting like "Show me the dashboard last Wednesday at 9:45 AM"

If you want to save it to a file only when you press a button, with you be overwritting the data each time or do you want to create a new file each time you press the button or add the new data to the end of the file? Are you going to time stamp the data written to the file?

britzz · 14 December 2018 14:09

To be honest I tried yesterday with sqlite but I got installation errors when installing sqlite and decided to quit this approach. -.-

Maybe when I get home I can show you the message I get when I try to install sqlite on node-red. Maybe something is missing...

Coming from the C, C++ background my idea was to make something like a multidimensional array and store the information in paralell to the textboxes on this array. But my little knowledge in Node-red /Javascript is an obstacle here.

I want to create a new file everytime I press the button and I do not intend to include the timestamp.

zenofmud · 14 December 2018 16:05

How are you going to name the file if you create a new one each time you press the button?

britzz · 15 December 2018 08:07

Maybe with a text-input node and get the value to msg.filename.

zenofmud · 15 December 2018 11:37

Have you looked at some of the 'table' examples in the Flows section of this site? One of them might give you some ideas.

britzz · 17 December 2018 10:21

Thanks for the suggestion. I tried all the flows mentioned above, but unfortunately couldn't find anything that suits my needs.

Maybe I will try to solve the problem that I have when I try to install sqlite and will try to implement what I need using it

Anyway, thanks for your time and thank you for trying to help me!

Topic		Replies	Views
Beginners questions on handling strings and using NodeRED General	19	2259	14 September 2021
Help - CSV node issue General	26	1457	21 April 2020
Changing structure of msg.payload from multidimensional array General split , function-node , mqtt	9	378	19 June 2022
[Example flow] Dashboard table node General	1	2858	20 July 2020
Column to row & separated by comma General	7	711	18 February 2022

Save values on a table / multidimensional array

Related Topics