Loading and filtering dropdown choices from a local CSV or Excel file in SurveyJS with AlaSQL (No REST service needed!)

Introduction

Choices for multiple choice questions (radiogroup, dropdown, checkbox, etc.) in SurveyJS can be loaded in several different ways.

If the list of choices is static, and relatively small, you can define it within the survey schema JSON. If you have a large list you should avoid using this approach. It may bloat your JSON data, which in turn will affect the initial loading time of your survey.

Another reason to look for alternative ways of loading your choices is to accommodate dropdowns that need to be dynamic. In other words, the list of choices could be different, depending on responses to earlier questions in the survey. SurveyJS provides a great mechanism for doing that through the choicesByUrl property. There is a great example demonstrating this feature, including the option to apply choice filtering on cascading dropdowns, on the official site. However, being a front-end library, SurveyJS does not cover the code behind the API endpoint that delivers the choices data. Since such an endpoint would depend on the server technology stack, and since it is relatively trivial to put together, I won’t go into any code samples.

However, sometimes you might need to load a large list of filtered choices, without having access to any server-side API. This blog post will show you one of the ways you can do that, by storing your data in a local CSV file, and querying it with a neat little library, called AlaSQL.

Prerequisites

Since we are going to be recreating the official “choicesByUrl” example on the SurveyJS website, we will need to find a spreadsheet with a list of countries by content. There is a great repository on GitHub, which provides this kind of data under the MIT License: https://github.com/annexare/Countries.

We will also need to add a reference to the AlaSQL library, which is also available under the MIT License: https://github.com/annexare/Countries. There are many ways you can bundle this compact library with your application, so I will let you refer to the installation section of the docs: https://github.com/AlaSQL/alasql#installation

Survey model

Our survey will feature the so called cascading dropdowns - a very common UI approach for filtering, based on parent categories. For our example we will put together a simple survey, containing two dropdown fields - one for continents, and one for countries:

{
 elements: [
   {
     type: "dropdown",
     name: "continent",
     title: "Select the continent..."
   },
   {
     type: "dropdown",
     name: "country",
     title: "Select the country...",
     isRequired: true
   }
 ]
}

Data storage

AlaSQL supports a variety of formats, including CSV, Excel, and JSON (for a full list check https://github.com/AlaSQL/alasql/wiki#alasql-qa. For this example we will grab the countries.csv file from https://github.com/annexare/Countries/blob/master/dist/. It contains more country-related data than we need, but that won’t be a problem. Here’s a sample of the data:

We need to place the file somewhere in the file structure of our website, where it can be publicly served. You should also make sure that your web server is configured to serve CSV files through the “text/csv” mime type mapping.

*The code sample accompanying this article is hosted on CodeSandbox. The correct folder to place our CSV file within their structure is ‘Public’, even though once the sandbox is executed it will appear to be in the same location as the script files.

You should make sure that your data file does not contain any sensitive information, because anyone will be able to download it off your website!

Querying the CSV data

The tricky part is that AlaSQL needs to issue a separate request to the server in order to load the data from the static file. This means we need to work in an asynchronous manner and populate our choices in a callback function, once the query has finished executing.

AlaSQL uses promises, so we can create a function to load our list of continents as follows:

function loadContinents(survey, destinationQuestionName) {
 alasql.promise(
     "SELECT DISTINCT [Continent] AS [value], [Continent] AS [text] FROM csv('./countries') ORDER BY [Continent]"
   )
   .then(function (results) {
     var q = survey.getQuestionByName(destinationQuestionName);
     q.choices = results;
   })
   .catch(console.error);
}

Each choice element in a SurveyJS dropdown requires a value item and a text item. In the case of continents value and text will be the same, which is why the SQL query is fetching the [Continent] column twice. Aliasing the columns as [value] and [text] ensures we will get a list of objects which can be directly assigned to the ‘choices’ property of the dropdown, without the need for any conversion.

Filtering the second dropdown

The list of countries that will get loaded into the second dropdown needs to be filtered by the selected continent. For that purpose we will create a function, similar to the one for continents, but with a WHERE clause in the SQL statement:

function loadCountries(survey, destinationQuestionName, continent) {
   alasql.promise(
       "SELECT DISTINCT [Code] AS [value], [Name] AS [text] FROM csv('./countries') WHERE [Continent] = ? ORDER BY [Name]",
       [continent]
     )
     .then(function (results) {
       var q = survey.getQuestionByName(destinationQuestionName);
       q.choices = results;
     })
     .catch(console.error);
}

Parameters within AlaSQL queries are represented by question marks and passed as the second argument of the promise, in the form of an array.

Wiring up the SurveyJS events

Loading the list of continents should happen as soon as the dropdown is rendered. We will use the onAfterRenderQuestion event https://surveyjs.io/Documentation/Library?id=surveymodel#onAfterRenderQuestion:

function onAfterRenderQuestion(sender, options) {
  if(options.question.name === 'continent') {
    loadContinents(sender, options.question.name);
  }
}

survey.onAfterRenderQuestion.add(onAfterRenderQuestion);

The list of countries, however, should get loaded each time the value of the continents dropdown gets changed. We will use the onValueChanged event https://surveyjs.io/Documentation/Library?id=surveymodel#onValueChanged:

function onValueChanged(sender, options) {
  if (options.question.name === "continent") {
    loadCountries(sender, "country", options.value);
  }
}

survey.onValueChanged.add(onValueChanged);

The complete example

A working sample using the React flavor of SurveyJS is available at https://codesandbox.io/s/loading-and-filtering-surveyjs-dropdown-choices-with-alasql-kpxzny

Here’s a listing of the SurveyComponent.jsx file:

import React from "react";

import { StylesManager, Model } from "survey-core";
import { Survey } from "survey-react-ui";

import alasql from "alasql";

import "survey-core/defaultV2.css";
import "./index.css";
import { json } from "./json";

StylesManager.applyTheme("defaultV2");

function loadContinents(survey, destinationQuestionName) {
 alasql
   .promise(
     "SELECT DISTINCT [Continent] AS [value], [Continent] AS [text] FROM csv('./countries') ORDER BY [Continent]"
   )
   .then(function (results) {
     var q = survey.getQuestionByName(destinationQuestionName);
     q.choices = results;
   })
   .catch(console.error);
}

function loadCountries(survey, destinationQuestionName, continent) {
 alasql
   .promise(
     "SELECT DISTINCT [Code] AS [value], [Name] AS [text] FROM csv('./countries') WHERE [Continent] = ? ORDER BY [Name]",
     [continent]
   )
   .then(function (results) {
     var q = survey.getQuestionByName(destinationQuestionName);
     q.choices = results;
   })
   .catch(console.error);
}

function onAfterRenderQuestion(sender, options) {
 if (options.question.name === "continent") {
   loadContinents(sender, options.question.name);
 }
}

function onValueChanged(sender, options) {
 if (options.question.name === "continent") {
   loadCountries(sender, "country", options.value);
 }
}

function SurveyComponent() {
 const survey = new Model(json);

 survey.onAfterRenderQuestion.add(onAfterRenderQuestion);
 survey.onValueChanged.add(onValueChanged);

 return <Survey model={survey} />;
}

export default SurveyComponent;