How to accomodate and/or with brackets in field filtering system

Question

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:

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?

Answer 1

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.

Answer 2

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.

---

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

One more thing from another ux.se post:

• Use background color changes for intended blocks

---

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.

Answer 3

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):

^{download bmml source – Wireframes created with Balsamiq Mockups}

Answer 4

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.

Answer 5

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

Answer 6

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

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

Answer 7

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.

^{download bmml source – Wireframes created with Balsamiq Mockups}

QBE can scale to more complex terms.

Answer 8

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:

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.

Answer 9

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.

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.

Answer 10

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.

Answer 11

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

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