Button “Select” to filter datable to another datat

Question

Days ago, I started to create another project in MVC about filtering data tables.

I decided to create datatable that filters another datatable using a "Select" button.

The first thing that I did was to create a class that can be used to display two data tables in single view.

This is my class:

   public class MyData
   {
    public IEnumerable<pmTA_ProjectCategory> ProjectCategory { get; set; }
    public IEnumerable<pmTA_FundCategory> FundCategory { get; set; }
   }

Then I created two data table in a single view.

This is my code:

  @using iBUDGET.Models;
  @model MyData

  <table id="myDataTable" class="table table-bordered table-hover ">
    <thead>
        <tr>
            <th>id</th>
            <th>code</th>
            <th>
                title
            </th>
            <th>
                description
            </th>
            <th>--</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model.FundCategory)

        {
            string selectedRow = "";
            if (item.id == ViewBag.fund)
            {
                selectedRow = "success";
            }
            <tr class="@selectedRow">
                <td>
                    @Html.DisplayFor(modelItem => item.id)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.code)
                </td>
                <td>

                    @Html.DisplayFor(modelItem => item.title)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.description)
                </td>
                <td>
                    @Html.ActionLink("Select", "Index", new { fund_category_id = item.id }, null)
                </td>
            </tr>
        }
    </tbody>
</table>


 @if (Model.ProjectCategory != null) {
 <table class="table table-bordered table-hover ">
<thead>
    <tr>
        <th>id</th>
        <th>title </th>
        <th>
            description
        </th>

        <th>Edit</th>

    </tr>
  </thead>
<tbody>

    @foreach (var item in Model.ProjectCategory)
    {

        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.id)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.title)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.description)
            </td>

            <td>
                @Html.ActionLink("Edit", "ProjectCategoryEdit", new { id = item.id }, new { @class = "btn btn-primary" })
            </td>

        </tr>
    }
</tbody>
  </table>
     }


      @section Scripts{
       <script>
                $(document).ready(function () {
                    $("#myDataTable").DataTable({
                        searching: false,
                        dom: 'Bfrtip'      
                    });
                });
          </script>

The script above is for the paging of a data table if there are lots of data in data table only.

In order to fully function, the two data tables in single view I created codes in controller to display the data of two data table.

This is my code in my Index controller:

      pppmsTAYABAS_dbEntities db = new pppmsTAYABAS_dbEntities();
        public ActionResult Index(int? fund_category_id)
        {

            var viewModel = new InstructorIndexData();
        viewModel.FundCategory = (from p in db.pmTA_FundCategory
                                  select new
                                  {
                                      id = p.id,
                                      code = p.code,
                                      title = p.title,
                                      description = p.description,
                                      status = p.status
                                  }).ToList()
                  .Select(x => new pmTA_FundCategory()
                  {
                      id = x.id,
                      code = x.code,
                      title = x.title,
                      description = x.description,
                      status = x.status
                  });

       if (fund_category_id != null)
        { 
          ViewBag.fund = fund_category_id.Value;
            viewModel.ProjectCategory = (from p in db.pmTA_ProjectCategory
                          join g in db.pmTA_FundCategory
                           on p.fund_category_id equals g.id
                          where p.fund_category_id == fund_category_id
                          select new
                          {
                              id = p.id,
                              title = p.title,
                              description = p.description,
                              title1 = g.title,
                              status = p.status
                          }).ToList()
               .Select(x => new pmTA_ProjectCategory()
               {
                   id = x.id,
                   title = x.title,
                   description = x.description,
                   title1 = x.title1,
                   status = x.status

               });

        }d
        return View(viewModel);

      }

When I run it, the button "Select" functions. The data table shows another data table corresponds to the data selected in data table based on the ID of data table.

This is the result when you run the program:

This is the result when you click the button "Select", then show the another data table corresponds to the ID of the first data table:

But the problem is:

When I added drop downs as my tool to filter the data of my first data table.

This is now my code for View when I Added the drop downs as tool for filtering:

  @using iBUDGET.Models;
  @model MyData
  <div>
   @using (Html.BeginForm("Index", "Test", FormMethod.Get))
        { 
   @Html.DropDownList("title", new SelectList(ViewBag.funds) , "Select...", new 
  { @class = "form-control" })
  @Html.DropDownList("code", new SelectList(ViewBag.codes), "Select...", new 
  { @class = "form-control" })
   <input type = "submit" value= "Search" />
  }
  </div>
  <table id="myDataTable" class="table table-bordered table-hover ">
    <thead>
        <tr>
            <th>id</th>
            <th>code</th>
            <th>
                title
            </th>
            <th>
                description
            </th>
            <th>--</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model.FundCategory)

        {
            string selectedRow = "";
            if (item.id == ViewBag.fund)
            {
                selectedRow = "success";
            }
            <tr class="@selectedRow">
                <td>
                    @Html.DisplayFor(modelItem => item.id)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.code)
                </td>
                <td>

                    @Html.DisplayFor(modelItem => item.title)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.description)
                </td>
                <td>
                    @Html.ActionLink("Select", "Index", new { fund_category_id = item.id }, null)
                </td>
            </tr>
        }
    </tbody>
