Sir ,I want to copy 2 msg.id in my sql DB

General
61

Look at your settings in the switch node and explain what you think it is doing and why you have the settings you have..

62

Sir ,In switch i thing if it is not empty then go to 1 like this ,i tried but i know this is wrong.

63

You have three conditions in the switch node, what do you think each will do. Also you have 'checking all rules' set. What do you think that will do?

If you don't know, try creating a flow with two inject nodes going to a switch node and for each output of the switch node, cnnect a seperate debug node. Set the first inject note do contain something in msg.payload and in the second inject node, remove the msg.payload. Then test and see what your results are.

Then play with the settings in the switch node till you understand how it works and you should be able to solve your own problem.

64

Also, be sure to read the help tab of the switch node so you will understand what each of the rules will do.

65

Sir i tried this i got success in case 1 of my code still case 2 and 3 are left for testing

a1
a1628×609 18 KB

9/17/2020, 5:18:30 PMnode: Fob
INSERT INTO NCS_DATABASE2 (Date_and_Time_India,Date_and_Time_UK ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Trade_ID,Door_ID ,Door_Description ,Status) VALUES ((strftime('%Y-%m-%d %H:%M:%S','now','localtime')),(strftime('%Y-%m-%d %H:%M:%S','now','localtime','-4 hour','-30 minutes')),'FOB','DR OPEN BY FOB ',' ','Shipra Nigam','04222436',' ',' ',' ','DR1 ON') : msg : Object
{ topic: "INSERT INTO  NCS_DATABASE2 (Da…", _msgid: "9ea93463.9d16d8" }
66

Show us what msg.payload looks like for case 1, case 2 and case 3.

Until we know what the messages look like in all three cases we cannot help you create the switch rules to tell the difference between them.

67

Sir I am sharing flow ,i know you must get angry but your question I didnt understand

