# How to receive a phone number by MQTT then perform an Excel style vlookup to obtain a caller name?
**URL:** https://discourse.nodered.org/t/how-to-receive-a-phone-number-by-mqtt-then-perform-an-excel-style-vlookup-to-obtain-a-caller-name/25764
**Category:** General
**Created:** [29 April 2020 20:31 UTC](https://discourse.nodered.org/t/how-to-receive-a-phone-number-by-mqtt-then-perform-an-excel-style-vlookup-to-obtain-a-caller-name/25764 "2020-04-29T20:31:24Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@dclear](https://discourse.nodered.org/u/dclear)
#### Post date: [29 April 2020 20:31 UTC](https://discourse.nodered.org/t/how-to-receive-a-phone-number-by-mqtt-then-perform-an-excel-style-vlookup-to-obtain-a-caller-name/25764/1 "2020-04-29T20:31:24Z")
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
```auto
[
{
"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": ""
}
]
```
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [29 April 2020 21:42 UTC](https://discourse.nodered.org/t/how-to-receive-a-phone-number-by-mqtt-then-perform-an-excel-style-vlookup-to-obtain-a-caller-name/25764/2 "2020-04-29T21:42:42Z")
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..._
```auto
{
"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`
```auto
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
```
---
### Author:  [@dclear](https://discourse.nodered.org/u/dclear)
#### Post date: [30 April 2020 09:57 UTC](https://discourse.nodered.org/t/how-to-receive-a-phone-number-by-mqtt-then-perform-an-excel-style-vlookup-to-obtain-a-caller-name/25764/3 "2020-04-30T09:57:24Z")
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
```auto
[
{
"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": ""
}
]
```
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [14 May 2020 09:57 UTC](https://discourse.nodered.org/t/how-to-receive-a-phone-number-by-mqtt-then-perform-an-excel-style-vlookup-to-obtain-a-caller-name/25764/4 "2020-05-14T09:57:25Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.