Filter dropdown field with excel as datasource based on another dropdown field chosen value

Forms Public Forms
1

Hello,

I'm trying to filter a dropdown datasource using the value of another dropdown field as filter value.

The scenario is:

-dropdown field 1 based on a fix list with office names (Office1, Office2, Office3)
-dropdown field 2 based on a excel file containing a table with employees's names

If i choose Office1 i want that the second dropdown field returns only the employees for the choosen office and not all of them.

Is it possible in public forms?

Thanks in advance for your help.

Andrea

2

Hello @vigand,

There is no out-of-the-box functionality to filter options in the drop-down field, only using custom JavaScript code. It's a little tricky, but pretty easy to implement.

For your scenario you must add 3 dropdown fields to the form:

  1. Field connected to external Excel file
  2. Field with a list of office names
  3. Field with a list of employees' names

The field that is connected to external Excel field will be used to get data and filter it by Office Name.

Select the Excel file as the data source. Choose the Office column in Text column and Employee name in Value column:
image

You can hide this field by default. Add display: none; to the field Style property.

And add this code to filter the list of employees by office and fill the Office dropdown field with the filtered data.

fd.rendered(function() {
    //function that filters employees' names
    function filterNames() {
        var source = fd.field('Source').widget.dataSource.data()
        //filter stores by the selected office
        var stores = source.filter(s => s.text == fd.field('Office').value);

        //Populate Store dropdown with filtered stores
        fd.field('EmployeeName').widget.setDataSource({
            data: stores.map(function(i) { return i.value})
        });
    }

    //call function on form load
    filterNames();

    //call function when field value changes
    fd.field('Office').$on('change', function() {
        fd.field('EmployeeName').clear();        
        filterNames();
    })
});
3

Hi,

it works like a charm!

Thank you very much.

1 Like