How to automatically create multiple msg.selectRange payloads?

General
1

Hello everyone,
this is a bit difficult to explain, I think.
I am trying to vary one message parameter but I need to vary it in a bunch of flows simultaneously.

I want to change the ranges (like Excel ranges)
A1:A22
B1:B22
C1:C22

to e.g.
A4:A9
B4:B9
C4:C9

I was thinking of something like the code at te bottom but that is probably the worst possible approach (although it would work) because I need to trigger many nodes and create lots of nodes.

Ideally I have one node to define the starting number and end number and one node to define the letter and then join them.

Does anybody know if that is possible (for a newbie like me)?

Thank you all :slight_smile:
Alex

EDIT:
Maybe I can inject msg.SelectRange with value "A1:A2" and then have a change node that can change the "1" and "2" to new values using a regular expression? Would that be possible? Although no idea what the regular expression would be for that.
(see next flow)

[
    {
        "id": "306ba3bd11863c34",
        "type": "tab",
        "label": "Flow 3",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "ec5df9bd8567b0cc",
        "type": "inject",
        "z": "306ba3bd11863c34",
        "name": "start result",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            },
            {
                "p": "selectRange",
                "v": "A1:A2",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": "",
        "topic": "",
        "payload": "",
        "payloadType": "str",
        "x": 500,
        "y": 160,
        "wires": [
            [
                "6ad7b76f52143be3",
                "e5524ddca70c3f0b"
            ]
        ]
    },
    {
        "id": "e5524ddca70c3f0b",
        "type": "debug",
        "z": "306ba3bd11863c34",
        "name": "debug 6",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "selectRange",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 960,
        "y": 200,
        "wires": []
    },
    {
        "id": "6ad7b76f52143be3",
        "type": "range",
        "z": "306ba3bd11863c34",
        "minin": "1",
        "maxin": "2",
        "minout": "1",
        "maxout": "22",
        "action": "scale",
        "round": false,
        "property": "selectRange",
        "name": "",
        "x": 650,
        "y": 260,
        "wires": [
            [
                "e5524ddca70c3f0b"
            ]
        ]
    }
]

First flow

