I am wondering where to begin if I want to access a lookup table on my computer (example .csv) that would have two columns and I could match up a know variable passing through a function node on the payload and assign a new variable to the payload after a match is made. For example my table has a column "ip address" and a column "location". I would fill out the table with the ip address and associated location, and as my function node runs, it will be able to output the location based on the ip address that is attached to that payload.
Yes, I do this a lot. For example, I have a lookup table of device identities against actual location in the house.
You might want to think beyond a "table" though. I simply create a suitable JSON structure for the data and I set that up using an input trigger node then a change node to write it to a global variable. That can then be accessed easily from any function node or merged into a msg using a change node.
Now that Node-RED supports global variable retention via file-based storage, this is even better. But basically, I make sure that any global lookup variables are on the first tab and triggered on restart. Previously you just had to make sure that other flows sometimes needed a short initial delay. Now, though, keeping things retained means that this is no longer a worry.
So, in your case, you might end up with some JSON that looks like:
[
{ "ipaddress": "192.168.1.10", "location": "Living Room" },
{ "ipaddress": "192.168.1.11", "location": "Kitchen" },
...
]
Though you might prefer something like:
{
"192.168.1.10": { "ipaddress": "192.168.1.10", "location": "Living Room" },
"192.168.1.11": { "ipaddress": "192.168.1.11", "location": "Kitchen" },
...
}
Since that may be easier to lookup.
1 Like
Thank you!! I will mess around with this in the coming days and see what I can make of it.
Could you possibly help me with setting up the nodes as you described?
[{"id":"c089e2f9.2ab97","type":"inject","z":"b47adec7.c593","name":"","topic":"","payload":"","payloadType":"date","repeat":"1","crontab":"","once":true,"onceDelay":0.1,"x":170,"y":220,"wires":[["ad81d130.71f6e"]]},{"id":"61c88aac.86a274","type":"change","z":"b47adec7.c593","name":"","rules":[{"t":"change","p":"payload","pt":"msg","from":"","fromt":"str","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":640,"y":220,"wires":[["7ff62249.a588dc"]]},{"id":"ad81d130.71f6e","type":"trigger","z":"b47adec7.c593","op1":"{\"192.168.25.203\":{\"ipaddress\":\"192.168.25.203\",\"location\":\"Living Room\"},\"192.168.1.11\":{\"ipaddress\":\"192.168.1.11\",\"location\":\"Kitchen\"}}","op2":"0","op1type":"json","op2type":"str","duration":"250","extend":false,"units":"ms","reset":"","bytopic":"all","name":"","x":380,"y":220,"wires":[["61c88aac.86a274","3107d4de.c7264c"]]},{"id":"3107d4de.c7264c","type":"debug","z":"b47adec7.c593","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":570,"y":140,"wires":[]},{"id":"7ff62249.a588dc","type":"debug","z":"b47adec7.c593","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":830,"y":140,"wires":[]}]
You don't need to keep sending the lookup table.
[{"id":"547c4742.2d4598","type":"change","z":"d480ffdb.73b02","name":"","rules":[{"t":"set","p":"myLookupTable","pt":"global","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":490,"y":1740,"wires":[[]]},{"id":"e84be586.08c728","type":"inject","z":"d480ffdb.73b02","name":"","topic":"","payload":"{\"192.168.25.203\":{\"ipaddress\":\"192.168.25.203\",\"location\":\"Living Room\"},\"192.168.1.11\":{\"ipaddress\":\"192.168.1.11\",\"location\":\"Kitchen\"}}","payloadType":"json","repeat":"","crontab":"","once":true,"onceDelay":0.1,"x":290,"y":1740,"wires":[["547c4742.2d4598"]]}]
If you set up a file store for your variables (in settings.js), you can make that permanently stored and not need to redo it when restarting, just leave it for when you want to change the table.
In order to use the table with some incoming data you will use a change node with JSONata to do the lookup (or use a function node). If you let me have some example data, I'll set up an example.
Here is an example from one location. Could you also describe what needs to be done in the setting.js file?
{
"192.168.25.203": { "ipaddress": "192.168.25.203", "location": "Main Gate" },
"192.168.25.204": { "ipaddress": "192.168.25.204", "location": "Exiting Scale" },
"192.168.25.205": { "ipaddress": "192.168.25.205", "location": "Entering Scale" },
"192.168.25.206": { "ipaddress": "192.168.25.206", "location": "Primary" },
}
[{"id":"bcffb931.10c998","type":"function","z":"c5e4ee5.ad3701","name":"EPC Edge Analytics V1.1","func":"flow.rfid = flow.rfid || [];\nflow.activeReadSecs = flow.activeReadSecs || 3;\nflow.newTagReadSecs = flow.newTagReadSecs || 5;\n\nwbRSSIdB = Math.round(msg.wbRSSIdB || 0);\nnbRSSIdB = Math.round(msg.nbRSSIdB || 0);\nip = msg.ip || \"no reader\";\n\nif (msg.payload === \"clear\") {\n flow.rfid = [];\n msg.payload = \"interval\";\n}\n \nvar incoming = msg.epc || \"No Tag\";\n//var location = msg.ip || \"Unknown\";\ncurrentTime = msg.time || new Date();\n\nvar found = false;\nvar timeUpdate = true;\n\nif (msg.payload === \"interval\"){\n timeUpdate = false;\n} \n\nmsg.timeUpdate = timeUpdate;\n\nvar i;\nvar newTagRead = false;\n\nfor (i = 0; i < flow.rfid.length; i++){\n flow.rfid[i].timeElapsed = (currentTime - flow.rfid[i].time)/1000;\n \n if (flow.rfid[i].timeElapsed < (flow.activeReadSecs)){\n flow.rfid[i].activeRead = true;\n }\n else flow.rfid[i].activeRead = false;\n \n if (flow.rfid[i].epc === incoming){\n found = true;\n flow.rfid[i].wbRSSIdB = wbRSSIdB;\n flow.rfid[i].nbRSSIdB = nbRSSIdB;\n flow.rfid[i].ip = ip;\n if(timeUpdate){\n flow.rfid[i].time = currentTime;\n if (flow.rfid[i].timeElapsed > (flow.newTagReadSecs)){\n flow.rfid[i].count++;\n newTagRead = {\"payload\":{\"epc\":flow.rfid[i].epc,\"time\":currentTime,\"ip\":flow.rfid[i].ip}};\n }\n }\n }\n flow.rfid[i].timeElapsed = Math.round(flow.rfid[i].timeElapsed);\n}\n\nif (!found){\n var newRFID = JSON.parse('{\"epc\":\"'+incoming+'\",\"time\":0}');\n newRFID.time = currentTime;\n flow.rfid[i] = newRFID;\n flow.rfid[i].count = 1;\n flow.rfid[i].timeElapsed = 0;\n flow.rfid[i].activeRead = true;\n flow.rfid[i].wbRSSIdB = wbRSSIdB;\n flow.rfid[i].nbRSSIdB = nbRSSIdB;\n \n newTagRead = {\"payload\":{\"epc\":flow.rfid[i].epc,\"time\":currentTime, \"ip\":flow.rfid[i].ip}};\n}\n\n\nmsg.payload = flow.rfid;\nif (newTagRead && newTagRead.payload.epc !== \"\"){\n return [msg,newTagRead];\n}\nelse return [msg,null];\n\n","outputs":"2","noerr":0,"x":630,"y":200,"wires":[[],[]]},{"id":"9c3ae4d0.ba8018","type":"change","z":"c5e4ee5.ad3701","name":"activeReadSecs & newTagReadSecs","rules":[{"t":"set","p":"activeReadSecs","pt":"flow","to":"payload.activeReadSecs","tot":"msg"},{"t":"se
t","p":"newTagReadSecs","pt":"flow","to":"payload.newTagReadSecs","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":350,"y":180,"wires":[["bcffb931.10c998"]]},{"id":"d6ead39b.2d1f1","type":"inject","z":"c5e4ee5.ad3701","name":"","topic":"","payload":"interval","payloadType":"str","repeat":"1","crontab":"","once":false,"onceDelay":"","x":400,"y":240,"wires":[["bcffb931.10c998"]]}]
I am not sure if that will paste correctly or not, but in the function node, where the payload is set with the epc, time, and ip..(Line 64)..I would like to look up to the table and return the "location". This seems odd with only four locations, but ultimately there will be nearly 30 locations possibly.
Please check the Node-RED docs for setting up a persistent store. Sorry don't have the link to hand.
I need some input data to use against the reference data in order to come up with an example.
No it won't - it should end up on a single line.
HAHA.....Awesome!! Thanks
[{"id":"7d732f80.fe24e","type":"function","z":"f5267ba9.a53938","name":"EPC Edge Analytics V1.1","func":"flow.rfid = flow.rfid || [];\nflow.activeReadSecs = flow.activeReadSecs || 3;\nflow.newTagReadSecs = flow.newTagReadSecs || 5;\n\nwbRSSIdB = Math.round(msg.wbRSSIdB || 0);\nnbRSSIdB = Math.round(msg.nbRSSIdB || 0);\nip = msg.ip || \"no reader\";\n\nif (msg.payload === \"clear\") {\n flow.rfid = [];\n msg.payload = \"interval\";\n}\n \nvar incoming = msg.epc || \"No Tag\";\n//var location = msg.ip || \"Unknown\";\ncurrentTime = msg.time || new Date();\n\nvar found = false;\nvar timeUpdate = true;\n\nif (msg.payload === \"interval\"){\n timeUpdate = false;\n} \n\nmsg.timeUpdate = timeUpdate;\n\nvar i;\nvar newTagRead = false;\n\nfor (i = 0; i < flow.rfid.length; i++){\n flow.rfid[i].timeElapsed = (currentTime - flow.rfid[i].time)/1000;\n \n if (flow.rfid[i].timeElapsed < (flow.activeReadSecs)){\n flow.rfid[i].activeRead = true;\n }\n else flow.rfid[i].activeRead = false;\n \n if (flow.rfid[i].epc === incoming){\n found = true;\n flow.rfid[i].wbRSSIdB = wbRSSIdB;\n flow.rfid[i].nbRSSIdB = nbRSSIdB;\n flow.rfid[i].ip = ip;\n if(timeUpdate){\n flow.rfid[i].time = currentTime;\n if (flow.rfid[i].timeElapsed > (flow.newTagReadSecs)){\n flow.rfid[i].count++;\n newTagRead = {\"payload\":{\"epc\":flow.rfid[i].epc,\"time\":currentTime,\"ip\":flow.rfid[i].ip}};\n }\n }\n }\n flow.rfid[i].timeElapsed = Math.round(flow.rfid[i].timeElapsed);\n}\n\nif (!found){\n var newRFID = JSON.parse('{\"epc\":\"'+incoming+'\",\"time\":0}');\n newRFID.time = currentTime;\n flow.rfid[i] = newRFID;\n flow.rfid[i].count = 1;\n flow.rfid[i].timeElapsed = 0;\n flow.rfid[i].activeRead = true;\n flow.rfid[i].wbRSSIdB = wbRSSIdB;\n flow.rfid[i].nbRSSIdB = nbRSSIdB;\n \n newTagRead = {\"payload\":{\"epc\":flow.rfid[i].epc,\"time\":currentTime, \"ip\":flow.rfid[i].ip}};\n}\n\n\nmsg.payload = flow.rfid;\nif (newTagRead && newTagRead.payload.epc !== \"\"){\n return [msg,newTagRead];\n}\nelse return [msg,null];\n\n","outputs":"2","noerr":0,"x":730,"y":220,"wires":[[],[]]},{"id":"578777c7.09ff58","type":"change","z":"f5267ba9.a53938","name":"activeReadSecs & newTagReadSecs","rules":[{"t":"set","p":"activeReadSecs","pt":"flow","to":"payload.activeReadSecs","tot":"msg"},{"t":"set","p":"newTagReadSecs","pt":"flow","to":"payload.newTagReadSecs","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":450,"y":200,"wires":[["7d732f80.fe24e"]]},{"id":"170cee85.f68cd1","type":"inject","z":"f5267ba9.a53938","name":"","topic":"","payload":"interval","payloadType":"str","repeat":"1","crontab":"","once":false,"onceDelay":"","x":500,"y":260,"wires":[["7d732f80.fe24e"]]}]
There, I did the first one from a subflow.
Sorry, that's not giving me any data to work with. I just need an example msg against which you want to add the lookup data.
So, for example, in my own flows, my raw sensor data generally arrives with a msg.topic that identifies the sensor device with an ID, something like WIFI/<mac address> or TRX/ABCDE
So I have a global retained variable that contains a lookup table keyed on the ID part and containing location and other static info. Lets call that global.locations:
{
"TRX/ABCDE": { "location": "Hallway" },
...
}
So in a function, I can do something like:
const locations = global.get("locations");
msg.payload.location = locations[msg.topic].location;
msg.topic = locations[msg.topic].location
return msg;
Of course, you would want to put some error checking around that to make it more robust but hopefully you get the idea.
You can also do this with JSONata but that is hard to get right without example data.