</table>


 @if (Model.ProjectCategory != null) {
 <table class="table table-bordered table-hover ">
<thead>
    <tr>
        <th>id</th>
        <th>title </th>
        <th>
            description
        </th>

        <th>Edit</th>

    </tr>
  </thead>
<tbody>

    @foreach (var item in Model.ProjectCategory)
    {

        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.id)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.title)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.description)
            </td>

            <td>
                @Html.ActionLink("Edit", "ProjectCategoryEdit", new { id = item.id }, new { @class = "btn btn-primary" })
            </td>

        </tr>
    }
</tbody>
  </table>
     }


      @section Scripts{
       <script>
                $(document).ready(function () {
                    $("#myDataTable").DataTable({
                        searching: false,
                        dom: 'Bfrtip'      
                    });
                });
          </script>

and this is now the codes for my controller

         pppmsTAYABAS_dbEntities db = new pppmsTAYABAS_dbEntities();
        [HttpGet]
        public ActionResult Index(int? fund_category_id, string title, 
        string code)
        {
            ViewBag.funds = (from r in db.pmTA_FundCategory
                       select r.title).Distinct();
            ViewBag.codes = (from r in db.pmTA_FundCategory
                       select r.code).Distinct();

            var viewModel = new InstructorIndexData();
        viewModel.FundCategory = (from p in db.pmTA_FundCategory
                                  where p.title == title || title == null || title = ""
                                  where p.code == code || code || code == null || code = ""
                                  select new
                                  {
                                      id = p.id,
                                      code = p.code,
                                      title = p.title,
                                      description = p.description,
                                      status = p.status
                                  }).ToList()
                  .Select(x => new pmTA_FundCategory()
                  {
                      id = x.id,
                      code = x.code,
                      title = x.title,
                      description = x.description,
                      status = x.status
                  });

       if (fund_category_id != null)
        { 
          ViewBag.fund = fund_category_id.Value;
            viewModel.ProjectCategory = (from p in db.pmTA_ProjectCategory
                          join g in db.pmTA_FundCategory
                           on p.fund_category_id equals g.id
                          where p.fund_category_id == fund_category_id
                          select new
                          {
                              id = p.id,
                              title = p.title,
                              description = p.description,
                              title1 = g.title,
                              status = p.status
                          }).ToList()
               .Select(x => new pmTA_ProjectCategory()
               {
                   id = x.id,
                   title = x.title,
                   description = x.description,
                   title1 = x.title1,
                   status = x.status

               });

        }
        return View(viewModel);

      }

This is the problem:

The first data table filters the data table, but when you click the "Select" Button, it can't highlight the data selected, and the data table of Model.FundCategory refreshes and shows again all the data of the first(Model.FundCategory) data table, but filters the second data table.

I will show you the problem using pictures.

This is when I run again my project with drop down:

This when I filter my first data table:

Then when I click the "Select" button in the data I filter just like the picture above.

This become the result:

The problem is:

1. It shows above that the data of my first data table refreshes to the same state which has all the data again though it must be filtered though I click the "Select" button, it must only show my data filtered and not all data.

2. When the data filtered the highlighted portion must be retain but not all the data in data table.

It must be like this (I just edited this picture):

Hope that you will help in my problem.

Answer 1

When you run your project first time, your Index action method will construct query to get all data from FundCategory and ProjectCategory and bind it to razor view that you shown in above pictures.

Now if you select any option from both of drop downs then it will filter your FundCategory and show respective record regarding option selected in both of drop downs.

Now if you click on Select button in filtered record then it will hit again to same Index action method but this time fund_category_id has been supplied to it but title and code parameters become null because your did not supplied to your Index action method from select button so that in action method your FundCategory returns all of data.

So for you have to also pass these parameter from select button like

<td>
     @Html.ActionLink("Select", "Index", new { fund_category_id = item.id, title = titleSelectedFomDropDown, code = codeSelectedFromDropDown }, null)
</td>

But i think this is hard to do this, So you may follow below steps to achieve your goal.

You have to separate both of operation in two different action method, one for load all data for first time and second for getting filtered data from select button click.

1) Write your Index action method that carry your drop down data and FunCategory data like

