Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts
    When creating a small database with 15 fields in the clients table and I setup a report menu with 15 list boxes enabling various filter selections from any of the boxes.

    However, on a larger database with about 75 fields, I would like to create a large filter still using list boxes in any conceivable combination but of course, screens are generally not big enough. Therefore I started thinking of creating sections or groups of list boxes and having some mechanism to expand/collapse each section as required.

    But then I wondered if loungers have had to do a similar thing and if anyone was willing to share their design inspirations please?

    The brief is that I definitely need to filter in any combination on all fields. Selecting entries from each box creates a filter string to be used in a SQL statement. The programming of the SQL statement is no problem whatsoever, it just working out a clever and easy way to filter using 75 list boxes.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I've had users claim that they absolutely needed to be able to filter on all fields at once, but when I analyzed their usage patterns, it turned out that they seldom filtered on more than 3 fields at once, and never at all on more than 6 fields. So I provided 6 combo boxes and 6 list boxes. Each of the combo boxes listed the fields; when the user selected a field, the corresponding list box was populated with the values of that field. I never got a complaint that they needed to do more...

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Basically, I use a technique that allows the user to select a field from a listbox, and then the appropriate objects are displayed for the user to enter the selection criteria. So for a date field, the user might be shown 2 text boxes (for enterring a date range). If a listbox is needed, it is dynamically filled. User selections are saved, and then used to create the appropriate WHERE clause for the report when the report is run.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I have some philosophical differences with allowing users to construct reports based on them setting a bunch of filters. In my experience they are a great way to burn a lot of trees, and the data is usually temporal data that you don't really care about next year, or even next week. I prefer to used drill-down tools such as treeviews or pivot-table approaches to viewing data - it is amazing what can be gleaned from using those kind of tools that you never get from paper reports. Then I devise a set of reports that meet any statistical needs or summary reports to satisfy upper management. Different strokes for different folks and all that.
    Wendell

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Regardless of the output mode, I would think you often would want to filter the data. That's all we are talking about doing. I don't think it matters if you are filtering data for a report, a form, a pivot table, or to export to Excel. You want to provide a means for relatively unsophisticated users to restrict the output.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have developed a generic reporting facility which is essentially table-driven. For the sort of requirement you have, I store the names of the database columns (fields) which can be filtered against and the SQL code needed, as in:
    Code:
         SelectColumns                               SelectSql
         ----------------------                      --------------
         ColNameA/ColNameB/ColNameF                  Select x, y from a order by y/Select x, y from b order by y/Select x, y from c order by y
    Then I have a generic subform containing 2 listboxes (AvailableForSelection and Selected), which is displayed on a generic form where each database column which can be used as a criterion is tickable. When ticked the subform is displayed showing, in the lh listbox, all possible minus selected values and, in the rh listbox, all selected values (since I allow "queries" to be saved, an existing "query" may have some values already selected).

    So, in your case, the main form would show, say, 75 column names or descriptors (which should easily fit in multi-column format on a single page without scrolling), and the 2 listboxes would show values for the currently selected column name or descriptor.

Posting Permissions

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