How to display options of three columns within one dropdown

[Deleted User]

Test case: REMOVED

Description of problem: need to show results of area 1, area 2, and area 3 in the "Area" dropdown menu

kthorngren

As I understand it you want the Area search filter to include options from Area 1, Area 2 and Area 3 and to search the three columns when selected, correct?

First you will need to remove column 2 index from the table.columns([1, 2, 7]).every(function() { statement in the buildSelect() function.

Next you will need to create an addition buildSelect like function that first creates the select element then gets the unique data for columns 2,3 and 4 to the slect values values. The change event handler will need to search columns 2,3 and 4 with columns().search(). This uses the flatten() API to convert the columns().data() 2D structure to a 1D array. Note the use of columns().data() is changed twice in the below code.

function buildAreaSelect(table) {

    //. Create select element
    var select = $('<select aria-label="select"><option value=""></option></select>')
      .appendTo($('#dropdown' + counter).empty())
      .on('change', function() {
        var val = $.fn.dataTable.util.escapeRegex(
          $(this).val()
        );

        // Search Area columns for selected Area
        table.columns(2, 3, 4)
          .search(val ? '^' + val + '$' : '', true, false)
          .draw();
      });

    // Build single list of options from the Area columns
    table.columns([2, 3, 4], { search: 'applied' })..data().flatten().unique().sort().each(function(d, j) {
      select.append('<option value="' + d + '">' + d.substr(0,25) + '</option>');
    });
    
    // The rebuild will clear the exisiting select, so it needs to be repopulated
    var currSearch = column.search();
    if (currSearch) {
      // Use RegEx to find the selected value from the unique values of the column.
      // This will use the Regular Expression returned from column.search to find the first matching item in column.data().unique
      select.val(table.columns([2, 3, 4], { search: 'applied' })..data().flatten()..unique().toArray().find((e) => e.match(new RegExp(currSearch))));
    }
  });
}

In addition to calling buildSelect() you will also need to call buildAreaSelect().

I didn't test this code so you may need to do some debugging.

Kevin

[Deleted User]

Yes, that is correct. Thanks so much!

[Deleted User]

kthorngren

Hello again, Kevin. Thanks so much for your assistance. Here is my attempt. Is something wrong with my syntax? I see the error Unexpected token ')' on line 152.4 : REMOVED. Also, as you will see, the dropdowns and pagination disappeared. Thank you for helping out a newbie.

$(document).ready(function() {
  var table = $('#bcc-directory').DataTable({
    responsive: true,
    searching: true
  });

  buildSelect(table);
 
  table.on('draw', function() {
    buildSelect(table);
  });
  $('#clear-table').on('click', function() {
    table.search('').columns().search('').draw();
  });
});

function buildSelect(table) {
  var counter = 0;
  table.columns([1, 7]).every(function() {
    var column = table.column(this, {
      search: 'applied'
    });
    counter++;
    var select = $('<select aria-label="select"><option value=""></option></select>')
      .appendTo($('#dropdown' + counter).empty())
      .on('change', function() {
        var val = $.fn.dataTable.util.escapeRegex(
          $(this).val()
        );

        column
          .search(val ? '^' + val + '$' : '', true, false)
          .draw();
      });

    column.data().unique().sort().each(function(d, j) {
      select.append('<option value="' + d + '">' + d.substr(0,25) + '</option>');
    });
    
    // The rebuild will clear the existing select, so it needs to be repopulated
    var currSearch = column.search();
    if (currSearch) {
      // Use RegEx to find the selected value from the unique values of the column.
      // This will use the Regular Expression returned from column.search to find the first matching item in column.data().unique
      select.val(column.data().unique().toArray().find((e) => e.match(new RegExp(currSearch))));
    }
  });
}
function buildAreaSelect(table) {
 
    // Create select element
    var select = $('<select aria-label="select"><option value=""></option></select>')
      .appendTo($('#dropdown' + counter).empty())
      .on('change', function() {
        var val = $.fn.dataTable.util.escapeRegex(
          $(this).val()
        );
 
        // Search Area columns for selected Area
        table.columns(2, 3, 4)
          .search(val ? '^' + val + '$' : '', true, false)
          .draw();
      });
 
    // Build single list of options from the Area columns
    table.columns([2, 3, 4], { search: 'applied' }).data().flatten().unique().sort().each(function(d, j) {
      select.append('<option value="' + d + '">' + d.substr(0,25) + '</option>');
    });
     
    // The rebuild will clear the exisiting select, so it needs to be repopulated
    var currSearch = column.search();
    if (currSearch) {
      // Use RegEx to find the selected value from the unique values of the column.
      // This will use the Regular Expression returned from column.search to find the first matching item in column.data().unique
      select.val(table.columns([2, 3, 4], { search: 'applied' }).data().flatten().unique().toArray().find((e) => e.match(new RegExp(currSearch))));      
     }
});
}

