Hide Selected Values in Spreadsheet Drop-Downs

Enables the Spreadsheet Cascading Drop-Down List to hide a value selected in the previous row. This ensures that the value will not be shown as an option for the next row, preventing users from selecting the same value consecutively. However, the selected value will remain available once the form is saved and reloaded.

This tutorial addresses examples that require a highly customized spreadsheet. The Spreadsheet form element in Joget uses the Handsontable library, specifically version 6.2.2 for Joget DX 8. The library offers a wealth of plugins and APIs to customize cell appearance, selection, dynamic data, validation, and much more.

You can add more spreadsheet properties that are available in the library documentation. The configuration is formatted as JSON. Usually, a single object and value in Custom Settings would suffice for simple configurations. Combine this with a Custom HTML form element for more complex configurations to complete the functionality.

See the section Spreadsheet Deep Customizations for more examples.

To achieve the desired result, you use a Custom HTML form element and Custom Settings within the form element properties.

Instructions

Each option in the spreadsheet is a list of data from another form with a Form Data Store configuration. This tutorial will guide you step-by-step on configuring the Spreadsheet Form Element for this example.

To enable the Handsontable instance to be interactable, paste the following code into the HTML elements from Form Builder > Spreadsheet Configuration > UI > Custom Settings (JSON).
```
{
    "afterInit": function() {
        var hot = this;
        $(hot.rootElement).data("hot", hot);
    }
}
```

Add a Custom HTML form element to the form and use the following JavaScript code to adjust the content of the Continent dropdown menu dynamically based on user edits in the table. This script removes values from the dropdown if they are edited to be non-empty and adds them back if they are edited to be empty.

<script>
    $(function(){
        var orig_pool = []; // we will keep a copy of "removed" elements for restoring later
        var hot = FormUtil.getField("field1").data("hot");
        source = hot.getSettings()['columns'][1]['source']; // get all dropdown options from column #1 of the table ("Continent"), call it "source"
                                                            // p.s, column count starts from 0. so column #1 = second column
        hot.addHook('afterChange', function(change, type){ // set a hook / event listener to trigger after any change on the table
            if(type == 'edit'){ // if the change type is "edit", proceed
                data = { // prepare the data object
                    source: source,
                    change: change,
                    orig_pool: orig_pool
                }
                if(change[0][3] != '') { removeSource(data); } // if the changed value is not empty, remove the data from "source"
                else { addSource(data); } // otherwise, if the changed value is empty, add it back to "source"
            }
        })
    })

    function removeSource(data){
        slice_index = data.source.indexOf(data.change[0][3]); // find the index of the continent in the "source" array
        data.orig_pool.push(data.source.slice(slice_index, slice_index + 1)); // copy this value to our "original pool" for later use in addSource()
        data.source.splice(data.source.indexOf(data.change[0][3]), 1); // splice the value out from "source" array
    }

    function addSource(data){
        data.orig_pool.splice(data.source.indexOf(data.change[0][2]), 1); // splice the value out from "original pool" array
        if(data.change[0][2] != '') { // if the initial value of the cell is not empty,
            data.source.push(data.change[0][2]); // add the value back to "source" array
            data.source.sort(); // sort the source array so it looks nice
        }
    }
</script>

The Continent dropdown will only show values not selected by the user in previous rows. This ensures that each value is unique per row. However, the values will reappear after the form has been saved and reloaded.