[
    {
        "id": "76cf1830.35de38",
        "type": "tab",
        "label": "Flow 4",
        "disabled": false,
        "info": ""
    },
    {
        "id": "3bbd89ff.c385de",
        "type": "mqtt in",
        "z": "76cf1830.35de38",
        "name": "",
        "topic": "FOB",
        "qos": "1",
        "datatype": "auto",
        "broker": "2b0fd3c9.2369b4",
        "x": 110,
        "y": 240,
        "wires": [
            [
                "db88cc17.b124d8"
            ]
        ]
    },
    {
        "id": "abe1e993.5ec7b8",
        "type": "mqtt in",
        "z": "76cf1830.35de38",
        "name": "",
        "topic": "TRADE",
        "qos": "1",
        "datatype": "auto",
        "broker": "2b0fd3c9.2369b4",
        "x": 110,
        "y": 300,
        "wires": [
            [
                "f6a762ca.8a937",
                "dcb68435.c57928"
            ]
        ]
    },
    {
        "id": "71fb9113.f8d02",
        "type": "function",
        "z": "76cf1830.35de38",
        "name": "check FOB ",
        "func": "let newMsg = {};\nnewMsg.topic = `SELECT Fob_Tag_ID FROM NCS_DATABASE1 WHERE Fob_Tag_ID='${msg.payload}'`;\nreturn newMsg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "x": 570,
        "y": 240,
        "wires": [
            [
                "34a94e3f.0ca192"
            ]
        ]
    },
    {
        "id": "db88cc17.b124d8",
        "type": "function",
        "z": "76cf1830.35de38",
        "name": "id no space ",
        "func": "msg.payload = msg.payload.replace(/\\s/g, '')\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "x": 330,
        "y": 240,
        "wires": [
            [
                "63a5a6bc.5b5c3"
            ]
        ]
    },
    {
        "id": "f38cc54a.74fb58",
        "type": "function",
        "z": "76cf1830.35de38",
        "name": "check TRADE/TAG",
        "func": "\nlet newMsg1 = {};\nnewMsg1.topic = `SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='${msg.payload}'`;\nreturn newMsg1;\n",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "x": 590,
        "y": 300,
        "wires": [
            [
                "1b0604d0.bce41b"
            ]
        ]
    },
    {
        "id": "f6a762ca.8a937",
        "type": "function",
        "z": "76cf1830.35de38",
        "name": "tagid no space ",
        "func": "msg.payload = msg.payload.replace(/\\s/g, '')\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "x": 340,
        "y": 300,
        "wires": [
            [
                "518ab51d.e39104"
            ]
        ]
    },
    {
        "id": "63a5a6bc.5b5c3",
        "type": "change",
        "z": "76cf1830.35de38",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "id",
                "pt": "flow",
                "to": "payload",
                "tot": "msg"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 460,
        "y": 180,
        "wires": [
            [
                "71fb9113.f8d02"
            ]
        ]
    },
    {
        "id": "518ab51d.e39104",
        "type": "change",
        "z": "76cf1830.35de38",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "id",
                "pt": "flow",
                "to": "payload",
                "tot": "msg"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 460,
        "y": 380,
        "wires": [
            [
                "f38cc54a.74fb58"
            ]
        ]
    },
    {
        "id": "68911054.5892c",
        "type": "switch",
        "z": "76cf1830.35de38",
        "name": "",
        "property": "payload",
        "propertyType": "msg",
        "rules": [
            {
                "t": "empty"
            },
            {
                "t": "nempty"
            }
        ],
        "checkall": "true",
        "repair": false,
        "outputs": 2,
        "x": 780,
        "y": 240,
        "wires": [
            [
                "b847d25e.b285e8"
            ],
            [
                "41686377.c56c1c",
                "408c1a77.8ff894"
            ]
        ]
    },
    {
        "id": "34f160b2.bcdf68",
        "type": "switch",
        "z": "76cf1830.35de38",
        "name": "",
        "property": "payload",
        "propertyType": "msg",
        "rules": [
            {
                "t": "empty"
            },
            {
                "t": "nempty"
            }
        ],
        "checkall": "true",
        "repair": false,
        "outputs": 2,
        "x": 790,
        "y": 300,
        "wires": [
            [
                "da20360d.9c0bc8"
            ],
            [
                "ade0d5b5.c314f",
                "408c1a77.8ff894"
            ]
        ]
    },
    {
        "id": "ebd9c1c5.3e58c8",
        "type": "mqtt out",
        "z": "76cf1830.35de38",
        "name": "",
        "topic": "MATCH",
        "qos": "1",
        "retain": "true",
        "broker": "2b0fd3c9.2369b4",
        "x": 1280,
        "y": 80,
        "wires": []
    },
    {
        "id": "1b43f7af.47da78",
        "type": "mqtt out",
        "z": "76cf1830.35de38",
        "name": "",
        "topic": "MATCH1",
        "qos": "1",
        "retain": "true",
        "broker": "2b0fd3c9.2369b4",
        "x": 1300,
        "y": 420,
        "wires": []
    },
    {
        "id": "408c1a77.8ff894",
        "type": "join",
        "z": "76cf1830.35de38",
        "name": "",
        "mode": "custom",
        "build": "object",
        "property": "payload",
        "propertyType": "msg",
        "key": "topic",
        "joiner": "\\n",
        "joinerType": "str",
        "accumulate": false,
        "timeout": "1",
        "count": "2",
        "reduceRight": false,
        "reduceExp": "",
        "reduceInit": "",
        "reduceInitType": "",
        "reduceFixup": "",
        "x": 1170,
        "y": 260,
        "wires": [
            [
                "b1f89505.e8d268",
                "2172337f.6415cc"
            ]
        ]
    },
    {
        "id": "b847d25e.b285e8",
        "type": "change",
        "z": "76cf1830.35de38",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "payload",
                "pt": "msg",
                "to": "CR_FAIL",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 960,
        "y": 60,
        "wires": [
            [
                "548a6b74.151d4c",
                "ebd9c1c5.3e58c8"
            ]
        ]
    },
    {
        "id": "41686377.c56c1c",
        "type": "change",
        "z": "76cf1830.35de38",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "payload",
                "pt": "msg",
                "to": "CR_SUCCESS",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 960,
        "y": 140,
        "wires": [
            [
                "ebd9c1c5.3e58c8",
                "54d33210.5d6784"
            ]
        ]
    },
    {
        "id": "dcb68435.c57928",
        "type": "debug",
        "z": "76cf1830.35de38",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 270,
        "y": 400,
        "wires": []
    },
    {
        "id": "548a6b74.151d4c",
        "type": "debug",
        "z": "76cf1830.35de38",
        "name": "not exists",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1100,
        "y": 20,
        "wires": []
    },
    {
        "id": "da20360d.9c0bc8",
        "type": "change",
        "z": "76cf1830.35de38",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "payload",
                "pt": "msg",
                "to": "CR_FAIL",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 980,
        "y": 340,
        "wires": [
            [
                "897bd7a1.50c47",
                "1b43f7af.47da78"
            ]
        ]
    },
    {
        "id": "ade0d5b5.c314f",
        "type": "change",
        "z": "76cf1830.35de38",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "payload",
                "pt": "msg",
                "to": "CR_SUCCESS",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 960,
        "y": 440,
        "wires": [
            [
                "6b015f30.bb7c38",
                "1b43f7af.47da78"
            ]
        ]
    },
    {
        "id": "34a94e3f.0ca192",
        "type": "sqlite",
        "z": "76cf1830.35de38",
        "mydb": "1a9ea8ca.835eef",
        "sqlquery": "msg.topic",
        "sql": "SELECT * FROM NCS WHERE Person_ID='{{payload}}';",
        "name": "SQLITE3",
        "x": 681.25,
        "y": 177.5,
        "wires": [
            [
                "68911054.5892c"
            ]
        ]
    },
    {
        "id": "1b0604d0.bce41b",
        "type": "sqlite",
        "z": "76cf1830.35de38",
        "mydb": "1a9ea8ca.835eef",
        "sqlquery": "msg.topic",
        "sql": "SELECT * FROM NCS WHERE Person_ID='{{payload}}';",
        "name": "SQLITE3",
        "x": 700,
        "y": 360,
        "wires": [
            [
                "34f160b2.bcdf68"
            ]
        ]
    },
    {
        "id": "6b015f30.bb7c38",
        "type": "debug",
        "z": "76cf1830.35de38",
        "name": "Exists(tag)",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1070,
        "y": 480,
        "wires": []
    },
    {
        "id": "54d33210.5d6784",
        "type": "debug",
        "z": "76cf1830.35de38",
        "name": "Exists",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1050,
        "y": 220,
        "wires": []
    },
    {
        "id": "efd77388.22edc",
        "type": "switch",
        "z": "76cf1830.35de38",
        "name": "",
        "property": "payload",
        "propertyType": "msg",
        "rules": [
            {
                "t": "nempty"
            },
            {
                "t": "nempty"
            },
            {
                "t": "cont",
                "v": "CR_SUCCESS",
                "vt": "msg"
            }
        ],
        "checkall": "true",
        "repair": false,
        "outputs": 3,
        "x": 1390,
        "y": 260,
        "wires": [
            [
                "8d27a04c.fcd5e"
            ],
            [
                "8efffbc1.087908"
            ],
            [
                "58beade5.b4a47c"
            ]
        ]
    },
    {
        "id": "dfa519aa.e98cc",
        "type": "debug",
        "z": "76cf1830.35de38",
        "name": "Fob",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1710,
        "y": 120,
        "wires": []
    },
    {
        "id": "897bd7a1.50c47",
        "type": "debug",
        "z": "76cf1830.35de38",
        "name": "not exists(tag)",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1040,
        "y": 300,
        "wires": []
    },
    {
        "id": "fca22ee0.3d8d88",
        "type": "debug",
        "z": "76cf1830.35de38",
        "name": "tag",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1730,
        "y": 280,
        "wires": []
    },
    {
        "id": "5b6be1e5.c52aa",
        "type": "debug",
        "z": "76cf1830.35de38",
        "name": "Fob&tag",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1760,
        "y": 440,
        "wires": []
    },
    {
        "id": "8d27a04c.fcd5e",
        "type": "function",
        "z": "76cf1830.35de38",
        "name": "valid fob updating",
        "func": "\nvar words=msg.id;\nvar i= words.length;\n/*if(i==4)\n{\nlet newMsg1 = {};\n\nnewMsg1.topic = `INSERT INTO  NCS_DATABASE2(Date_and_Time_India,Date_and_Time_UK ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Trade_ID,Door_ID ,Door_Description ,Status)\nVALUES ((strftime('%Y-%m-%d %H:%M:%S','now','localtime')),(strftime('%Y-%m-%d %H:%M:%S','now','localtime','-4 hour','-30 minutes')),'TAG','DR OPEN BY TAG',' ','Shipra Nigam',' ','${msg.id}',' ',' ','DR1 ON')`;\nreturn newMsg1;\n\n}*/\nif(i==8)\n{\n    let newMsg1 = {};\n\nnewMsg1.topic = `INSERT INTO  NCS_DATABASE2 (Date_and_Time_India,Date_and_Time_UK ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Trade_ID,Door_ID ,Door_Description ,Status)\nVALUES ((strftime('%Y-%m-%d %H:%M:%S','now','localtime')),(strftime('%Y-%m-%d %H:%M:%S','now','localtime','-4 hour','-30 minutes')),'FOB','DR OPEN BY FOB ',' ','Shipra Nigam','${msg.id}',' ',' ',' ','DR1 ON')`;\nreturn newMsg1;\n}\n",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "x": 1530,
        "y": 100,
        "wires": [
            [
                "dfa519aa.e98cc"
            ]
        ]
    },
    {
        "id": "8efffbc1.087908",
        "type": "function",
        "z": "76cf1830.35de38",
        "name": "valid tag updating",
        "func": "\nvar words=msg.id;\nvar i= words.length;\nif(i==4)\n{\nlet newMsg1 = {};\n\nnewMsg1.topic = `INSERT INTO  NCS_DATABASE2(Date_and_Time_India,Date_and_Time_UK ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Trade_ID,Door_ID ,Door_Description ,Status)\nVALUES ((strftime('%Y-%m-%d %H:%M:%S','now','localtime')),(strftime('%Y-%m-%d %H:%M:%S','now','localtime','-4 hour','-30 minutes')),'TAG','DR OPEN BY TAG',' ','Shipra Nigam',' ','${msg.id}',' ',' ','DR1 ON')`;\nreturn newMsg1;\n\n}\n/*if(i==8)\n{\n    let newMsg1 = {};\n\nnewMsg1.topic = `INSERT INTO  NCS_DATABASE2 (Date_and_Time_India,Date_and_Time_UK ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Trade_ID,Door_ID ,Door_Description ,Status)\nVALUES ((strftime('%Y-%m-%d %H:%M:%S','now','localtime')),(strftime('%Y-%m-%d %H:%M:%S','now','localtime','-4 hour','-30 minutes')),'FOB','DR OPEN BY FOB ',' ','Shipra Nigam','${msg.id}',' ',' ',' ','DR1 ON')`;\nreturn newMsg1;*/\n",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "x": 1610,
        "y": 220,
        "wires": [
            [
                "fca22ee0.3d8d88"
            ]
        ]
    },
    {
        "id": "58beade5.b4a47c",
        "type": "function",
        "z": "76cf1830.35de38",
        "name": "case 1 and 3 updating together",
        "func": "\nvar words=msg.id;\nvar words1=msg.id1;\nvar i= words.length;\nvar j= words1.length;\n/*if(i==8)\n\n{\n     let newMsg1={};\n      newMsg1.topic = `INSERT INTO  NCS_DATABASE4 (Date_and_Time_India,Date_and_Time_UK ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Trade_ID,Door_ID ,Door_Description ,Status)\nVALUES ((strftime('%Y-%m-%d %H:%M:%S','now','localtime')),(strftime('%Y-%m-%d %H:%M:%S','now','localtime','-4 hour','-30 minutes')),'FOB','DR OPEN BY FOB ',' ','Shipra Nigam','${msg.id}',' ',' ',' ','DR1 ON')`;\nreturn newMsg1;\n    \n}*/\nif(i==8&&j==4)\n         {\n     \nlet newMsg2 = {};\n\nnewMsg2.topic = `INSERT INTO  NCS_DATABASE2 (Date_and_Time_India,Date_and_Time_UK ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Trade_ID,Door_ID ,Door_Description ,Status)\nVALUES ((strftime('%Y-%m-%d %H:%M:%S','now','localtime')),(strftime('%Y-%m-%d %H:%M:%S','now','localtime','-4 hour','-30 minutes')),'FOB & TAG','DR OPEN BY FOB & TAG',' ','Shipra Nigam',' ','${msg.id}','${msg.id1} ',' ','High Risk DR ON')`;\n\nreturn newMsg2;\n}\n\n\n\n\n\n/*if(i==8)\n{\n    let newMsg2 = {};\n\nnewMsg2.topic = `INSERT INTO  NCS_DATABASE4 (Date_and_Time_India,Date_and_Time_UK ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Trade_ID,Door_ID ,Door_Description ,Status)\nVALUES ((strftime('%Y-%m-%d %H:%M:%S','now','localtime')),(strftime('%Y-%m-%d %H:%M:%S','now','localtime','-4 hour','-30 minutes')),'FOB','DR OPEN BY FOB ',' ','Shipra Nigam','${msg.id}',' ',' ',' ','DR1 ON')`;\nreturn newMsg2;\n}*/",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "x": 1650,
        "y": 380,
        "wires": [
            [
                "5b6be1e5.c52aa"
            ]
        ]
    },
    {
        "id": "b1f89505.e8d268",
        "type": "debug",
        "z": "76cf1830.35de38",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 1330,
        "y": 320,
        "wires": []
    },
    {
        "id": "2172337f.6415cc",
        "type": "change",
        "z": "76cf1830.35de38",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "id",
                "pt": "msg",
                "to": "id",
                "tot": "flow"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 1290,
        "y": 180,
        "wires": [
            [
                "efd77388.22edc"
            ]
        ]
    },
    {
        "id": "2b0fd3c9.2369b4",
        "type": "mqtt-broker",
        "z": "",
        "name": "",
        "broker": "tcp://mqtt.eclipse.org",
        "port": "1883",
        "clientid": "NCS_Client_034",
        "usetls": false,
        "compatmode": true,
        "keepalive": "60",
        "cleansession": true,
        "birthTopic": "",
        "birthQos": "0",
        "birthPayload": "",
        "closeTopic": "",
        "closeQos": "0",
        "closePayload": "",
        "willTopic": "",
        "willQos": "0",
        "willPayload": ""
    },
    {
        "id": "1a9ea8ca.835eef",
        "type": "sqlitedb",
        "z": "",
        "db": "/home/shipra/Documents/NCS_Projects/IOT/NCS_API/New DB Testing/NCS_new.db",
        "mode": "RWC"
    }
]
68

