10

I am building a filtering system for a report and am trying to find a way to incorporate and/or logic when both are to be mixed.

In essence, a very simple filter might be:

Select field 1 from table where field2="123" OR field3="123"

In that scenario, I am using something like the below and it is fine:

enter image description here

However, occasionally users will want to mix and and or in the same query which then becomes more complex from a UI perspective. For example,

Select field 1 from table where (field2="123" OR field3="123") AND field 1="abc"

Simply using the original UI doesn't work as it does not allow the user to specify the bits which are in brackets so I need to incorporate allowing them to do that - without doing this then the query reads very differently!

My example is pretty simple but of course queries can be a lot more complex than this so you might have three groups of OR and then two ands for example in which you need to know which OR as well as maybe the ANDs to bracket and so on

What would be the best way to allow the user to do this?

  • Will there always only be 3 fields to filter on or is that just an example? Can a user make a filter where field1 = A or field1 = B or field1 = C etc.? – DaveAlger May 13 '15 at 1:22
  • It is just an example DaveAlger - there could be 2 or there could be 10. We can set a limit if we choose but, for the moment, there is no limit – bhttoan May 13 '15 at 9:22
  • 1
    The question can't be closed because of the bounty, but it's a duplicate of Intuitive Interface for Composing Boolean Logic, which has some good answers. – Vitaly Mijiritsky May 14 '15 at 5:34
  • This question comes up again and again (in addition to Vitaly's reference and its many explicit duplicates, you have this one, this one, and this one at least), and Apple products show up repeatedly as the example. I wonder why it's so difficult to find other relevant examples in the wild despite how (apparently) prevalent the business requirement is (could be that it tests very poorly)? – Kit Grose May 19 '15 at 5:32

11 Answers 11

18
+100

Nested blocks in a vertical layout

This pattern tested very well with our users. It uses common language to explain what you are looking for and allows any level of complex grouping where individual blocks can be moved around, changed from AND to OR, or deleted.

This level of clarity does take up quite a bit of space but not too much for most simple filters.

nested expression builder

  • 3
    I'll try and put together a little demo of this later so you can try it out. – DaveAlger May 12 '15 at 12:58
  • +1 for proper spatial organisation, and for avoiding reading errors between any/all with colours. This answer uses multiple sound design principles. I'm curious though, up to how many rules have you successfully pushed this UI? – Steve Dodier-Lazaro May 13 '15 at 13:03
  • I think this comes closest to what we will go with - it still is confusing though. The test for me is to try and "read" the query it generates and I still get confused! – bhttoan May 18 '15 at 14:47
  • That is just a super complex example. The majority of queries our users did were much simpler. – DaveAlger May 19 '15 at 1:20
  • Thanks for this solution @DaveAlger. I've spent the last 2 weeks in searching for a usable sound query builder, and your example is just brilliant. Thanks again! – Kristiyan Lukanov Oct 13 '16 at 16:18
7

Users

First you need to know who are the users and if this approach fits their needs and skills. For most business users and/or logic is hard to understand and should be avoided. Technicians or clerks in finance, accounting, ... are used to such a logic.


UI

Depending on the requirements several implementations are conceivable:

Simple filter: Implicit and/or definitions

Like Google. As it seems it's too simple for your requirements I only mention it for completeness.

Google filter options


Rule editor with reduced features: explicit but limited and/or definitions

This would include your mockup. Not all features are provided, but it is the best off-trade between your users' skills and what they want.


Full-blown rule editor: explicit and/or definitions

Here you can do everything you want, but it's complex. Expect usability problems and still try to fit your solution to your users, like DaveAlger's solution.

Two rivers wrote a great article on that http://tworivers.com/archives/697

Most important points:

  • Criteria, operation, value
  • Nested rules
  • Possibility to add rules everywhere
  • Good defaults

OS Lion file filter

One more thing from another ux.se post:

  • Use background color changes for intended blocks

nested rule builder with background changes


Text input: explicit and/or definitions

Yes, for some users like technicians a text input is the easiest way. Why most developers use that kind for building SQL queries? Still, guidance and support is always appreciated (autocomplete for keywords/variables/...)


Test

Developers have a completely different view on that topic. I really recommend you to test, test and test it again with your users.

2

Is it only me or that the textual option you used is better than any of the graphical suggestions above? In my opinion, this is too complex to be solved solely by some sophisticated graphical arrangement.

Now, in this point you have to ask yourself, who is your target user? I guess this is meant for some professional/experienced user, not for a novice one. In this case, you may expect such user to format a search phrase written in rather simple language.

If you decide to take it, pay attention to the following which will make easier for the user:

  1. You do not need the quotation marks to "translate" this search string to code.
  2. Provide auto-complete where you can.

So, basically, it should look like this (two options for the auto-complete):

mockup

download bmml source – Wireframes created with Balsamiq Mockups

1

In case of non-techsavvy users, this might be a good way:

Whats about making your search criteria like common language. All links are changeable with a dropdown list or similar. Inputfields for numbers or text.

enter image description here

0

You can choose for fields and take values for each fields instead of fixing the field sequence.

enter image description here

0

The solution is in the interface. Don't make users interpret a tree of inputs. The interface should help the user understand their task.

Let the users focus on the filter they are creating. Use form fields, selectors, etc. only as inputs, not to display any information, and remove them once their function is complete.

By breaking the statement into logical blocks, you can use drag-and-drop to edit the statement and the blocks themselves can be edited vs. forcing users to traverse and change inputs. The interface can easily inform users when their filter is correct, inform users of errors in the logic, and how to correct errors.

Here's a mock of an interface like this. The inputs can be dropdown menus, pickers, etc.

Edited: added more description to mock

enter image description here

The user can add components to the statement, drag them into position, and the app can instantly give the user feedback on everything.

0

If your criteria are mostly simple then a Query By Example (QBE) approach may be sufficient, and simpler for User to learn, construct and debug, especially those users familiar with spreadsheets.

mockup

download bmml source – Wireframes created with Balsamiq Mockups

QBE can scale to more complex terms.

0

My company researched and tested various query representations for more than a decade now, and the one that works best with our users is a funnel-like representation, where OR conditions are laid out horizontally and AND conditions vertically. The AND conditions work like filters in a query in that they make the results set smaller, so we present them that way. This resulted in a visual query representation that is intuitive even for non-technical users, who may otherwise struggle with the distinction of AND/OR and the significance of brackets.

Your very simple example query would look at its most basic representation something like this: simple query

You can use this same layout for creating a query. You can further allow grouping of boxes for more complex queries. In that case, groups act like brackets as well. We've developed a drag and drop UI for adding and rearranging conditions which tested very well with our users, and even extended the UI further for adding If/Then/Else style logic, so it's quite flexible. I'll leave the design details out of this post, but there are many ways to turn this logic representation into a good UI.

0

Lots of good answers here. I am adding one more. If you are familiar with Microsoft TFS web interface. This is how they currently offer AND/OR based filtering. This also has sub criteria.

enter image description here

This only allows one level of cascading. There is a valid reason for that too. Adding more number of levels is overwhelming for the processing logic yielding marginally better outcome. A two level filter works for most of the cases.

Another Approach

In our application we have followed a two level approach. We have a top level AND OR selection radio button. Based on that selection all items are either ANDed or ORed together. We have provided an option of selecting any two or more items, and then create a subgroup (just a name we use for criteria one level below). This generated subgroup automatically moves the selected criteria together. Puts them in a bracket and switch the internal operator from AND to OR or from OR to AND whichever applicable. Based on our customer feedback, this has worked well for our business case.

Unfortunately, I can not share the screenshot of the same.

0

Two modes can be given to the user: Basic and Advanced.

  • Basic mode will cover only All/Any option to join all filters.

  • In Advanced mode, the user can write a complicated query using brackets.

Otherwise, putting all options in the given UI will make it a bit complicated, and maybe only few users want to use this Advanced feature.

-1

Have you considered something like a report generation rules wizard similar to outlook inbox rules wizard?

enter image description here

The reason I'm suggesting this is:

  • most users may be familiar with this kind of design which would improve user experience
  • the user can change parameters and keep it simple or as complex as they want

I agree with @gustav test with users

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.