Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

This use case will enable the Spreadsheet Cascading Drop-Down List to hide a value that has been selected on the row before. It is vital to ensure that the value will not be shown as an option for the next row. It will hide the user's value for the prior row. This will be useful to prevent the user from selecting the same value on the next row. However, it the value that has been selected will not delete the value from the database table and be deleted. The value can still be used and selected once the form has been saved. Once the form has been saved and submitted, the value will still appear for you to use and select for the next subsequent form updating usageinput.


Panel
borderColorgreen
borderWidth1
titleBGColor#ddffcc
borderStylesolid
titleDefinition
English

This tutorial caters to use cases that require a highly customized spreadsheet.

The Spreadsheet form element uses the Handsontable library, specifically version 6.2.2 for Joget Workflow V6.

There is a wealth of plugins and APIs in the library documentation to change the cell appearance, cell selection, dynamic data, dynamic validation, and much more.



Image Modified

Figure 1 : Spreadsheet Form Element Properties - UI - Custom Settings

...

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


...

{
    "afterInit" : function() {
        var hot = this;
        $(hot.rootElement).data("hot", hot);
    },
}


Image Modified

Figure 2: Enable handsontable instance


...

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

The orig_pool array is used to keep track of removed values for potential restoration. It is designed with a Handsontable instance, where the goal is to manage the content of a dropdown menu ("Continent" & " Climate" ONLY) based on changes made in the table. The code dynamically adds or removes items from the dropdown menu source based on user interactions with the table. (See Figure 3)

Code Block
<script>
    $(function(){
            var orig_pool = []; // we will keep a copy of "removed" element for restoring later
            var hot = FormUtil.getField("field1").data("hot");
            
            source_continent = hot.getSettings()['columns'][1]['source']; // we get all the dropdown option from column #1 of the table ("Continent"), call it "source"
            source_climate = hot.getSettings()['columns'][2]['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 of type "edit", we proceed
                    data = {    // prepare the data object
                        source : source_continent, 
                        change : change,
                        orig_pool : orig_pool
                    }
                    
                    if(change[0][1] == 'continent'){
                        data.source = source_continent;
                    }
                    if(change[0][1] == 'climate'){
                        data.source = source_climate;
                    }
                    
                    if(change[0][3] != ''){ removeSource(data); }   // if the changed value is not empty, we remove the data from the "source"
                    else{ addSource(data); }    // otherwise, the change value is empty, meaning it is removed from the table
                                                // we need to add it back to the "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" of country
                                                                             // for use later 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]);    // we add the value back to "source" array
            data.source.sort(); // sort the source array so it looks nice.
        }
    }
</script>



Image Modified

Figure 3 : Add a Javascript in the Custom HTML - Properties - Configure Custom HTML

...

The value under the " Continent" & "Climate" table will only show the value that has not been selected by user. Hence, the selected value from the prior row will be hidden for the next row selection. ( Figure 4)

Image RemovedImage Added

Figure 4 : Expected Outcome

...

Do explore the attached Sample App below and adjust according to your user specification.

View file
nameAPP_kb_dx8_HideSelectedValue_spreadsheet_hide_value_Cascading_Dropdown_List-multi_column.jwa
height250