Sir for case 2 it is also working but whenever i am giving both inputs together (for case 3) output is not coming as per requirement

69

this is the case 3 join node debug output

9/17/2020, 5:58:29 PMnode: b1f89505.e8d268
SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='2814' : msg.payload : Object
object
SELECT Fob_Tag_ID FROM NCS_DATABASE1 WHERE Fob_Tag_ID='04222436': array[1]
0: object
SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='2814': array[1]
0: object
70

Okay, you were asking about the first Switch node, not the last one - that was my mistake.

The Switch node needs to tell the difference between "a TAG has been matched" and "a TAG has not been matched".

So the same question as before - share an example of the message that represents both of those cases. We can then help you get the Switch node rules right.

71

aa
aa790×357 37.9 KB

this switch is want that if user presses 3rd case and he will entered both ID's together now this switch node should display me like this in DB.

l
l1254×37 8.51 KB

72

I cannot provide any more help until you are able to provide example messages for the three cases that will arrive at the Switch node:

  1. just the FOB
  2. just the TAG
  3. both the FOB and TAG

Until we see what those messages look like, we cannot help you get the Switch rules right to separate each of those cases out.

73

for

  1. only 04222436 gets into sql FOB column(04222436 is an series it could be any 8 digit no)
  2. only 2814 gets into tag column of sql
  3. both 04222436 and 2814 gets in to both Fob as well as Tag column in sql
