How to pull data from a source in Dialogue Studio

Another advance feature you can add to your flow is retrieving data based on input, for example customer phone number. Below we will have an example, first what you can do with the incoming customer uri.

Preview

Preparations

In this scenario we are going to pull data from a database. In this example we will be using the following data set format:

id	FirstName	LastName	SipAddress
1	Keith	Sanders	sip:keiths@domain.com

Configure

Before starting we need to add the nodes to connect to SQL. This can be done by adding it to the pallet:

Steps:

1. Open the menu in the top right

2. Select Manage palette

3. Open the tab Install

4. Search for the module node-red-contrib-mssql

5. Click install

We are going to start with a “Incoming conversation” node. This node only listens to audio/video and is connected to our server using SignalR.

Steps:

1. Drag and drop Incoming Call node

2. Open Node

   1. Select / Configure server

   2. Filter on Audio/Video

 

Next we are going to lookup the customers uri in a SQL database.

Steps:

1. Drag and drop MSSQL node

2. Open Node

   1. Configure Connection

      1. Enter Server name

      2. Enter Username

      3. Enter Password

      4. (Optional) Enter Domain if you are using Microsoft Authentication

      5. Enter Database

   2. Enter the following Query:

      Copy

      SQL

      SELECT FirstName, LastName
      FROM users
      where sipaddress = '{{{session.sipUri}}}'

3. Connect end of Incoming Call node to begin MSSQL node

Now we want to check if we found any results. We will do this using a switch.

Steps:

1. Drag and drop Switch node

2. Open Node

   1. Add 2 rules

      1. Is empty

      2. Otherwise

3. Connect end of MSSQL node to begin Switch node

If there are results found, they will be stored in the msg.payload. For now we want to store this information so we can use it later. This can be done using a Change node.

Steps:

1. Drag and drop Change node

2. Open Node

   1. Create 2 rules

      1. Set: msg.FirstName

         to: msg.payload[0].FirstName

      2. Set: msg.LastName

         to: msg.payload[0].LastName

3. Connect otherwise from Switch node to begin Change node

Next we want to greet the customer with there first and last name. This will be done using a Say node with an expression.

Steps:

1. Drag and drop Say node

2. Open Node

   1. Change String to Expression

   2. Use the following expression:

      Copy

      Expression

      "Hello " & FirstName & " " & LastName

3. Connect end Change node to begin Say node

We also want a message if we didn't find the customer. This will be done with a Say node.

Steps:

1. Drag and drop Say node

2. Open Node

   1. Enter a text notifying the customer we didn't find him.

3. Connect end Change node to begin Say node

Finally we want to wrap up this flow with a Disconnect node.

Steps:

1. Drag and drop Disconnect node

2. Connect end of both Say nodes to begin Disconnect node