Handsontable: Autocomplete list ID and text

[korssane]

Hi Peers,

i know how to set up an array list in an autocomplete list .
But how come if i have a 2 dimension array data [[1,"name1"],[2,"name2"]] and i want to select names in the autocomplete list but take the IDs as value of the cell ?

is it possible with handsontable

Thanks

[setthase]

You could do it by onBeforeChange callback - mapping name to value.

[korssane]

please do you have a concrete example .. i need it urgent

[nikunj24u]

I am also looking for such solution,
@codename- Can please provide us sample jsfiddle?

[warpech]

Did you consider using Handsontable in Handsontable instead of Autocomplete? http://handsontable.com/demo/handsontable.html

I know the documentation lacks an example for the thing that you ask about, but it should be possible. I will try to write an example soon

[IdleMach]

This is essential for Handsontable to be useful for me. A table might have 2 or 3 of them. So I created a new cell type called "lookup" with very basic mapping in a branch. It's just an extension of the "autocomplete" type. Here is a demo:
http://jsfiddle.net/IdleMach/zs4tQ/9/

But it's brittle and convoluted...How could it be done so it's less convoluted?

https://github.com/IdleMach/jquery-handsontable/blob/master/src/editors/lookupEditor.js .

[IdleMach]

(This version uses strict: http://jsfiddle.net/IdleMach/zs4tQ/11/ )

[setthase]

@IdleMach - nice job! :+1:

Could you try do Pull Request for that? It will be helpful if this could be also with tests for that.

Maybe @warpech take look at that and find some clever way to optimize that or find way to do this less convoluted.

[IdleMach]

Well things like copy and paste, and drag copy don't go through Editor and you end up with the codes/id replicated and not the text. In hindsight sub classing the Editor isn't really what's needed here. A data converter pattern is what's needed. The Renderer is great for mapping it one way, but onBeforeChange isn't good for the other way. We need an onBeforeChange delegate per column and triggered per cell... I wonder if javascript getter and setters are sufficient which I only just found out JS is capable of, so going to look into that.

[IdleMach]

So getters and setters work but Handsontable needs to create a new instance of the class when adding new records... http://jsfiddle.net/IdleMach/zs4tQ/22/ any ideas how to do that?

[kritochin]

@IdleMach your demos aren't working (only the last one is working). is anyhting wrong?
thank you very much.

[motin]

Afaik, this is not possible currently without a plugin like https://github.com/trebuchetty/Handsontable-select2-editor/

[ping2anilsharma]

How to implement auto complete with key value pair.On selection it should give the attached key.Autocomplete is failing in this case.Suppose I have values like:
Tes1(101)
Test2(102)
if same name is there then auto complete gets confuse.How I can implement this scenario.
Comments are welcome.....

Need urgent solution.....

[motin]

@ping2anilsharma Handsontable currently has no support for storing a different value in the cell than the one displayed (for instance an id and a label).
We worked around this by setting the cell's value to the label and then we translate this back to an id on the server. Works if your labels are unique.

[mazing]

Aren't there any work-arounds if my labels aren't unique? I think this is a quite important feature so someone might have figured this out already :-D

[JD-Robbs]

EDIT: ooops, I thought this was two years old, but just realised the last activity was mid-March. So I guess there's no workaround so far. :smiley:

Sorry for digging this up, but have there been any changes that implement such functionality? I tried sending objects into the dropdown-source and checked the documentation - no luck.

This would be fantastic as the display values are not always the right way of storing the actual underlying data. E.g. when wanting to display survey-data where the responses ('Extremely important', 'Definitely agree') correspond to numeric items on a scale.

[brownrw8]

What is the purpose of having a data schema if you can't update it through a dropdown?

[ping2anilsharma]

I will suggest to go for spreadjs instead of Handsontable. A great tool tool which gives every feature of the excel. Try its samples. I have created lot of projects using this. Its really a great tool and good experience to work with spreajd Wijmo.

[brownrw8]

Thanks  I will look into it!  

Sent from my U.S. Cellular® Smartphone

-------- Original message --------

From: Anil Sharma [email protected]

Date:06/29/2015 1:21 AM (GMT-05:00)

To: handsontable/handsontable [email protected]

Cc: brownrw8 [email protected]

Subject: Re: [handsontable] Autocomplete list ID and text (#584)

I will suggest to go for spreadjs instead of Handsontable. A great tool tool which gives every feature of the excel. Try its samples. I have created lot of projects using this. Its really a great tool and good experience to work with spreajd Wijmo.

—
Reply to this email directly or view it on GitHub.

[ping2anilsharma]

This is the link what I have used and its excellent. Please go through all demos. It gives excel look and feel and inbuilt formula's capability, formula's intelligence etc...

http://wijmo.com/demo/spreadjs/samples/ExplorerSample/

Regards
Anil Sharma

[motin]

@ping2anilsharma Handsontable currently has no support for storing a different value in the cell than the one displayed (for instance an id and a label).

Sorry for digging this up, but have there been any changes that implement such functionality? I tried sending objects into the dropdown-source and checked the documentation - no luck.

Yes, today it is fairly easy to implement this. You can use a select2 dropdown that selects an ID for the cell's value (https://github.com/trebuchetty/Handsontable-select2-editor), then assign a custom renderer for the cell to display another value than the ID (such as a label, an image or any other HTML) http://docs.handsontable.com/0.15.0/demo-custom-renderers.html

[tdjun]

I made new cell type which was customized autocomplete.
https://github.com/tdjun/Handsontable-autocomplete2

[luisdav86]

You can fill out the drop down list with objects that can handle toString() function like this

var value = {};
      value.name = 'name';
      value.id = 1;
      value.toLowerCase = function () { return value.name.toLowerCase() };  
      value.substr = function (start, end) { return value.name.substr(start, end) };
      value.replace = function (match, other) { return value.name.replace(match, other) };
      value.toString = function() { return value.name; }

 columns: [
                   { data: 'value.name', title: 'Project', width: 230, type: 'dropdown', allowInvalid: false}
                ]

So you will be able to use object in the dropDown

[delsner]

@tdjun thanks for sharing your cell type - it's a great idea to address the problem. However, I'm having trouble implementing a cell type autocomplete2 with html-renderer as I don't get any afterChange-callback and thus the content of the cell doesn't change when I select another option in the dropdown. Do you have a solution for this?

return {
    type: 'autocomplete2',
    renderer: 'html',
    data: data,
    source: function(value, rowObj, process) {
      someCall().$promise.then(function(res) {
      process(res);
      });
    },
   dataField: 'name',
   labelTemplate: '{{name}}'
}

[optilude]

If anyone is interested, here is a cell type that supports key/value pairs. It is perhaps a bit hacky, but it works for me.

import Handsontable from 'handsontable';
import _ from 'lodash';

/* Key/value autocomplete editor.

Use a cell like this:

{
    title: "User",
    data: "user",
    ...KeyValueAutocompleteCell,

    // the autocomplete dropdown is a handsontable-in-handsontable; configure
    // it here.
    handsontable: {
        // should correspond to the data fetched in `source()`
        dataSchema: {name: null, email: null},
        columns: [
            {title: "Name", width: 150, data: 'name'},
            {title: "Email", width: 200, data: 'email'}
        ],
        // return the value to be saved in the table when a selection is made
        getValue() {
            const selection = this.getSelected();
            return this.getSourceDataAtRow(selection[0]).id;
        }
    },

    // this function will be called to match the title set in the text field
    extractTitle: row => row? row.name : "",

    strict: true,
    filteringCaseSensitive: false,

    source: (query, process) => {

        // fetch data here, then call process with a list of values. Each
        // value should be an object at least the same fields as the columns
        // of the inner handsontable.

        // here's some test data...
        setTimeout(() => {
            process([
                {id: 'a', name: "User Alpha", email: "[email protected]"},
                {id: 'b', name: "User Beta", email: "[email protected]"},
                {id: 'd', name: "User Delta", email: "[email protected]"},
            ])
        }, 100);
    }
}

In the main handsontable, you can preload the data for the selected item so that
the cell renders the right thing as returned by `extractTitle`. To do that,
the `data` of the handsontable instance has to contain objects like this:

    {
        // other props
        user: 'a', // the id, i.e. the thing returned by `getValue()` above
        __autocomplete__: {
            user: {id: 'a', name: "User Alpha", email: "[email protected]"}
        }
    }

When the user selects a value using the editor, the `__autocomplete__` key will
be created in the relevant record in the table's source `data`. This may
surprise you later, and you will likely need to filter it out later if you use
the data correctly.
*/

const AutocompleteEditor = Handsontable.editors.AutocompleteEditor,
      HandsontableEditor = Handsontable.editors.HandsontableEditor;

export const KeyValueAutocompleteEditor = AutocompleteEditor.prototype.extend();

_.extend(KeyValueAutocompleteEditor.prototype, {

    metadataProp: '__autocomplete__',

    getMetadata() {
        let md = this.instance.getSourceDataAtRow(this.row)[this.metadataProp];
        return md? md[this.prop] : undefined;
    },

    setMetadata(value) {
        let rowData = this.instance.getSourceDataAtRow(this.row),
            md = rowData[this.metadataProp];
        if(!md) {
            md = rowData[this.metadataProp] = {};
        }
        md[this.prop] = value;
    },

    open() {
        AutocompleteEditor.prototype.open.apply(this, arguments);

        // we don't want this hook to run, it stringifes everything
        // also: no good API for this if we don't have a refernece to the the
        // original hook :(

        const bucket = Handsontable.hooks.getBucket(this.htEditor),
              bucketList = bucket.afterRenderer; // see what I did there?

        bucketList.forEach(cb => {
            this.htEditor.removeHook('afterRenderer', cb);
        });
    },

    prepare() {
        AutocompleteEditor.prototype.prepare.apply(this, arguments);

        // We don't support stripping tags (allowHtml), trimming list (filter),
        // sorting by relevance (make the source function sort), or trimming
        // the dropdown... yet.
        this.cellProperties.filter = false;
        this.cellProperties.allowHtml = true;
        this.cellProperties.sortByRelevance = false;
    },

    beginEditing(initialValue) {
        // Set initial editor value based on the title that was last used
        let hint = this.getMetadata();

        if(hint !== undefined && this.cellProperties.extractTitle !== undefined) {
            initialValue = this.cellProperties.extractTitle(hint);
        }

        HandsontableEditor.prototype.beginEditing.apply(this, [initialValue]);
    },

    updateChoicesList(choices) {
        this.choices = choices;
        this.htEditor.loadData(choices);
        this.updateDropdownHeight();
        this.flipDropdownIfNeeded();
        this.highlightBestMatchingChoice(this.findBestMatchingChoice());
        this.focus();
    },

    highlightBestMatchingChoice(index) {
        if (typeof index === 'number') {
            let endCol = this.htEditor.countCols() - 1;
            this.htEditor.selectCell(index, 0, index, endCol);
        } else {
            this.htEditor.deselectCell();
        }
    },

    findBestMatchingChoice() {
        let bestMatch = {},
            choices = this.choices,
            value = this.getValue(),
            valueLength = value.length,
            filteringCaseSensitive = this.cellProperties.filteringCaseSensitive,
            currentItem,
            indexOfValue,
            charsLeft;

        if(!filteringCaseSensitive) {
            value = value.toLowerCase();
        }

        for(let i = 0, len = choices.length; i < len; i++){
            currentItem = this.cellProperties.extractTitle(choices[i]);

            if(currentItem && !filteringCaseSensitive) {
                currentItem = currentItem.toLowerCase();
            }

            if(valueLength > 0){
                indexOfValue = currentItem.indexOf(value);
            } else {
                indexOfValue = currentItem === value ? 0 : -1;
            }

            if(indexOfValue == -1) continue;

            charsLeft =  currentItem.length - indexOfValue - valueLength;

            if(
                typeof bestMatch.indexOfValue == 'undefined' ||
                bestMatch.indexOfValue > indexOfValue ||
                (bestMatch.indexOfValue == indexOfValue && bestMatch.charsLeft > charsLeft)
            ){
                bestMatch.indexOfValue = indexOfValue;
                bestMatch.charsLeft = charsLeft;
                bestMatch.index = i;
            }

        }

        return bestMatch.index;
    },

    finishEditing(restoreOriginalValue) {
        if(this.htEditor) {
            let selection = this.htEditor.getSelected();

            if(selection) {
                let rowData = this.htEditor.getSourceDataAtRow(selection[0]),
                    value = this.htEditor.getValue();

                if(rowData !== undefined && value !== undefined) {
                    this.setMetadata(rowData);
                }
            }
        }

        AutocompleteEditor.prototype.finishEditing.apply(this, arguments);
    },

    getDropdownHeight() {
        // TODO: We should find a better calculation here
        return undefined;
    }
});

const KeyValueAutocompleteCell = {
    editor: KeyValueAutocompleteEditor,
    renderer: (instance, td, row, col, prop, value, cellProperties) => {
        if(value !== undefined && value !== null) {
            let md = instance.getSourceDataAtRow(row)[KeyValueAutocompleteEditor.prototype.metadataProp];
            if(md) {
                let hint = md[prop];
                if(hint !== undefined) {
                    value = cellProperties.extractTitle(hint);
                }
            }
        }

        Handsontable.AutocompleteRenderer(instance, td, row, col, prop, value, cellProperties);
    }
};

export default KeyValueAutocompleteCell;

[AMBudnik]

same issue as https://github.com/handsontable/handsontable/issues/2305

[evenmind]

Hi, how can I implement the @tdjun solution with strict:true parameter ?