74

Why are you talking about sql columns? We are talking about messages arriving at the Switch node.

Lets look once again the list of steps we agreed earlier:

  1. The Join node passes on a message containing the FOB and/or TAG payloads
    1. If the message contains just the FOB value
      1. Update the database with the FOB value
      2. send back a success message
    2. If the message contains just the TAG value
      1. Update the database with the TAG value
      2. send back a success message
    3. If the message contains both a valid FOB and TAG value
      1. Update the database with both values
      2. Send back a success message

If you have built the flow as I suggested, then the Switch node after the Join will be receiving a message in one of three possible states:

  1. the message contains just the FOB value
  2. the message contains just the TAG value
  3. the message contains both FOB and TAG value

It is the job of the Switch node to recognise these three different cases and pass the message to the corresponding output. But the only way we can do that is if you can share example messages for each of those cases.

Do the following:

  1. Wire a Debug node after the Join node.
  2. trigger just a FOB message, wait for the Debug node to show the result and take a copy of it.
  3. trigger just a TAG message, - take a copy of the Debug node output
  4. trigger a FOB and TAG message - take a copy of the Debug node output.
  5. Show us the Debug node output for each of those cases - and make it very clear which case is which and make sure we can see the full contents of msg.payload.
75

Sure Sir,

Case 1 join debug output

