How to Use SQL Server Json as Dropdown Source

Tags: #<Tag:0x00007efc6c965d60> #<Tag:0x00007efc6c9653d8>
#1

I’m trying to build off the previously asked question from the link reference below. Ultimately, I’m trying to source my dropdown from a SQL database which returns the data in a JSON string. I know it was stated that 1) “you cannot use JSON” as the dropdown source, 2) “the dropdown cell type has to be passed with an array of options”, and 3) I’d need to parse the JSON to do that.

However, do you have a suggestion on how I might do parse the JSON to an array of options?

Sorry if this is a simple question or should be asked elsewhere, but I quite a bit of searching and attempts, I cannot seem to find a straight example.

The key part of the code below is “var vMYDATA = MyRespnse.d” variable which returns the following JSON from the SQL server response.

 [{"Size":"small"},{"Size":"medium"},{"Size":"large"}]

However, I assume it needs to be converted as the below to work with the dropdown. Is there a javascript or jquery function that does that? Or do I need to build something on my own?

 ["small", "medium", "large"]

Code:

const MyContainer = document.querySelector('#MyExample');
const MyHOT = new Handsontable(MyContainer, {
    data: [
        ['Shirt', 'medium'],
        ['Pants', 'large'],
        ['Shorts', 'small']
    ],
    colHeaders: ['Clothes', 'Size'],
    columns: [
        {},
        {
            type: 'dropdown',
            source(query, process) {
                $.ajax({
                    type: "POST",
                    url: "myexample.aspx/LoadSqlServerJson",
                    data: '{MyRecordID: "true" }',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (MyResponse) {
                        var vMYDATA = MyResponse.d;
                        process(vMYDATA);
                    }
                });
            }
        },
    ],
    licenseKey: 'non-commercial-and-evaluation'
});

Link:
Dropdown menu with json data - Getting help / Questions - Handsontable Forum

#2

Hi @ptownbro

With data defined in JSON like this you would need to extract the single strings at first, and then join them together again as array of arrays to be able to use them as a source in dropdown cell type. We don’t have API that would allow you to do this so it has to be done with native JS API.

I created a simple example showing first how to convert array of objects from JSON file to array of arrays, and then merged it single array that can be used as a source: https://jsfiddle.net/handsoncode/cb67zxnk/

#3

Awesome. Thank you! The solution was to implement the Javascript “flat” method as you demonstrated. I hadn’t seen anyone suggest that in the 3 days of research on this! :slight_smile: Thank you again!

2 Likes
#4

Hi @ptownbro

I’m glad that we have a solution! I’ll close this topic now, if you have any other questions feel free to open a new one.

#5