How to pull data from a source in Dialogue Studio

Introduction

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 sipThe Session Initiation Protocol (SIP) is a protocol to make multimedia communication (audio, video and other data communication) possible and it is also used for Voice over IP (VoIP). SIP has similarities with other Internet protocols such as HTTP and SMTP.: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:

      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:

      "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