9/17/2020, 6:41:53 PMnode: join deug
SELECT Fob_Tag_ID FROM NCS_DATABASE1 WHERE Fob_Tag_ID='04222436' : msg : Object
object
topic: "SELECT Fob_Tag_ID FROM NCS_DATABASE1 WHERE Fob_Tag_ID='04222436'"
_msgid: "98d19304.da549"
payload: object
SELECT Fob_Tag_ID FROM NCS_DATABASE1 WHERE Fob_Tag_ID='04222436': array[1]
0: object
Fob_Tag_ID: "0422243

Case 2 Join debug output

9/17/2020, 6:43:03 PMnode: join deug
SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='2814' : msg : Object
object
topic: "SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='2814'"
_msgid: "9538d3d8.69ec7"
payload: object
SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='2814': array[1]
0: object
Trade_ID: "2814"

Case 3 Join Debug output

9/17/2020, 6:45:02 PMnode: join deugSELECT Fob_Tag_ID FROM NCS_DATABASE1 WHERE Fob_Tag_ID='04222436' : msg : Object
object
topic: "SELECT Fob_Tag_ID FROM NCS_DATABASE1 WHERE Fob_Tag_ID='04222436'"
_msgid: "b1ca7237.07ea8"
payload: object
SELECT Fob_Tag_ID FROM NCS_DATABASE1 WHERE Fob_Tag_ID='04222436': array[1]
0: object
Fob_Tag_ID: "04222436"
9/17/2020, 6:45:05 PMnode: join deug
SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='2814' : msg : Object
object
topic: "SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='2814'"
_msgid: "bcab4ad7.b5dff8"
payload: object
SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='2814': array[1]
0: object
Trade_ID: "2814"

