Results 1 to 2 of 2

Thread: Dynamically Filter Database - UI help and Logistics help

  1. #1

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    South Louisiana

    Dynamically Filter Database - UI help and Logistics help

    I'm going to attach a screenshot of what it is that I want to do:
    Name:  Capture.jpg
Views: 252
Size:  15.5 KB
    The data returned right now is arbitrary data for illustration purposes only.

    As you can tell I want to dynamically filter a database to allow users to add as little or as many filters as they want. Whenever they click on a link on under the Filters section, the idea is that it will display a modal where they can narrow down which filter they want to apply. So for example, if they clicked on Address then they can filter it where it based on an exact/similar match on the numbers in the street address, the name of the street, and the street suffix. So theoretically a user could match:
    • Where the numbers in the address is like "1"
    • Where the numbers in the address is exactly "45"
    • Where the street suffix is like "St"

    At it would return examples like the following:
    1599 Shell Street
    45 Honorable Dday9 Street
    113 Parkway St.
    This is where my first problem comes: I have 21 filter options and each option there could be up to n amount of sub-options. Will I need to create the markup for every modal? This seems like a lot of code that could go unused. For example, if somebody just wanted to filter the database where the Birthday field has the same month and day as today, then they would only use one modal.

    The other issue that I have is dynamically building the query. If you cannot follow what's going on, the filters are added from top to bottom. So in the image, the zip code proximity filter came first and it returned 30 households. Then came the age audit which says that I want to return all households that are 50 miles from 70663 and they have someone in the household who is in between the ages of 18 and 30 which ultimately returns 3 households. I suppose this would work easily if I didn't allow the user to edit or remove any of the existing filters, but... I'd like to give the user that option. So how would I update, not only the existing query that they want to change, but also any subsequent ones that follow?

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004

    Re: Dynamically Filter Database - UI help and Logistics help

    I use a SlickGrid to contain my rows - and it is not unusual for some of my clients to call up grids with 10,000 or 20,000 rows.

    Point of the above statement is to RETURN all the rows to the UI running in the browser. Then you filter those rows without a visit to the DB.

    I have coded a generic filter that runs against the grid. Self analyzes the data in each column - just like excel. SlickGrid has the ability for you to specify the type of data in a column - I exploit that to make smart choices about how to show the filter for each column you might select.
    Attached Images Attached Images  
    Last edited by szlamany; Sep 23rd, 2018 at 02:09 PM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts


Click Here to Expand Forum to Full Width