You drop down will remain in your main view and create partial view for both of tables so that if you select option from down then only partial view will get refreshed and your main view's selected drop down values will remain as it is, otherwise your drop down will refreshed every time when you click on select button.

public ActionResult Index()
{
    ViewBag.funds = (from r in db.pmTA_FundCategory
                   select r.title).Distinct();
    ViewBag.codes = (from r in db.pmTA_FundCategory
                   select r.code).Distinct();

    var viewModel = new InstructorIndexData();
    viewModel.FundCategory = (from p in db.pmTA_FundCategory
                              where p.title == title || title == null || title = ""
                              where p.code == code || code || code == null || code = ""
                              select new
                              {
                                  id = p.id,
                                  code = p.code,
                                  title = p.title,
                                  description = p.description,
                                  status = p.status
                              }).ToList()
              .Select(x => new pmTA_FundCategory()
              {
                  id = x.id,
                  code = x.code,
                  title = x.title,
                  description = x.description,
                  status = x.status
              });
} 

2) Now if you click on select button for any record then you have to pass fund_category_id so for modify your razor like below

<td>
     @Html.ActionLink("Select", "Index", "Default", new { fund_category_id = item.id }, new { @class = "myLink" })
</td>

Then add script to get drop down values and fund category id like

$(document).on('click', '.myLink', function (e) {
    e.preventDefault();

    var fund_category_id = $(this).attr('href').split('?')[1].split('=')[1];

    $.ajax({
         url: "@Url.Action("GetFilteredData", "Default")",
        data: { fund_category_id: fund_category_id },
        type: "Get",
        dataType: "html",
         success: function (data) {
             $("#myPartialView").html( data );
        }
    });

});

3) And the above ajax call will hit below action method that can only filter ProjectCateogry wrt fund_category_id

[HttpGet]
        public ActionResult GetFilteredData(int? fund_category_id)
        {
            var viewModel = new InstructorIndexData();
            if (fund_category_id != null)
            {
                ViewBag.fund = fund_category_id.Value;
                viewModel.ProjectCategory = (from p in db.pmTA_ProjectCategory
                                             join g in db.pmTA_FundCategory
                                              on p.fund_category_id equals g.id
                                             where p.fund_category_id == fund_category_id
                                             select new
                                             {
                                                 id = p.id,
                                                 title = p.title,
                                                 description = p.description,
                                                 title1 = g.title,
                                                 status = p.status
                                             }).ToList()
                   .Select(x => new pmTA_ProjectCategory()
                   {
                       id = x.id,
                       title = x.title,
                       description = x.description,
                       title1 = x.title1,
                       status = x.status

                   });

            }
            return View(viewModel);

        }