How can you pass the results of many cascading dropdowns to "filter" another list? RSS

• • Reply
  

  Liquidmetal

  Member

  24 Points

  176 Posts

  Re: How can you pass the results of many cascading dropdowns to "filter" another list?

  Feb 27, 2013 08:55 AM|LINK

  It is early days, but this seems to be on the money. Will keep testing and report back. There are no spurious results and any selection that do not match returns no values. Which is good.

  However, can I be cheeky and ask if you have any ideas on how I can limit the last dropdown by 2 values (BrandID and StyleID) rather than the 1 value it is receiving at the moment. Currently the last drop-down populates with all items that are a property of StyleID and this means that there are too many selections to choose and if you choose a value that does not also link with the BrandID, we get null result.  We need to populate the drop-down with the correct values we can remove another area of 'error'.

  Yet again my hats of to you and thank you very much.

• • Reply
  

  GmGregori

  Contributor

  5470 Points

  737 Posts

  Re: How can you pass the results of many cascading dropdowns to "filter" another list?

  Feb 27, 2013 01:39 PM|LINK

  Liquidmetal

  how I can limit the last dropdown by 2 values (BrandID and StyleID) rather than the 1 value

  I'm not so acquainted in javascript, so look with prudence at my suggestion.

  Try replacing

  $('#styleID').change(function () {
              var styleID = $(this).val();
              $.getJSON('/GetDesigns/'+ styleID, function (getDesigns) {

  with

          $('#styleID').change(function () { 
              var styleID = $(this).val();
              var brandID = $('#brandID').val();
              $.getJSON('/GetDesigns/'+ styleID + '/' + brandID, function (getDesigns) { 

  and modify your GetDesigns.cshtml file adding a row like the following

  var brandId = UrlData[1].IsEmpty() ? 1 : UrlData[1].AsInt();

  to read the second UrlData and adding a second condition to your sql query

      var sql = "SELECT DesignId, Designs FROM your_table WHERE BrandID = @0 AND StyleID = @1 ORDER BY Designs";
      var product = db.Query(sql, brandId, styleId);

  If it isn't obvious how to modify the GetDesigns.cshtml, post the code of your file.
  
  
  

  • Edited by GmGregori on Feb 27, 2013 01:39 PM
  • Marked as answer by Liquidmetal on Feb 28, 2013 12:21 PM
• • Reply
  

  Liquidmetal

  Member

  24 Points

  176 Posts

  Re: How can you pass the results of many cascading dropdowns to "filter" another list?

  Feb 27, 2013 02:08 PM|LINK

  Hi, thanks for looking into this for me. It works!! I know it is early days but after clearing the cache and putting the proper tables etc. It is popluated with the proper number of drop down items!!! wow, hats off and thank you. I have posted my code for other people like me so they can see how we got this working. However, if anything goes wrong I will be back seeking further help. :-)

   

  @{ 
      Layout = "~/_SiteLayout.cshtml"; 
      Page.Title = "Home Page"; 
    
      var db = Database.Open("A-LensCatFE-01SQL") ; 
      var data=db.Query("Select CatID,CatName FROM dbo.qryFilteredCatalogues ORDER BY CatID"); 
      var categories=data.Select(item=>new SelectListItem { 
          Value=item.CatID.ToString(), 
          Text=item.CatName 
      }); 
   
      if (IsPost){ 
          var catid = Request["CatId"]; 
          var brandid = Request["brandID"]; 
          var styleid = Request["styleID"]; 
          var designid = Request["designID"]; 
   
          Response.Redirect("~/ResultLens.cshtml?catid=" + catid + "&brandid=" + brandid + 
                      "&styleid=" + styleid + "&designid=" + designid); 
      } 
  } 
   
  <form method="post"> 
      <p> 
          @Html.DropDownList("CatID", "-- Select Catalogue --", categories) 
      </p> 
      <p> 
          @Html.DropDownList("brandID", "-- Select Brand --", Enumerable.Empty<SelectListItem>()) 
      </p> 
      <p> 
          @Html.DropDownList("styleID", "-- Select Style --", Enumerable.Empty<SelectListItem>()) 
      </p> 
      <p> 
          @Html.DropDownList("designID", "-- Select Design --", Enumerable.Empty<SelectListItem>()) 
      </p> 
      <p> 
          <input type="submit" name="submit" /> 
      </p> 
  </form> 
    
  @section scripts { 
  <script src="~/Scripts/jquery-1.8.1.min.js" type="text/javascript"></script> 
  <script> 
      $(function () { 
          var brandsSelect = $('#brandID'); 
          var stylesSelect = $('#styleID'); 
          var designsSelect = $('#designID'); 
   
          brandsSelect.attr('disabled', true); 
          stylesSelect.attr('disabled', true); 
          designsSelect.attr('disabled', true); 
   
          $('#CatID').change(function () { 
              var CatID = $(this).val(); 
              $.getJSON('/GetBrands/'+ CatID, function (getBrands) { 
                  brandsSelect.attr('disabled', false); 
                  brandsSelect.empty(); 
                  brandsSelect.append( 
                          $('<option/>') 
                              .attr('value', '') 
                              .text('-- Select Brand --')); 
                  $.each(getBrands, function (index, product) { 
                      brandsSelect.append( 
                          $('<option/>') 
                              .attr('value', product.BrandID) 
                              .text(product.BrandName) 
                      ); 
                  }); 
              }); 
          }); 
   
          $('#brandID').change(function () { 
              var brandID = $(this).val(); 
              $.getJSON('/GetStyles/'+ brandID, function (getStyles) { 
                  stylesSelect.attr('disabled', false); 
                  stylesSelect.empty(); 
                  stylesSelect.append( 
                          $('<option/>') 
                              .attr('value', '') 
                              .text('-- Select Style --')); 
                  $.each(getStyles, function (index, product) { 
                      stylesSelect.append( 
                          $('<option/>') 
                              .attr('value', product.StyleID) 
                              .text(product.Styles) 
                      ); 
                  }); 
              }); 
          }); 
   
          $('#styleID').change(function () {  
              var styleID = $(this).val(); 
              var brandID = $('#brandID').val(); 
              $.getJSON('/GetDesigns/'+ styleID + '/' + brandID, function (getDesigns) { 
                  designsSelect.attr('disabled', false); 
                  designsSelect.empty(); 
                  designsSelect.append( 
                          $('<option/>') 
                              .attr('value', '') 
                              .text('-- Select Design --')); 
                  $.each(getDesigns, function (index, product) { 
                      designsSelect.append( 
                          $('<option/>') 
                              .attr('value', product.DesignID) 
                              .text(product.Designs) 
                      ); 
                  }); 
              }); 
          }); 
   
      }); 
  </script> 
       
  } 
  

  getDesigns:

  @{
      Layout = null;
      var brandid = UrlData[1].IsEmpty() ? 1 : UrlData[1].AsInt();
      var styleid = UrlData[0].IsEmpty() ? 1 : UrlData[0].AsInt(); 
      var db = Database.Open("A-LensCatFE-01SQL") ;
      var sql = "SELECT DesignID, Designs FROM dbo.qryFilteredDesigns WHERE BrandID = @0 AND StyleID = @1 ORDER BY DesignID"; 
      var getDesigns = db.Query(sql, brandid, styleid);
      Json.Write(getDesigns, Response.Output);
  }

  I am tentatively going to mark this as resolved. Thank you. 

  • Edited by Liquidmetal on Feb 27, 2013 02:13 PM