Code-based Actions: Retrieve filtered values from an external database

Let’s say you have a database of products that are distinguishable on multiple properties, like the type of product, the brand, and the color:

When the user says: “I’m looking for a black Samsung TV”, the bot responds with something like “TVSamsungBlack is just the right product for you!” and sends the URL for the product page.

To set this up, we need to do 4 things:

• Capture the user input.
• Retrieve the database.
• Extract the right product and product URL from the database.
• Return the values to the user.

Capture the user input

For capturing the user input, use entities for the properties, and combine them all in a single intent.

We set all the entities to required, so we can ask the user for potential missing properties:

Learn more about How to capture user input.

Retrieve the database

To retrieve our database, import it to restdb.io. We first need to convert the Google Spreadsheet to a CSV file.

This CSV file can directly be uploaded to restdb.io.

Click on the gear icon on the top right corner to enter Developer mode. Click the settings icon, and then the JavaScript API Docs.

Extract the right product and product URL from the database

Copy and paste your URL and API Key from restdb.io into this code, and also change the entity names to yours:

async payload => {

   var request = require("request");  
  var options = { method: 'GET',  
  url: '<https://test1-9795.restdb.io/rest/database-example-sheet-1'>,  
  headers:  
   { 'cache-control': 'no-cache',  
     'x-apikey': '6abea6430b6ef2e86c7197f7df47c0076721c' } };

  let p = new Promise((res,rej) => request(options, function (error, response, body) {  
    if (error) rej(error)
res(body)
    }))

  let body = await p  
  body = JSON.parse(body)

  console.log('b',body)

  const product_type = payload.params.product_type[0].value,  
  brand = payload.params.brand[0].value,  
  color = payload.params.color[0].value  
  console.log(product_type, brand, color)

  const data = body.filter((e) => e.product_type === product_type && e.brand === brand && e.color === color);

  console.log(data)  
  return {  
    params: {  
      // Using a spread operator, we add all provided params in the result  
      ...payload.params,  
      // Now we override the product param  
      product: \[{  
        value: data[0].product,  
        match: 'product'  
      }],  
      product_url: \[{  
        value: data[0].product_url,  
        match: 'product_url'  
      }]  
    }  
  }

}

You can add more filters if you have 4 or 5 properties instead of the 3 in this example.

Return the values to the user

After the code action is triggered, the bot has values for the parameters “product” and “product_url’, so we can use them in bot responses like any other parameter in Flow:

Ensure that you test this flow.