How to receive a phone number by MQTT then perform an Excel style vlookup to obtain a caller name?

General
1

Basically I'm trying to send a phone number into nodered (this works) then get node-red to perform a lookup in a table stored somewhere (CSV?) and return a name for the caller. Getting nowhere. Have been Googling two evenings on the trot. If anyone knows how I should be doing this I'd be very grateful! Thanks

[
    {
        "id": "17688172.d2304f",
        "type": "tab",
        "label": "Woo woo",
        "disabled": false,
        "info": ""
    },
    {
        "id": "c56ba5eb.e35668",
        "type": "mqtt in",
        "z": "17688172.d2304f",
        "name": "",
        "topic": "calls/in/01302628441",
        "qos": "2",
        "datatype": "auto",
        "broker": "34ac2466.a0f64c",
        "x": 170,
        "y": 160,
        "wires": [
            [
                "53847812.e80058"
            ]
        ]
    },
    {
        "id": "53847812.e80058",
        "type": "function",
        "z": "17688172.d2304f",
        "name": "",
        "func": "\nvar loc = {\n\n'01209793193':'Jen',\n\n'67890':'Location 2',\n\n'abcde':'Location 3'\n\n};\n\nmsg.payload.location = loc[msg.payload.id];\n\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "x": 370,
        "y": 200,
        "wires": [
            [
                "f5bef78b.e23848"
            ]
        ]
    },
    {
        "id": "f5bef78b.e23848",
        "type": "debug",
        "z": "17688172.d2304f",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "x": 570,
        "y": 180,
        "wires": []
    },
    {
        "id": "34ac2466.a0f64c",
        "type": "mqtt-broker",
        "z": "",
        "name": "",
        "broker": "192.168.1.110",
        "port": "1883",
        "clientid": "node-red",
        "usetls": false,
        "compatmode": true,
        "keepalive": "60",
        "cleansession": true,
        "birthTopic": "",
        "birthQos": "0",
        "birthPayload": "",
        "closeTopic": "",
        "closeQos": "0",
        "closePayload": "",
        "willTopic": "",
        "willQos": "0",
        "willPayload": ""
    }
]
2

FOREWORD: Ideally, this phone book lookup would be a database transaction. The below info shows you how you could do this with pure node red only.

Idea...

You could have a "phonebook" lookup object stored in global context that you query.
NOTE: if the phone number is a different format, it will fail the lookup. See the NOTE in the function code

flow 1...

inject "initialise phone book" --> change node "set global.phonebook"

change node "set global.phonebook"

setup global.phonebook to following JSON...

{
  "123456789" : "ted",
  "213546879" : "harry",
  "312645978" : "billy",
  "987654321" : "sarah",
  "111222333" : "the ball and chain", 
}

flow 2...

mqtt --> function node "lookup phone number" --> debug node

Function node "lookup phone number"

to "lookup" the phone number

  • assumes phone number is in msg.payload
  • copies phone number to msg.phoneNumber
  • returns name in msg.payload 
var phonebook = global.get("phonebook") || {}; //get the phonebook out of global
msg.phoneNumber = msg.payload; //copy incoming phone number to msg.phoneNumber 

//NOTE: at this point (before performing lookup) you may wish to sanitise the phone number (remove any NON numeric values)
msg.payload = phonebook[ msg.phoneNumber ]; //lookup the phone number
return msg; //return msg to next node
2 Likes
3

COOL! Thanks Steve

Don't know if this is 'done' here (I'm new!) but here's the working flow for anyone coming after me. I just had to remove the final comma after chain" for it to work correctly for me. Don't know if others will find this necessary. THANK YOU

[
    {
        "id": "38bf2ce1.128f44",
        "type": "tab",
        "label": "Flow 4",
        "disabled": false,
        "info": ""
    },
    {
        "id": "f214f2b2.c9914",
        "type": "change",
        "z": "38bf2ce1.128f44",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "phonebook",
                "pt": "global",
                "to": "{\"111222333\":\"the ball and chain\",\"123456789\":\"ted\",\"213546879\":\"harry\",\"312645978\":\"billy\",\"987654321\":\"sarah\",\"01301671007\":\"nodtons\"}",
                "tot": "json"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 200,
        "y": 80,
        "wires": [
            []
        ]
    },
    {
        "id": "94290f8e.2696f",
        "type": "function",
        "z": "38bf2ce1.128f44",
        "name": "lookup phone number",
        "func": "\nvar phonebook = global.get(\"phonebook\") || {}; //get the phonebook out of global\nmsg.phoneNumber = msg.payload; //copy incoming phone number to msg.phoneNumber \n\n//NOTE: at this point (before performing lookup) you may wish to sanitise the phone number (remove any NON numeric values)\nmsg.payload = phonebook[ msg.phoneNumber ]; //lookup the phone number\nreturn msg; //return msg to next node",
        "outputs": 1,
        "noerr": 0,
        "x": 440,
        "y": 140,
        "wires": [
            [
                "2c833a43.c88dc6"
            ]
        ]
    },
    {
        "id": "4cfd773e.1b4c98",
        "type": "mqtt in",
        "z": "38bf2ce1.128f44",
        "name": "",
        "topic": "calls/in/01301627447",
        "qos": "2",
        "datatype": "auto",
        "broker": "34ac2466.a0f64c",
        "x": 200,
        "y": 140,
        "wires": [
            [
                "94290f8e.2696f"
            ]
        ]
    },
    {
        "id": "bce6c64a.a66958",
        "type": "inject",
        "z": "38bf2ce1.128f44",
        "name": "",
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "repeat": "60",
        "crontab": "",
        "once": true,
        "onceDelay": 0.1,
        "x": 170,
        "y": 40,
        "wires": [
            [
                "f214f2b2.c9914"
            ]
        ]
    },
    {
        "id": "2c833a43.c88dc6",
        "type": "mqtt out",
        "z": "38bf2ce1.128f44",
        "name": "mqtt name",
        "topic": "calls/in/01301627447/name",
        "qos": "",
        "retain": "",
        "broker": "34ac2466.a0f64c",
        "x": 650,
        "y": 140,
        "wires": []
    },
    {
        "id": "34ac2466.a0f64c",
        "type": "mqtt-broker",
        "z": "",
        "name": "",
        "broker": "192.168.0.110",
        "port": "1883",
        "clientid": "node-red",
        "usetls": false,
        "compatmode": true,
        "keepalive": "60",
        "cleansession": true,
        "birthTopic": "",
        "birthQos": "0",
        "birthPayload": "",
        "closeTopic": "",
        "closeQos": "0",
        "closePayload": "",
        "willTopic": "",
        "willQos": "0",
        "willPayload": ""
    }
]
4

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