Edited by Kevin: Syntax highlighting. Details on how to highlight code using markdown can be found in this guide

kthorngren

I copied your code into this test case to have help with where the errors are: REMOVED

Click on the Errors element at the bottom of the Javascript tab. Click on the specific error and it will jump to that spot. Remove the ); from line 77 in you above code snippet to fix one error. Then remove the } on line 78 to fix the last error.

Kevin

[Deleted User]

Thank you, Kevin. I think I am getting closer: REMOVED. How do I get the new table to append to the "Area" dropdown? Right now "Area" acts like a link when you hover, but there is no dropdown.

kthorngren

I would change this:

<span id="dropdown2"></span>

To a div, for example:

<div id="dropdown2"></div>

In buildAreaSelect() change the selector used to append the select to something like this:

.appendTo($('#dropdown2').empty())

See if this works.

Kevin

[Deleted User]

Thank you, Kevin. I have updated the code but no luck yet. I removed unused scripts but that did not help either. I noticed that if I add "2" back into the first index, the select options appear for "Area 1." I guess that I am not appending correctly: REMOVED

kthorngren

It doesn't look like you are calling the buildAreaSelect() function. I would call it after you call the buildSelect() function.

Kevin

[Deleted User]

Thanks to your help, I believe I have made some progress. The "Area" dropdown is working but does not list, for example, all areas containing "allied health," which should include two instances.

allan

       table.columns(2, 3, 4, 5)
          .search(val ? '^' + val + '$' : '', true, false)
          .draw();

There are two issues here:

1. table.columns(2, 3, 4, 5) will only select column index 2. The 3,4,5 are ignored (see columns() for useage). You could use .columns([2,3,4,5]), but...
2. Even if that did work, it would apply the same filter to all columns - effectively an AND operation. Your are looking for an OR.

What I would suggest you do here is provide a function for the search action

table.search.fixed('area', function (str, row, idx) {
  if (! val) {
    return true;
  }

  if (row[2] === val || row[3] === val  || row[4] === val  || row[5] === val) {
    return true;
  }
  return false;
});

Or something like that!

Allan

[Deleted User]

Thanks so much, Allan. I added the code and got the dropdowns to appear but the "Area" one is not working. Sorry, I tried rearranging the code but nothing worked. Thanks again for helping out a newbie. REMOVED

allan

var val = $.fn.dataTable.util.escapeRegex(
          $(this).val()
);

change to:

var val = $(this).val();

Have you changed how the select was being constructed? It was populating the list of options before, bit it is empty now.

Allan

[Deleted User]

Hi, Allan. Yes, but I have reverted it back so options show in the area dropdown. Earlier I tried adding the first piece of code you gave me, but I was not sure where to place it.

allan

Use my earlier code to perform the search action, but use something like:

table.colums([2, 3, 4, 5]).data().unique().toArray()

To get the data to show in the dropdown. Lol inner that and add each entry in the array as a value. You may want to add .sort() to that function chain as well thinking about it.

Allan

[Deleted User]

Good morning. Thank you for your help. Is it possible to hire a Datatables developer to fix our directory, or do I need to buy a one-year subscription?

allan

I don't provide development as a service, but rather support (I recognise the line can be a bit blurred, particularly for small things like this).

Aside from the area filter, what else do you need done?

I've just had a look at the page you linked to before and it is still using a regex filter on the area. I think your // Build single list of options from the Area columns is absolutely right. You just need to integrate my suggestion from above with a fixed filter:

var areaFilter = "";

table.search.fixed("area", function (str, row, idx) {
  if (!areaFilter) {
    return true;
  }

  return row[2] === areaFilter ||
    row[3] === areaFilter ||
    row[4] === areaFilter ||
    row[5] === areaFilter
    ? true
    : false;
});

var select = $(
  '<select aria-label="select"><option value=""></option></select>',
)
  .appendTo($("#dropdown2").empty())
  .on("change", function () {
    areaFilter = $(this).val();
    table.draw();
  });

// Build single list of options from the Area columns
table
  .columns([2, 3, 4, 5], { search: "applied" })
  .data()
  .flatten()
  .unique()
  .sort()
  .each(function (d, j) {
    select.append('<option value="' + d + '">' + d.substr(0, 25) + "</option>");
  });

Allan

[Deleted User]

Thanks so much for all your help! I almost have it done. Are you able to delete this post and my account?

allan

Yes of course. Would you like me to do so? Others might benefit from it if you are willing to let the post remain? (I can remove the URLs). No worries if you want it fully deleted though.

Allan

[Deleted User]

Yes, that will be great! Thank you!

allan

All done