thats all 3 output from join node in 3 different cases Sir

76

That's great.

I notice that is use case 3 you are getting two messages. Looking at the timestamps, they are 3 seconds apart. Remember the Join node is configured for a maximum of 1 second delay between the two messages, otherwise this isn't going to work at all. So you need to either trigger the message quicker, or increase that delay in the Join node.

Now, in one of my many replies yesterday, I recommended you add a Function node to tidy up the output of the sql node.

I still recommend you do that.

Here is what I suggest you do.

  1. Add a Function node between each of your sqlite nodes and their switch node. Here's a screenshot of what I mean (ignore the fact I don't have the sqlite node installed...)

image
image706×892 65.6 KB

  1. set the code of the function to the following: (Make sure you change the msg.topic line at the start to match which of the two Function nodes you are setting.
msg.topic = "tag"; // or "trade"
if (msg.payload && msg.payload.length > 0) {
   msg.payload = msg.payload[0];
}
return msg;

Once you have done that, rerun the previous test - trigger each of the three use cases and share what the Debug output it for each of them.

77

Sir I have done check ones
But first check these functions
z

z2

z3

now case 1 Join Debug output

9/17/2020, 7:15:06 PMnode: join deug
Fob_Tag_ID : msg : Object
object
topic: "Fob_Tag_ID"
_msgid: "140bf647.b1e30a"
payload: object
Fob_Tag_ID: object
Fob_Tag_ID: "04222436

Case 2 Join Debug Output

9/17/2020, 7:18:17 PMnode: join deug
Trade_ID : msg : Object
object
topic: "Trade_ID"
_msgid: "f00bbcf2.b8891"
payload: object
Trade_ID: object
Trade_ID: "2814"

Case 3 Join Debug Output

9/17/2020, 7:19:03 PMnode: join deug
Fob_Tag_ID : msg : Object
object
topic: "Fob_Tag_ID"
_msgid: "95d190e4.0d7f4"
payload: object
Fob_Tag_ID: object
Fob_Tag_ID: "04222436"
9/17/2020, 7:19:06 PMnode: join deugTrade_ID : msg : Object
object
topic: "Trade_ID"
_msgid: "2a49b89f.fd02c8"
payload: object
Trade_ID: object
Trade_ID: "2814"

Check ones and give suggestions

78

It looks better, as I hope you can see. But it isn't quite there. I forgot the exact format of the message coming from the sql node.

Change one line in each of the functions you've just added to:

msg.payload = msg.payload[0].Trade_ID;

(And .Fob_Tag_ID)

79

The required modifications in function as you suggested just i have done see join debug output now after modification

Case 1:Join Debug output

9/17/2020, 7:33:21 PMnode: join deug
Fob_Tag_ID : msg : Object
object
topic: "Fob_Tag_ID"
_msgid: "a8081565.e02248"
payload: object
Fob_Tag_ID: "04222436"

Case2 :Join Debug Output

9/17/2020, 7:35:50 PMnode: join deug
Trade_ID : msg : Object
object
topic: "Trade_ID"
_msgid: "fb24c02c.618ca"
payload: object
Trade_ID: "2814"

Case 3 :Join Debug Output

9/17/2020, 7:36:39 PMnode: join deug
Fob_Tag_ID : msg : Object
object
topic: "Fob_Tag_ID"
_msgid: "5d256a2a.5f3114"
payload: object
Fob_Tag_ID: "04222436"
9/17/2020, 7:36:41 PMnode: join deugTrade_ID : msg : Object
object
topic: "Trade_ID"
_msgid: "3d022336.cab21c"
payload: object
Trade_ID: "2814"

Yes Sir check these and suggest

80

That is looking good.

In Case 1, msg.payload has a property called Fob_Tag_ID whose value is "04222436".

In Case 2, msg.payload has a property called Trade_ID whose value is "2814".

That's exactly what you want.

But in case 3, you are still getting two separate messages - although I hope you can see that one of them is the Fob message and one is the Trade message.

So now you need to either increase the delay in the Join node, or run the test quicker to get both messages to arrive within 1 second. Once you do that, you should get a single message with both the Fob_Tag_ID property and Trade_ID in the payload.