[
    {
        "id": "bf622e623503117a",
        "type": "tab",
        "label": "Flow 1",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "97fb2163122f2701",
        "type": "inject",
        "z": "bf622e623503117a",
        "name": "",
        "props": [
            {
                "p": "selectRange",
                "v": "A",
                "vt": "str"
            },
            {
                "p": "parts",
                "v": "1",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "x": 390,
        "y": 240,
        "wires": [
            [
                "9891ae51dfd0aa09"
            ]
        ]
    },
    {
        "id": "867a84d97f76647f",
        "type": "debug",
        "z": "bf622e623503117a",
        "name": "debug 5",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "selectRange",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1060,
        "y": 380,
        "wires": []
    },
    {
        "id": "9891ae51dfd0aa09",
        "type": "join",
        "z": "bf622e623503117a",
        "name": "",
        "mode": "custom",
        "build": "string",
        "property": "selectRange",
        "propertyType": "msg",
        "key": "topic",
        "joiner": "",
        "joinerType": "str",
        "accumulate": false,
        "timeout": "",
        "count": "4",
        "reduceRight": false,
        "reduceExp": "",
        "reduceInit": "",
        "reduceInitType": "num",
        "reduceFixup": "",
        "x": 650,
        "y": 300,
        "wires": [
            [
                "867a84d97f76647f",
                "fca0a12ee625a425"
            ]
        ]
    },
    {
        "id": "0d13f39b67bb5c1d",
        "type": "inject",
        "z": "bf622e623503117a",
        "name": "",
        "props": [
            {
                "p": "selectRange",
                "v": "1",
                "vt": "str"
            },
            {
                "p": "parts",
                "v": "2",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "x": 390,
        "y": 320,
        "wires": [
            [
                "9891ae51dfd0aa09"
            ]
        ]
    },
    {
        "id": "d32f317c32c90fd5",
        "type": "inject",
        "z": "bf622e623503117a",
        "name": "",
        "props": [
            {
                "p": "selectRange",
                "v": ":A",
                "vt": "str"
            },
            {
                "p": "parts",
                "v": "3",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "x": 390,
        "y": 400,
        "wires": [
            [
                "9891ae51dfd0aa09"
            ]
        ]
    },
    {
        "id": "0cbcdae59c6e13b5",
        "type": "inject",
        "z": "bf622e623503117a",
        "name": "",
        "props": [
            {
                "p": "selectRange",
                "v": "22",
                "vt": "str"
            },
            {
                "p": "parts",
                "v": "4",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "x": 390,
        "y": 460,
        "wires": [
            [
                "9891ae51dfd0aa09"
            ]
        ]
    },
    {
        "id": "fca0a12ee625a425",
        "type": "change",
        "z": "bf622e623503117a",
        "name": "",
        "rules": [
            {
                "t": "change",
                "p": "selectRange",
                "pt": "msg",
                "from": "A",
                "fromt": "str",
                "to": "B",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 830,
        "y": 460,
        "wires": [
            [
                "9fec9f2af5cfda9b"
            ]
        ]
    },
    {
        "id": "9fec9f2af5cfda9b",
        "type": "debug",
        "z": "bf622e623503117a",
        "name": "debug 6",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "selectRange",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1060,
        "y": 460,
        "wires": []
    }
]
2

Why not a ui-form, then you can construct the string with JSONata or javascript
example

[{"id":"97fb2163122f2701","type":"inject","z":"bf622e623503117a","name":"","props":[{"p":"payload"},{"p":"parts","v":"columStart","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"A","payloadType":"str","x":110,"y":260,"wires":[["9891ae51dfd0aa09"]]},{"id":"9891ae51dfd0aa09","type":"join","z":"bf622e623503117a","name":"join for testing","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"parts","joiner":"","joinerType":"str","accumulate":true,"timeout":"","count":"4","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":280,"y":340,"wires":[["783e4ab6518161e2"]]},{"id":"0d13f39b67bb5c1d","type":"inject","z":"bf622e623503117a","name":"","props":[{"p":"payload"},{"p":"parts","v":"columnStop","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"C","payloadType":"str","x":110,"y":300,"wires":[["9891ae51dfd0aa09"]]},{"id":"d32f317c32c90fd5","type":"inject","z":"bf622e623503117a","name":"","props":[{"p":"payload"},{"p":"parts","v":"rowStart","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"1","payloadType":"str","x":110,"y":340,"wires":[["9891ae51dfd0aa09"]]},{"id":"0cbcdae59c6e13b5","type":"inject","z":"bf622e623503117a","name":"","props":[{"p":"payload"},{"p":"parts","v":"rowStop","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"22","payloadType":"str","x":110,"y":380,"wires":[["9891ae51dfd0aa09"]]},{"id":"783e4ab6518161e2","type":"change","z":"bf622e623503117a","name":"","rules":[{"t":"set","p":"selectRange","pt":"msg","to":"$join(\t   $split(\t       $substringBefore(\t            $$.payload.columnStart & \t            $substringAfter(\t                \"A:B:C:D:E:F:G:H:I:J:K:L:M:N:O:P:Q:R:S:T:U:V:W:X:Y:Z\", \t                $$.payload.columnStart\t            ),\t            $$.payload.columnStop\t        ) & $$.payload.columnStop,\":\").(\t       $ & $$.payload.rowStart & \":\" & $ & $$.payload.rowStop\t   ),\t   \"\\n\"\t)\t    ","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":520,"y":460,"wires":[["9fec9f2af5cfda9b"]]},{"id":"8342a11ebe75cc7a","type":"switch","z":"bf622e623503117a","name":"ONLY A-Z 0-9","property":"$replace($join($$.payload.*, \"\"), /[A-Z0-9]/, \"\")","propertyType":"jsonata","rules":[{"t":"eq","v":"","vt":"str"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":290,"y":480,"wires":[["783e4ab6518161e2"],[]]},{"id":"9fec9f2af5cfda9b","type":"debug","z":"bf622e623503117a","name":"debug 6","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"selectRange","targetType":"msg","statusVal":"","statusType":"auto","x":740,"y":400,"wires":[]},{"id":"a5cdefdb8464a553","type":"ui_form","z":"bf622e623503117a","name":"","label":"","group":"2d4fe667.28f8ba","order":23,"width":0,"height":0,"options":[{"label":"Column Start","value":"columnStart","type":"text","required":true,"rows":null},{"label":"Column Stop","value":"columnStop","type":"text","required":true,"rows":null},{"label":"Row Start","value":"rowStart","type":"text","required":true,"rows":null},{"label":"Row Stop","value":"rowStop","type":"text","required":true,"rows":null}],"formValue":{"columnStart":"","columnStop":"","rowStart":"","rowStop":""},"payload":"","submit":"submit","cancel":"cancel","topic":"topic","topicType":"msg","splitLayout":"","className":"","x":110,"y":480,"wires":[["ce933e98339a7ad2","8342a11ebe75cc7a"]]},{"id":"ce933e98339a7ad2","type":"junction","z":"bf622e623503117a","x":40,"y":480,"wires":[["a5cdefdb8464a553"]]},{"id":"2d4fe667.28f8ba","type":"ui_group","name":"demo","tab":"1caa8458.b17814","order":2,"disp":true,"width":"12","collapse":false,"className":""},{"id":"1caa8458.b17814","type":"ui_tab","name":"Demo","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

[edit: fixed typo]

1 Like
3

Thank you @E1cid, but unfortunately a UI would be overkill.

I am now trying the following approach (inject the messages, change the message to the new range, switch/route to the respective target).
But, my problem is, that I want to route all incoming messages to the respective target, i.e.
IF msg.selectsetting == ip THEN route msg.* to output x. Is that possible?

[
    {
        "id": "bf622e623503117a",
        "type": "tab",
        "label": "Flow 1",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "5fe26d18c7e6d0d5",
        "type": "inject",
        "z": "bf622e623503117a",
        "name": "start result",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            },
            {
                "p": "setting",
                "v": "result",
                "vt": "str"
            },
            {
                "p": "selectRange",
                "v": "Ax:Ay",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": "",
        "topic": "",
        "payload": "",
        "payloadType": "str",
        "x": 430,
        "y": 150,
        "wires": [
            [
                "731483a325cb9cf8"
            ]
        ]
    },
    {
        "id": "731483a325cb9cf8",
        "type": "change",
        "z": "bf622e623503117a",
        "name": "Cell Range",
        "rules": [
            {
                "t": "change",
                "p": "selectRange",
                "pt": "msg",
                "from": "x",
                "fromt": "str",
                "to": "1",
                "tot": "str"
            },
            {
                "t": "change",
                "p": "selectRange",
                "pt": "msg",
                "from": "y",
                "fromt": "str",
                "to": "1",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 620,
        "y": 150,
        "wires": [
            [
                "48a95987edf724da"
            ]
        ]
    },
    {
        "id": "48a95987edf724da",
        "type": "switch",
        "z": "bf622e623503117a",
        "name": "Router",
        "property": "setting",
        "propertyType": "msg",
        "rules": [
            {
                "t": "nnull"
            },
            {
                "t": "eq",
                "v": "result",
                "vt": "str"
            },
            {
                "t": "eq",
                "v": "ip",
                "vt": "str"
            },
            {
                "t": "eq",
                "v": "template",
                "vt": "str"
            },
            {
                "t": "eq",
                "v": "settings",
                "vt": "str"
            },
            {
                "t": "eq",
                "v": "timers",
                "vt": "str"
            },
            {
                "t": "eq",
                "v": "rules",
                "vt": "str"
            }
        ],
        "checkall": "true",
        "repair": false,
        "outputs": 7,
        "x": 810,
        "y": 150,
        "wires": [
            [
                "16ef9112697e9058"
            ],
            [],
            [],
            [],
            [],
            [],
            []
        ]
    },
    {
        "id": "16ef9112697e9058",
        "type": "debug",
        "z": "bf622e623503117a",
        "name": "debug 9",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 1050,
        "y": 140,
        "wires": []
    }
]
4

This is fairly elegant even without the ui node.
IMHO, OP should have a closer look to the highlighted node which does the heavy lifting.

image
image1824Ă—624 54.5 KB

5

Then don't use a ui node. You can feed it a string and split it to create the same output, as @cameo69 suggest look at the change node.

[{"id":"1849a5c7c49e9335","type":"inject","z":"bf622e623503117a","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"A-C-!-2","payloadType":"str","x":310,"y":320,"wires":[["86afa5a163244e70"]]},{"id":"86afa5a163244e70","type":"change","z":"bf622e623503117a","name":"","rules":[{"t":"set","p":"selectRange","pt":"msg","to":"(\t    $ranges := $split($$.payload,\"-\");\t    $join(\t       $split(\t           $substringBefore(\t                $ranges[0] & \t                $substringAfter(\t                    \"A:B:C:D:E:F:G:H:I:J:K:L:M:N:O:P:Q:R:S:T:U:V:W:X:Y:Z\", \t                    $ranges[0]\t                ),\t                $ranges[1]\t            ) & $ranges[1],\":\").(\t           $ & $ranges[2] & \":\" & $ & $ranges[3]\t       ),\t       \"\\n\"\t    )\t)\t    ","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":320,"wires":[["9fec9f2af5cfda9b"]]},{"id":"9fec9f2af5cfda9b","type":"debug","z":"bf622e623503117a","name":"debug 6","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":660,"y":320,"wires":[]}]
6

Yes, you are right. I played with and without the UI. I now also found a router node that allows me to solve my issue in a more compact way.
But I have saved these approaches as an exmaple flow for my next project :slight_smile:

7

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.