Dynamically populate dropdown list with default value

ctran2

Hi @allan,

We currently have a large dataset which is filtered by a user's input from a drop down selection on the front end.  To prevent all the data from loading, on first load, the controller is leveraging the default selection that has been manually set in the .CSHTML file.  

The question is, rather than manually typing the contents of the dropdown list, how would we go about dynamically generating the values of the dropdown list based on the data in the table.  And along with this, how do we have the select list automatically default the value in this list to the max value of the list so the initial load of the table is filtered to the max value of this field rather than displaying all of the data?

I have found some sources from datatables about creating column dropdown filter, but that doesn't seem to apply for our case since it would force us to load all the data to generate that dropdown list, which is exactly what we're trying to avoid.

Controller:

public class TestController : ControllerBase
   [HttpGet]
   [HttpPost]
   public ActionResult Test()
   {
           // Minimized code for simplicity

            using (var db = new Database("sqlserver", cnStr))
            {
                var response = new Editor(db, "Test", "id")
                    .Model<TestModel>("Test")
                    .Where(q =>
                    {
                        q.Where(r =>
                        {
                            r.Where("Year", Request.Form["year"], "=");  // filter user's input from dropdown built on front end
                        }
                    });

                return new JsonResult(response);
            }
        }

JS:

var table = $('#Test').DataTable({
       ajax: {
            'url': '/api/Test',
        "type": 'POST',
        "data": function (d) {
        d.year = $('#year').val();
    }

// Minimized code for simplicity
},

// Reload table based on dropdown selection
$('#year').on('change', function () {
    table.ajax.reload();
});

CSHTML (option values are now manually entered and defaulted to max year. We want to dynamically populate it):

<select class="form-select form-select-sm" id="year">
            <option value=2022 selected>2022</option>
            <option value=2021>2021</option>
</select>

Any thoughts on how to tackle this would be highly appreciated.
Thank you

rf1234

You don't seem to use Editor. If you used it the solution would be rather simple.

On the occurence of an appropriate even you would simply make an ajax call, load the options from the server and update the dropdown with those options using the "update" method. You'll find many examples in this forum.

Here is one of them using the "dependent" event handler:

https://datatables.net/forums/discussion/comment/212601/

ctran2

Thanks for your response @rf1234 .

I'm currently using Editor. Just to make sure that my explanation was clear. Our table has multiple fields, including Year. I want to have a dropdown list that is dynamically populated from that field Year and defaulted to max value (2022).

So far we've hardcoded the option values for Year in html. Once a user selects an option from the dropdown, that single input would be sent to the Request.form[Year] inside the controller where clause, which then only loads the records of that single selected year from the database.

The reason why we've followed this approach is because the table may have up to a million records. We don't want to load the entire table with all the records and fields just to populate a dropdown list since that would take a colossal amount of time.

I'm curious if there's a solution to dynamically populating the dropdown list and defaulting it to max value in the first load without having to pass the entire table is what I meant. Should I create a custom query to retrieve just the list of year and pass the result to the controller at some point?

Please advise!

rf1234

Here is what I would do (probably - if I understood everythig correctly...):
- Initially I would post an empty year to the server.
- On "preGet" I would read the max. year from the database table using proprietary SQL and use a session variable or another global variable to make it usable in the Editor where clause
- I would use a getFormatter for the year field and return the max year from above
- On "open" of Editor I would make a proprietary ajax call as in my response above, read the options with proprietary SQL and populate the options.
- and so on and so forth

Feel free to come back to me if you have further questions.

ctran2

Thanks again for the prompt response, @rf1234,

I'm trying to follow what you said. Can you clarify where and how you would post an empty year to the server?

I'm fairly new to Editor events, yet I didn't find code examples from Editor documentation. If you can possibly provide some source code on the preGet and how to return max year using getFormatter that would be much appreciated, too! I imagine it would be something like this within Editor class in the controller:

using (var db = new Database("sqlserver", cnStr))
{
       var response = new Editor(db, "Test", "id")
           .Model<TestModel>("Test")
           .Field(new Field("Year").GetFormatter(Format.NullEmpty()) // Post an empty year to server. Not sure at which point to pass max value

           .Where(q =>
            {
                 q.Where(r =>
                 {
                      r.Where("Year", Request.Form["year"], "=");  // filter user's input from dropdown built on front end
                 }
             });
       
       response.preGet += // Not sure what to put here

       return new JsonResult(response);
}

allan

I've got a question that I'd like to clarify before I muddy the waters inadvertently. Are you saying you have a select at the top of the page (not in an Editor view) which has a list of years which is populated from the database. When the user selects a value, it will then display a DataTable for that year and that data can be edited via Editor. New inputs should default to the year selected.

Is that correct or am I barking up the wrong tree?

If you are able to show us a screenshot showing the issue, that would be really useful.

Allan

iqvct

Thanks @allan, I can answer this question on @ctran2's behalf as we have both worked on this issue in tandem.

Currently at the top of the front end view there is a standard hard coded HTML select as such:

<select class="form-select form-select-sm" id="year">
            <option value=2022 selected>2022</option>
            <option value=2021>2021</option>
</select>

This is currently not populated by the database, but rather hard carded as shown.

That is issue one of the two issues at hand. We need to find a way to populate that dropdown directly from the database, potentially through a custom db query that returns a list of years such as:

SELECT DISTINCT Year FROM Table

Secondly, the controller uses a where clause which is pulling data from this drop down to filter the query of data.

r.Where("Year", Request.Form["year"], "=");

The value of Year in this case needs to initially be defined as:

SELECT MAX (Year) FROM Table

This is critical because we don't want all of the data sent from the server and then filtered by the front end. We need the query itself modified using this where clause so the front end is not burdened by excessive amounts of data.

We currently are able to achieve this by modifying the option value within the select as selected. Then the table is reloaded using below:

$('#year').on('change', function () {table.ajax.reload();

Let me know if this has helped clarify the issues we're looking to solve. Thank you.

allan

potentially through a custom db query that returns a list of years

Yup - I'd do exactly that. DataTables / Editor won't help with that. You could use the server-side libraries to query the db (e.g. use the Sql() method).

We currently are able to achieve this by modifying the option value [...]

I'd use ajax.data like this:

ajax: {
  url: '...',
  type: 'post',
  data: function (d) {
    d.year = $('#year').val();
  }
}

Then have an event handler to trigger a reload:

$('#year').on('change', function () {
  table.ajax.reload();
});

Regards,
Allan

iqvct

Thanks @allan

This all looks aligned with our intention, but the implementation is still evading us.

I'd use ajax.data like this:

ajax: {
  url: '...',
  type: 'post',
  data: function (d) {
    d.year = $('#year').val();
  }
}

This is exactly how we're handling things now. But the question is how do we default year to equal the value of the max year on initial database query in the controller's where clause, but also allow the user to select a year after the initial load? We're currently using d.year = $('#year').val(); but this is only going to give us what is selected in the dropdown. We need to make sure that the dropdown's initial value is set to max(year).

The other component is populating the dropdown. We've been able to generate a list through a custom query, but we're still trying to determine the best way to pass the values of this list to the dropdown which I suppose is outside the scope of being a Datatables question.

To best explain the desired output, if the database table contains data for years 2018, 2019, 2020, 2021 and 2022, the where clause in the controller should say WHERE Year = '2022' on initial load. The server would then send to the front end only data where year is 2022. The user would would initially only see 2022 data but there would be a dropdown including 2018, 2019, 2020, 2021 and 2022. If the user were to select 2019 this would be passed to the controller and now the controller would read WHERE Year = '2019' and the page would reload with 2019 data.

Again, we have all this working now, but it's only because we have manually typed in all of the years, and we have manually selected what the default year should be.

kthorngren

But the question is how do we default year to equal the value of the max year on initial database query in the controller's where clause, but also allow the user to select a year after the initial load

I would use a jQuery ajax() request, before initializing Datatbles, to fetch the available years from the DB. In the success function build the select options, default it to the max year, then initialize Datatables which will grab the default selected value in the ajax.data option to initially load the data.

Kevin

allan

Another option, and possibly better (one less Ajax call) would be that when you are rendering the page initially, use the selected parameter on the option tag you want to be default selected. It depends a little bit about how you get that data and render the list. It's .NET you are using - what are you using to draw the initial page? i.e. can you use database specific information in the HTML (dynamically rendering the HTML)?

Allan

ctran2

Thank you very much for all your responses. I have built a global function that executes a custom SQL query to return the list of year. Then I called that function in the controller and added it to data.meta. I've been able to dynamically show the options for the dropdown. However, I've been having this issue that the dropdown kept duplicating itself whenever I clicked to select an option.

Here's my code for the controller:

public class TestController : ControllerBase
   [HttpGet]
   [HttpPost]
   public ActionResult Test()
   {
           // Minimized code for simplicity
           // Get list of year 
           IEnumerable<string> years = SQLQueries.TestDropdown.GetYears(cnStr);

 
            using (var db = new Database("sqlserver", cnStr))
            {
                var editor = new Editor(db, "Test", "id")
                    .Model<TestModel>("Test")
                    .Where(q =>
                    {
                        q.Where(r =>
                        {
                            r.Where("Year", Request.Form["year"], "=");  // filter user's input from dropdown built on front end
                        }
                    });
 
               var response = editor.Process(Request).Data();

                response.meta.Add("years", access);

                return new JsonResult(response);
            }
        }

JS:

var table = $('#Test').DataTable({
       ajax: {
            "url": '/api/Test',
        "type": 'POST',
        "data": function (d) {
        d.year = $('#year').val();
    }
 
// Minimized code for simplicity
},

var editor = new $.fn.dataTable.Editor({
       ajax: {
            "url": '/api/Test',
        "type": 'POST',
        "data": function (d) {
        d.year = $('#year').val();
    }
 
// Minimized code for simplicity
},
 

// Reload table based on dropdown selection
$('#year').on('change', function () {
    table.ajax.reload();
});


// Access year list in meta.data using xhr.dt
$('#Test').on('xhr.dt', function (e, s, data) {  
                var values = data.meta.year;
                console.log(values);

                var select = document.createElement("select");
                select.name = "year";
                select.id = "year"
                select.className = "form-select form-select-sm"

                for (const val of values) {
                    var option = document.createElement("option");
                    option.value = val;
                    option.text = val.charAt(0).toUpperCase() + val.slice(1);
                    select.appendChild(option);
                }
                document.getElementById("year").appendChild(select);
                
            });

CSS:

<h3>Year: </h3>
<div id="year"></div>

Any idea of why this happened?

kthorngren

Looks like you might need to clear the year element before appending the updated select. See if replaceChildren is the method you need.

Kevin

ctran2

Hi @kthorngren, I tried replaceChildren() then bumped into another issue. No matter what I selected the dropdown would return to the first option. Here I selected 2021 - Q4, and the dropdown showed 2022 - Q3.

Here's the JS code:

// Access year list in meta.data using xhr.dt
$('#Test').on('xhr.dt', function (e, s, data) { 
                var values = data.meta.year;
                console.log(values);
 
                var select = document.createElement("select");
                select.name = "year";
                select.id = "year"
                select.className = "form-select form-select-sm"
 
                for (const val of values) {
                    var option = document.createElement("option");
                    option.value = val;
                    option.text = val.charAt(0).toUpperCase() + val.slice(1);
                    select.appendChild(option);
                }
           
              // Remove existing select and append new select
               var dropdown =  document.getElementById("year");
               dropdown.replaceChildren(); 
               dropdown.appendChild(select);
                 
            });

I also tried
dropdown.innerHTML = '';
and

while (dropdown.firstChild) {
    dropdown.firstChild.remove();
}

but still encountered the same behavior. The dropdown cleared out the new select and returned the first option of the list.

kthorngren

Are you saying that you want the updated options list have the current search term re-selected?

This SO thread shows some options for programmatically selecting options.

Kevin

ctran2

Okay, I'll look into that, but I just realized a bigger issue. I set the breakpoint at the where clause for Request.Form["year"] in the controller and saw that null was returned every time I selected the dropdown.

kthorngren

I'm not familiar with ASP.NET ( I think that is what you are using ) but doing a bit of searching it looks like you might want to use Request.Params instead of Request.Form. See these two SO threads:

https://stackoverflow.com/questions/3584959/what-request-paramskey-do
https://stackoverflow.com/questions/5706/when-do-request-params-and-request-form-differ

Stack Overflow is an excellent resource for general programming questions.

Kevin