Tabulator: Autocomplete field with suggestions via AJAX

[shadlan]

I'm trying to edit an autocomplete field with suggestions from an AJAX source. The main list is just too big (> 1M records) and I want it updated whenever the user types (I'll send a top 10).

I would like to set an ajaxURL on the editorParams and it should do the rest, something similar to setData(ajaxURL).

While trying to solve it, I've created a function to retrieve this data and assigning it to the editorParams, but this is only called when the user enters the field and not when the user types something.

[olifolkerd]

Im afraid this isnt possible at the moment as the autocomplete lookup function is synchronous.

It will be coming in a later release.

Cheers

Oli :)

[alfred-zaki]

i know this is closed but i kind spent sometime to get to this and though to share how did i kind a lazy solve it.
i took a copy of autocomplete function and call it autocompleteajax so i would use it as a custom editor

i added the following after searchResults = editorParams.searchFunc(term, searchObjs);

if (editorParams.displayAllSearchResults && term !== "") {
searchResults.then(function (value) {
parseItems(value, term);
displayItems = allItems;
input.value = term;
fillList(true);
});
} else {

and moved searchResults.forEach(function (result) {... inside that else

and below is my col def., please note this is part of C# so

editor: autoCompleteAjax,
editorParams: {
displayAllSearchResults: true,
values: {},
searchFunc: async function (term, values) { //search for exact matches
let returnValues = await new Promise((resolve, reject) => {
$.ajax({
url: "http://yourdomain.tld/yourendpointforsearch",
method: "GET",
success: function (data) {
resolve(data);
}
});
});
return returnValues;
}
},

hope that make any sense and help develop such a nice feature into this editor in future releases.

my simple idea is let the search function have await and allow a parameter to display all match result despite if they where on the original list or not.

[GeorgeD19]

So for any one who just wants to copy / paste and get on with their day, here you are:

This is your editor function:

var autoCompleteAjax = function autocomplete(cell, onRendered, success, cancel, editorParams) {
    var self = this,
        cellEl = cell.getElement(),
        initialValue = cell.getValue(),
        vertNav = editorParams.verticalNavigation || "editor",
        initialDisplayValue = typeof initialValue !== "undefined" || initialValue === null ? initialValue : typeof editorParams.defaultValue !== "undefined" ? editorParams.defaultValue : "",
        input = document.createElement("input"),
        listEl = document.createElement("div"),
        allItems = [],
        displayItems = [],
        values = [],
        currentItem = {},
        blurable = true;

    this.table.rowManager.element.addEventListener("scroll", cancelItem);

    function getUniqueColumnValues(field) {
        var output = {},
            data = self.table.getData(),
            column;

        if (field) {
            column = self.table.columnManager.getColumnByField(field);
        } else {
            column = cell.getColumn()._getSelf();
        }

        if (column) {
            data.forEach(function (row) {
                var val = column.getFieldValue(row);

                if (val !== null && typeof val !== "undefined" && val !== "") {
                    output[val] = true;
                }
            });

            if (editorParams.sortValuesList) {
                if (editorParams.sortValuesList == "asc") {
                    output = Object.keys(output).sort();
                } else {
                    output = Object.keys(output).sort().reverse();
                }
            } else {
                output = Object.keys(output);
            }
        } else {
            console.warn("unable to find matching column to create autocomplete lookup list:", field);
        }

        return output;
    }

    function parseItems(inputValues, curentValue) {
        var itemList = [];

        if (Array.isArray(inputValues)) {
            inputValues.forEach(function (value) {
                var item = {
                    title: editorParams.listItemFormatter ? editorParams.listItemFormatter(value, value) : value,
                    value: value,
                    element: false
                };

                if (item.value === curentValue || !isNaN(parseFloat(item.value)) && !isNaN(parseFloat(item.value)) && parseFloat(item.value) === parseFloat(curentValue)) {
                    setCurrentItem(item);
                }

                itemList.push(item);
            });
        } else {
            for (var key in inputValues) {
                var item = {
                    title: editorParams.listItemFormatter ? editorParams.listItemFormatter(key, inputValues[key]) : inputValues[key],
                    value: key,
                    element: false
                };

                if (item.value === curentValue || !isNaN(parseFloat(item.value)) && !isNaN(parseFloat(item.value)) && parseFloat(item.value) === parseFloat(curentValue)) {
                    setCurrentItem(item);
                }

                itemList.push(item);
            }
        }

        if (editorParams.searchFunc) {
            itemList.forEach(function (item) {
                item.search = {
                    title: item.title,
                    value: item.value
                };
            });
        }

        allItems = itemList;
    }

    function filterList(term, intialLoad) {
        var matches = [],
            searchObjs = [],
            searchResults = [];

        if (editorParams.searchFunc) {

            allItems.forEach(function (item) {
                searchObjs.push(item.search);
            });

            searchResults = editorParams.searchFunc(term, searchObjs);

            searchResults.then(function (value) {
                                parseItems(value, term);
                                displayItems = allItems;
                                input.value = term;
                                fillList(true);
             });
        } else {
            if (term === "") {

                if (editorParams.showListOnEmpty) {
                    allItems.forEach(function (item) {
                        matches.push(item);
                    });
                }
            } else {
                allItems.forEach(function (item) {

                    if (item.value !== null || typeof item.value !== "undefined") {
                        if (String(item.value).toLowerCase().indexOf(String(term).toLowerCase()) > -1 || String(item.title).toLowerCase().indexOf(String(term).toLowerCase()) > -1) {
                            matches.push(item);
                        }
                    }
                });
            }
        }

        displayItems = matches;

        fillList(intialLoad);
    }

    function fillList(intialLoad) {
        var current = false;

        while (listEl.firstChild) {
            listEl.removeChild(listEl.firstChild);
        }displayItems.forEach(function (item) {
            var el = item.element;

            if (!el) {
                el = document.createElement("div");
                el.classList.add("tabulator-edit-select-list-item");
                el.tabIndex = 0;
                el.innerHTML = item.title;

                el.addEventListener("click", function () {
                    setCurrentItem(item);
                    chooseItem();
                });

                el.addEventListener("mousedown", function () {
                    blurable = false;

                    setTimeout(function () {
                        blurable = true;
                    }, 10);
                });

                item.element = el;

                if (intialLoad && item.value == initialValue) {
                    input.value = item.title;
                    item.element.classList.add("active");
                    current = true;
                }

                if (item === currentItem) {
                    item.element.classList.add("active");
                    current = true;
                }
            }

            listEl.appendChild(el);
        });

        if (!current) {
            setCurrentItem(false);
        }
    }

    function setCurrentItem(item, showInputValue) {
        if (currentItem && currentItem.element) {
            currentItem.element.classList.remove("active");
        }

        currentItem = item;

        if (item && item.element) {
            item.element.classList.add("active");
        }
    }

    function chooseItem() {
        hideList();

        if (currentItem) {
            if (initialValue !== currentItem.value) {
                initialValue = currentItem.value;
                input.value = currentItem.title;
                success(currentItem.value);
            } else {
                cancel();
            }
        } else {
            if (editorParams.freetext) {
                initialValue = input.value;
                success(input.value);
            } else {
                if (editorParams.allowEmpty && input.value === "") {
                    initialValue = input.value;
                    success(input.value);
                } else {
                    cancel();
                }
            }
        }
    }

    function cancelItem() {
        hideList();
        cancel();
    }

    function showList() {
        if (!listEl.parentNode) {
            while (listEl.firstChild) {
                listEl.removeChild(listEl.firstChild);
            }if (editorParams.values === true) {
                values = getUniqueColumnValues();
            } else if (typeof editorParams.values === "string") {
                values = getUniqueColumnValues(editorParams.values);
            } else {
                values = editorParams.values || [];
            }

            parseItems(values, initialValue);

            var offset = Tabulator.prototype.helpers.elOffset(cellEl);

            listEl.style.minWidth = cellEl.offsetWidth + "px";

            listEl.style.top = offset.top + cellEl.offsetHeight + "px";
            listEl.style.left = offset.left + "px";
            document.body.appendChild(listEl);
        }
    }

    function hideList() {
        if (listEl.parentNode) {
            listEl.parentNode.removeChild(listEl);
        }

        removeScrollListener();
    }

    function removeScrollListener() {
        self.table.rowManager.element.removeEventListener("scroll", cancelItem);
    }

    //style input
    input.setAttribute("type", "search");

    input.style.padding = "4px";
    input.style.width = "100%";
    input.style.boxSizing = "border-box";

    if (editorParams.elementAttributes && _typeof(editorParams.elementAttributes) == "object") {
        for (var key in editorParams.elementAttributes) {
            if (key.charAt(0) == "+") {
                key = key.slice(1);
                input.setAttribute(key, input.getAttribute(key) + editorParams.elementAttributes["+" + key]);
            } else {
                input.setAttribute(key, editorParams.elementAttributes[key]);
            }
        }
    }

    //allow key based navigation
    input.addEventListener("keydown", function (e) {
        var index;

        switch (e.keyCode) {
            case 38:
                //up arrow
                index = displayItems.indexOf(currentItem);

                if (vertNav == "editor" || vertNav == "hybrid" && index) {
                    e.stopImmediatePropagation();
                    e.stopPropagation();
                    e.preventDefault();

                    if (index > 0) {
                        setCurrentItem(displayItems[index - 1]);
                    } else {
                        setCurrentItem(false);
                    }
                }
                break;

            case 40:
                //down arrow

                index = displayItems.indexOf(currentItem);

                if (vertNav == "editor" || vertNav == "hybrid" && index < displayItems.length - 1) {

                    e.stopImmediatePropagation();
                    e.stopPropagation();
                    e.preventDefault();

                    if (index < displayItems.length - 1) {
                        if (index == -1) {
                            setCurrentItem(displayItems[0]);
                        } else {
                            setCurrentItem(displayItems[index + 1]);
                        }
                    }
                }
                break;

            case 37: //left arrow
            case 39:
                //right arrow
                e.stopImmediatePropagation();
                e.stopPropagation();
                e.preventDefault();
                break;

            case 13:
                //enter
                chooseItem();
                break;

            case 27:
                //escape
                cancelItem();
                break;

            case 36: //home
            case 35:
                //end
                //prevent table navigation while using input element
                e.stopImmediatePropagation();
                break;
        }
    });

    input.addEventListener("keyup", function (e) {

        switch (e.keyCode) {
            case 38: //up arrow
            case 37: //left arrow
            case 39: //up arrow
            case 40: //right arrow
            case 13: //enter
            case 27:
                //escape
                break;

            default:
                filterList(input.value);
        }
    });

    input.addEventListener("search", function (e) {
        filterList(input.value);
    });

    input.addEventListener("blur", function (e) {
        if (blurable) {
            chooseItem();
        }
    });

    input.addEventListener("focus", function (e) {
        var value = initialDisplayValue;
        showList();
        input.value = value;
        filterList(value, true);
    });

    //style list element
    listEl = document.createElement("div");
    listEl.classList.add("tabulator-edit-select-list");

    onRendered(function () {
        input.style.height = "100%";
        input.focus();
    });

    return input;
}

This is your column config:

{
    title: "Title",
    field: "field",
    headerFilter: "input",
    editor: autoCompleteAjax,
    editorParams: {
        displayAllSearchResults: true,
        values: {},
        searchFunc: async function (term, values) {
            let returnValues = await new Promise((resolve, reject) => {
                $.ajax({
                    url: "http://yourdomain.tld/yourendpointforsearch",
                        method: "GET",
                        success: function (data) {
                        resolve(data);
                    }
                });
            });

            return returnValues;
        }
    }
}

Ensure your resolved data is in the following format:

{
    "key1": "value1",
    "key2": "value2",
    "key3": "value3"
}

[olifolkerd]

@GeorgeD19

Thanks for posting that, if you want to submit a pull request to make an update to the actual autocomplete editor so that everyone can use it i would be happy to accept the Pull Request.

Cheers

Oli :)

[proautodinc]

@GeorgeD19
This is great, Thanks for that, please add a pull request for this.

[fingers10]

Any chance to bring this Autocomplete With AJAX suggestions functionality in Tabulator?

[OgarOgarovic]

Would be handy, meanwhile I used jQuery autocomplete:

var autocomplAjaxEditor = function(cell, onRendered, success, cancel, editorParams) {
    var editor = document.createElement("input");
    $(editor).autocomplete({
        source: '/your/autocomplete/url/',
        select: function(event, ui) {
            success(ui.item.value);
        }
    });
    ... rest of editor implementation ...

[jmartsch]

Autocomplete With AJAX would be a great enhancement. I want to add an address autocomplete field, which queries the Google Geocoding API and returns a correct address early.

[olifolkerd]

It is already a thing. You can implement your own Ajax request inside the search func