Handsontable Replace autocomplete values with key before posting

Question

I am using HandsOnTable to make editing database tables more interactive on my site.

HandsOnTable fulfils nearly all my requirements except that some columns in my database actually store foreign keys rather than local string values.

In the UI I would like these columns to appear as dropdown menus where the user selects a readable value mapped to the previously mentioned foreign key (I.e. something like an HTML name/value select).

Unfortunately HandsOnTable does not have such a cell type. The closest thing to it is autocomplete. This allows me to create a dropdown, but it only contains values; no corresponding keys. Here is how it is created:

"source": ["Jebediah", "Bob", "Bill", "Buzz"]

So what I am planning is to send two Json strings from the server:

One containing the parameters needed by HandsOnTable to render the table:

{
    "data": [
        { "ID": 1, "Description": "Crude", "Volume": 204, "Customer": "jebediah" },
        { "ID": 2, "Description": "Hidrogen", "Volume": 513, "Customer": "Bob" },
        { "ID": 3, "Description": "Coal", "Volume": '67', "Customer": "Bill" },
        { "ID": 4, "Description": "Wood", "Volume": '513',  "Customer": "Buzz" }
    ],
    "columns": [
        { "data": "ID", "type": "numeric" },
        { "data": "Description", "type": "text"},
        { "data: "Volume", "type": "numeric" },
        { "data": "color", "type": "autocomplete", "strict": "true",
            "source": ["Jebediah", "Bob", "Bill", "Buzz"]}
    ]
}

The second mapping keys to values

{
    "mappings": [
        {"key": 0, "value": "Jebediah"}, 
        {"key": 0, "value": "Bob"},
        {"key": 0, "value": "Bill"}, 
        {"key": 0, "value": "Buzz"}
    ]
}

So far so good. Now for the tricky part:

HandsOnTable has a function (getData()) that allows me to retrieve the tables data as a Json string ready to be sent back to the server:

var jdata = myHandsOnTable.getData();

Where jdata would look something like this:

"data": [
    { "ID": 1, "Description": "Crude", "Volume": 204, "Customer": "jebediah" },
    { "ID": 2, "Description": "Hidrogen", "Volume": 513, "Customer": "Bob" },
    { "ID": 3, "Description": "Coal", "Volume": '67', "Customer": "Bill" },
    { "ID": 4, "Description": "Wood", "Volume": '513',  "Customer": "Buzz" }
]

Now before posting, I would like to replace that values for the Customer node with their matching pair key within the mappings json string.

How can I best achieve this in JavaScript/JQuery?

Is there a function that works something as follows?:

jdata.replaceNode('node', mappings)

Thanks

Answer 1

I had a similar issue and here's what I did...

For each foreign key column, I stored 2 values in handsontable; one for the id itself, which I set as a hidden column and the other is the user friendly readable text value as dropdowns.

Everytime the value of a dropdown is changed, I also change the corresponding hidden id. In my case I have a dropdown outside the handsontable as a filter which I use to map key/value pairs, but you could use Hashtables or anything else.

Now the code...

Handsontable config:

afterChange: function (changes, source) { AfterChange(changes, source); }

After change event (called everytime there is a change in the table):

function AfterChange(Changes, Source) {

    if (Source === 'loadData') {
        return; //don't save this change
    }
    var rowIndex = 0, columnID = 1, oldTextVal = 2, newTextVal = 3, ntv = '', nv = '';
    $(Changes).each(function () {
        if (this[columnID] === 'CategoryID') {
            // Do nothing...
            //To make sure nothing else happens when this column is set through below
        }
        else if (this[columnID] === 'CategoryName') {
            ntv = this[newTextVal];
            //This is where I do my mapping using a dropdown.
            nv = $('#CategoriesFilterDropdown option').filter(function () { return $(this).text() === ntv; }).val();
            //13 is my CategoryID column
            $container.handsontable('setDataAtCell', this[rowIndex], 13, nv);
        }
    });
    }
}

This way, you change the foreign keys as you and don't need to loop through it all before saving. It also makes it easy to send the table data as is back to server.

In summary,

• The user interacts with CategoryName column (which is of type autocomplete).
• The CatgoryID column is hidden to the user by setting the column width to 0 using the colWidths option of handsontable.
• When the CategoryName field changes, use afterChange event to set the corresponding CategoryID column. In my case, I use a dropdown somewhere else on the page to map Name => ID, but you can use other means such as a hashtable.

I hope it makes sense...