User Experience Stack Exchange is a question and answer site for user experience researchers and experts. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm interested to know how people have, or would construct an interface which simplifies constructing logical boolean conditions? (Yes, it's a database query builder) In my mind a bad interface has them typing lots of AND, NOT IN, OR keywords etc. Or else has a million combo boxes.

I'm wondering if anyone has a good example I could glean ideas from? Or some suggestions?

At the moment I'm considering a split screen with a grid for composing the query and a canvas to display a visual representation of the sets of conditions and how they overlap etc. Like a Venn Diagram. This still gives me a problem with the usability/intuitiveness of the controls on the grid.

EDIT: I'm very interested in ideas which simplify it for less technical users. However, ideas for a version of the UI for advanced users are very helpful as well.

share|improve this question
3  
Be careful of using the word 'intuitive'. In my experience there is no such thing. :) The 'best' solution for one person may be confusing for the next person. – Stewbob Sep 30 '10 at 22:32
    
@Stewbob See this question for discussion on the meaning of "intuitive": ui.stackexchange.com/questions/1282/… – Rahul Oct 3 '10 at 14:55
    
If it fits your situation, it can really help if the user can start with a set of examples. For instance, if I'm creating an e-mail filter, I could select a load of emails which should match it. From that it's a relatively simple task to suggest a starting point for the filter, which the user can fine-tune. You can also help the user in their editing process by showing live examples of which records match and which records don't. – Peter Dec 16 '14 at 11:08
2  
reviewing the answers below, I think one of the keys is replacing the nomenclature of AND/OR with ALL/ANY. – Dvir Adler Oct 1 '15 at 19:07
1  
Dvir, AND/OR is just the starting point. Moving aware from those terms is the essential first step. – Edward Williams Apr 7 at 4:13

16 Answers 16

up vote 40 down vote accepted

See also iTunes 9 or later. It adds the ability to do "nested" AND / OR expressions, akin to how a programmer would do it with parenthesis:

alt text

share|improve this answer
    
Can AND and OR be combined with this interface? Or is it either "all ANDs" or "all ORs"? – unor Nov 21 '12 at 10:01
4  
iTunes 11 has hidden the nested boolean logic somewhat; you must now Option-click the plus button. – James Emerton Mar 11 '13 at 21:42
2  
Although the UI is capable, I dont think of it as particular intuitive. Granted this is a very difficult requirement to make intuitive. – Glen Lipka Feb 15 at 18:24
    
We implemented this on our solution about 2 years ago. Even though it does work, the feedbacks we get is that it's a total hell to use. Still looking for an alternative... – Florian F. Mar 8 at 21:20

It depends on the level of sophistication of your users. We have something similar in the current version of the interface have left out grouping and fixed the conjunction between to terms to OR. Each term can be negated. Most users are fine with that kind of querying and most of them would not be able to formulate more advanced queries correctly. We are now implementing a two step process in which the result of a query can be used to limit the population in a following query (still leaving out explicit grouping in the UI).

The UI for this is a basic list of constraints that can be added, removed, activated and deactivated and negated. This has been sufficient for most queries that users want to create. We had a design for a new version that was based on a dataflow concept (taking strong inspiration from vistrails, see below) but that got never executed. Vistrails is a product that can be used to created VTK pipelines. If you want to go the route of lots of pieces of UI, dataflow works for formulating queries, but also works for formulating output formatting.

Look for inspiration in

share|improve this answer
    
Edited question to specify my audience type better. Answer: both, ideally. Thanks for the examples, had forgotten about yahoo pipes. – Edward Williams Sep 30 '10 at 10:57

I like the way Apple Mail's rules work:

screenshot

share|improve this answer
    
I like that interface too. It gets around the problem @Michael mentioned: people who don't know boolean logic by definition don't understand what AND and OR mean. – Patrick McElhaney Sep 30 '10 at 12:50
1  
I think Apple avoids boolean logic quite well by using "any" (:= or) and "all" (:= and). – LennyUserExperience Oct 1 '10 at 7:24
3  
However, it only allows very simple queries -- if you want to do A AND (B OR C), you can't. Which is odd, because it would be rather useful, and Apple do allow you to construct such queries in iTunes and Finder. – scottishwildcat Jun 17 '11 at 20:30

The main problem non-technical users have with Boolean logic is understanding the difference between AND and OR because it doesn’t always correspond to natural language (e.g., “show me orders from New York and New Jersey” almost certainly means Location = NY OR Location = NJ). Often users tend to interpret "or" to be an an exclusive OR. Then there’s the issue of losing track of nested parentheses.

One solution that avoids both of these issues is to represent the logic graphically with a plumbing or electrical metaphor. There’ve been a couple lines of work taking this approach:

Shneiderman, B (1991). Visual User Interfaces for Information Exploration. Proceedings of the 54th Annual Meeting of The American Society for Information Sciences, 28, 379-384.

Murray NS, Paton NW, Goble CA, Bryce J (2000) Kaleidoquery—a flow-based visual language and its evaluation. Journal of Visual Languages & Computing, 11(2), 151-189.

Query builders are one of the few places where it makes sense to have separate Basic and Advanced modes. You’ll probably find that 90% of your users' queries fit only a few patterns (e.g., “Customers with name beginning with Xxxx,” “Accounts I’m responsible for with outstanding bills,” “Orders made between date a and b”). It makes sense to have these as canned or semi-canned queries that can be selected and specified easily while putting something like Kaleidoquery under Advanced for the rarer ad hoc querying.

share|improve this answer

My favourite UI for rule building is ATG's Scenario server. Check this out:

alt text

share|improve this answer
    
Can I see more screenshots of this? I'd know how to access them on the Oracle website. – Fresheyeball Apr 21 '15 at 16:15

If your users are advanced enough to know the hierarchy of a query, any graphical interface you give them has to be fluid enough not to get in their way. I think an interface based on dragging and dropping elements to create an implicit hierarchy is ideal. Here's an extended annotated visual example of how a user might construct the query (A and B) or ((not C) or D):

Drop A onto panel.
+---+
| A |
+---+

Drop "and" after A.
+-------------------+
| +---+     +-----+ |
| | A | and | ... | |
| +---+     +-----+ |
+-------------------+

Drop B onto "...".
+-----------------+
| +---+     +---+ |
| | A | and | B | |
| +---+     +---+ |
+-----------------+

Drop "or" after "and".
+--------------------------------+
| +-----------------+            |
| | +---+     +---+ |    +-----+ |
| | | A | and | B | | or | ... | |
| | +---+     +---+ |    +-----+ |
| +-----------------+            |
+--------------------------------+

Drop C onto "...".
+------------------------------+
| +-----------------+          |
| | +---+     +---+ |    +---+ |
| | | A | and | B | | or | C | |
| | +---+     +---+ |    +---+ |
| +-----------------+          |
+------------------------------+

Drop "not" onto C.
+--------------------------------------+
| +-----------------+    +-----------+ |
| | +---+     +---+ |    |     +---+ | |
| | | A | and | B | | or | not | C | | |
| | +---+     +---+ |    |     +---+ | |
| +-----------------+    +-----------+ |
+--------------------------------------+

Drop "or" after "not C".
+-----------------------------------------------------+
|                        +--------------------------+ |
| +-----------------+    | +-----------+            | |
| | +---+     +---+ |    | |     +---+ |    +-----+ | |
| | | A | and | B | | or | | not | C | | or | ... | | |
| | +---+     +---+ |    | |     +---+ |    +-----+ | |
| +-----------------+    | +-----------+            | |
|                        +--------------------------+ |
+-----------------------------------------------------+

Drop D onto "...".
+---------------------------------------------------+
|                        +------------------------+ |
| +-----------------+    | +-----------+          | |
| | +---+     +---+ |    | |     +---+ |    +---+ | |
| | | A | and | B | | or | | not | C | | or | D | | |
| | +---+     +---+ |    | |     +---+ |    +---+ | |
| +-----------------+    | +-----------+          | |
|                        +------------------------+ |
+---------------------------------------------------+

Individual query elements (A, B, etc.) are constructed before being dropped into the panel, using combo boxes or whatever elements are necessary. Small margins and alternating colours could make this very readable, as well as display rules that make, for example, a chain of ors display at a single level:

+-------------------------+
| +---+    +---+    +---+ |
| | A | or | B | or | C | |
| +---+    +---+    +---+ |
+-------------------------+

Naturally, query elements could be expanded, collapsed, re-ordered, and edited after being dropped into the composing panel.

I'm not saying this is the easiest way to construct a system for this purpose. In fact, from a development standpoint, it's probably about as difficult as it can be. But it's the most efficient and intuitive thing I can come up with, and anyway it's basically just a clone of the Apple Mail rule UI but with more emphasis on hierarchy.

Hope this proves useful in your search for what's right.

share|improve this answer
6  
If the users are smart enough to understand the difference between and and or in boolean logic, they sure are able to type or select `and' and 'or' in a control. Making them drag the word 'and' is very exhaustive. – GolezTrol Jun 9 '11 at 20:41
3  
Example of this type of UI is implemented at datasift's query builder tool : dev.datasift.com/editor/getting-started/demo. – Nitin Jadhav Apr 2 '13 at 9:25

If it's mobile, have a button for each operation that simply enters it into a text box. Provide a link to help on boolean algebra and the like. That would be both easy and flexible.

share|improve this answer

Here's an interface for composing boolean logic.

Interface for composing boolean logic

A Few Thoughts

  • The interface starts out simple
  • If it gets complicated, it's because the user built it step by step
  • No editing or drag/drop - just creating and deleting branches
  • Conditions are a simple dropdown in this example, but could be more complicated or possibly negated

Ambiguity

As an aside, I've also been concerned with the ambiguity of "show me red and blue" shirts since the user might technically means "red or blue" shirts.

I think if you ask them describe the singular version (a "shirt") then the problem is somewhat reduced. For example, they wouldn't say "Show me a red and blue shirt" if they mean "red or blue".

share|improve this answer

This one tested well with both the technical and non-technical users and can generate pretty much any possible database query...

db query builder

The benefits are that it's very clear and a user can drag and drop (or delete) any expression or group of expressions in the tree.

The down side is how much space it consumes.

share|improve this answer
1  
I like this. Is it online somewhere to view? Either way, thanks for the inspiration. – John Pancoast Aug 24 '15 at 16:47
    
This is a great solution- I came up with something similar but it still felt unclear- the colour coding really finish it off! – Shawson 5 hours ago

Microsoft Access had a reasonable attempt at a simple database query UI by producing a visual version of "Query by Example"

It has a more natural language that avoids need for nested UI, at the expense of occasionally slightly more redundant entries in lines.

mockup

download bmml source – Wireframes created with Balsamiq Mockups

share|improve this answer

I am working on a redesign of a web app which uses Boolean and the image below shows how it is currently being done. The user can delete the brackets where needed or add them back in. I am struggling to find a better way to do this so may end up keeping this part which users seem to be using quite well.

UI boolean

share|improve this answer
    
I take it the app is targeted at technical people? The brackets and use of AND/OR seem to point to that. – Edward Williams Apr 7 at 22:02

There is a jquery plugin to do this, called QueryBuilder, which do this in an interesting way : http://mistic100.github.io/jQuery-QueryBuilder/

Jquery QueryBuilder Screenshot

share|improve this answer
    
Just because plugins exist, that doesn't mean they're any good. From a UX perspective, is this good? Is it intuitive? WHY is / isn't it? This isn't really an answer to the question, it's just a screenshot and a link with no description or reasoning. – JonW Mar 22 at 14:44
2  
@JonW This is an answer to the question I'm wondering if anyone has a good example I could glean ideas from? – sboye Mar 23 at 11:38
    
Examples as parts of answers are fine, but you still need to explain them - state why they are good. – JonW Mar 23 at 11:41
2  
+1 for the tip....a helpful answer IMO – HDave Mar 29 at 15:29
    
Agreed, this is a great option. – plainclothes Apr 6 at 4:21

For creating relatively complex single table queries, pivot tables are very useful.

Good stuff

  1. You can get SUM, AVG and GROUP with relatively little knowledge.
  2. By splitting fields across columns vs rows you get AND queries
  3. The totals give you OR queries
  4. You can properly 'build' queries - i.e. you can quickly see a master set, then add rows / columns and then add in filters which shows you which data is available to filter out

Bad stuff

  1. I guess you'll hit limits for if you try to combine more than one table / data set.
  2. Depending on how deeply nested you want your AND / OR queries you might have problems

But at least you don't have a ton of combo boxes, and the combo boxes you do have are more intutive.

Here's one I made earlier with some lovely fantasy football stats to go with it.

enter image description here

share|improve this answer
    
pivot tables are powerful, but I always feel like I need to find an expert in quantum string theory to make them work. I don't feel like they're a good UX/IU example at all. (but +1 becauase it's a great addition to the conversation!) – Baronz Apr 8 at 15:07
    
@Baronz I guess I'm used to working with analysts for whom pivot tables were their bread and butter. I was taught about pivot tables by them rather than the other way round :) The great thing is the immediate feedback, but I can see how they might be a problem for novice users. – icc97 Apr 8 at 16:45

As opposed to re-using pivot tables as I previously answered, this is an experimental UI that I thought of to handle the repetitive need for writing AND or OR.

It relies on one element you must learn that ANDs are horizontal and ORs are vertical. It manages to deal with fairly complex Boolean logic though.

Overview

Assume that A, B, C, D, and E are Boolean expressions.

For testing the concept here is how you would draw the two different sides of a standard Boolean equivalence:

(A and B) or C === (A or C) and (B or C)

enter image description here

This extends to more complex queries:

((A and B and C) or D) and E

enter image description here

Actual UI

For this table of data:

enter image description here

The screen is split in two:

  1. A set of filters (effectively AND queries)
  2. A combination of the filters

You set the filters (these are just typed with basic conditional log) and then drag that across to 'merge' it with the full query.

enter image description here

After dragging the new set of filters across, the left-hand side returns to an unfiltered list and the right-hand side shows the 'merged' data set:

enter image description here

You should then be able to drag around and edit the expressions on the right-hand side - but that needs a lot more work.

share|improve this answer

It is hard to pin down exactly what is intuitive for something that has traditionally been dealt with by technical users (that won't necessarily find interfaces more user-friend compared to command prompts). There is a particular reason for this efficiency, as queries can be unambiguously specified and executed in a command-line prompt. Also, it probably won't surprise anyone that traditional interface design will probably break when it comes to more complex queries.

Nevertheless, I think it is probably safe to say that when it comes to Boolean logic, the most common/familiar theme has to be the Venn diagram. So the question might be how to take the precise statements of database queries and combine it with the simplicity of a Venn diagram like interface?

I think conceptually a possible solution would be to combine both spatial layout with user interactions that reflect the nature of the operations that is being performed. This way you will get the concept of the Venn diagram across to the users while making the operations 'intuitive' enough to understand. So that is effectively taking what @bendytree and @sboye suggested for doing the actual data entry but also outputting the result of those operations in the form of a Venn diagram so that the user can see immediately whether they have performed the right type of boolean operation. But you might want to make the input even easier by implementing some drag'n'drop for the boolean operators and datasets/fields that you want to display, taking inspirations from some of the interactive Venn diagrams that you can find on Google searches.

share|improve this answer
    
Related interface, showing joins based off a simple Venn Diagram: sql-joins.leopard.in.ua. Not sure how well Venn Diagrams work for a single table though. – icc97 Apr 13 at 15:07

There's quite a few good ideas/references here, especially to some existing approaches. Often, though not always, Apple's approach is a good place to start - but perhaps in your case it may not be. I get the impression (though you haven't actually said it) that you're working with an awful lot of data comprised of many many fields/variables.

I agree that trying to find a way to simplify it for less technical users is a good idea, so long as you expect to have less technical users using the system. Otherwise developing a less complicated interface may be a lot of work for little gain. I also like the idea of a Venn diagram - would be interesting to see how that pans out.

However, in terms of actual suggestions on how to simplify this, another approach would be to just create some sort of "Wizard" process that steps the user through the process with a combination of natural language and a familiar 'web' look and feel. Below is a mockup of how this could work, using an example of a motor vehicle database:

enter image description here

The above shows how Step 1 could work, giving users a number of options they can select by ticking the relevant checkbox. They can select one or more checkboxes as necessary (or maybe not any?!). If they select a checkbox for an option that requires further info then the relevant words are hyperlinked. Clicking on the hyperlinked words would present the user with something like the example below:

enter image description here

So, the example above shows what a user may see if they've selected the "Vehicle is produced by particular manufacturers" checkbox and then clicked on the hyperlinked text to select the manufacturer(s) they want included in the search results. Of course your approach may differ depending on whether they should see a free text field, a drop-down list of options, etc.

Now, to deal with any "exceptions" to the search criteria, you would basically recreate the first window again, but with different wording, like:

enter image description here

So, after selecting the fuel exception above, the user clicks on the hyperlinked words "particular fuel" to choose the exception as below:

enter image description here

Once again, this could be a drop-down list, radio buttons, etc depending on what best suits the condition. They would also go through the same process to select the country they didn't want the vehicle to be manufactured in.

Of course, I see this "Wizard" approach as being for less technical users. You would also offer an "Advanced" option for users comfortable with a more complicated approach that may be streamlined compared to the Wizard approach.

ADDENDUM

Okay, this kept me up last night. I actually see this Wizard approach as being quite a good way to go, so thought it worth coming back to improve on my answer as it may help someone else if not you.

I've updated the mockup images above and wanted to expand on your idea of using a split screen.

Originally I thought that after the last step was complete, then something like your Venn diagram idea could be used to visually indicate what the user has chosen. But then I thought about my original assertion that there should also be a way for the user to go back and forth to amend their criteria. So now I'm thinking that using your split screen you could achieve both. Below is a new image to illustrate what I'm thinking:

Split screen view

So, the above is an example of what could appear in the second part of your split screen. As the user selects their criteria, this updates to indicate what their selections have been. This approach also uses the familiar web look and feel of highlighting their choices with hyperlinks and, if they wanted to, they could change their criteria by clicking on the hyperlinks within this screen, rather than having to go back and forth between screens to redo their steps. Of course, if they wanted to select a new condition, they would need to perhaps go back to the relevant step. But you get the idea.

The only other thing I'd like to mention is that without knowing the complexity of your data, you may need to refine this Wizard approach. My simple vehicle database only needs two steps, but a complicated one may need more steps. The point of this approach though, is not so much in the number of steps, but in the fact that the Wizard 'talks' people through the steps using natural language as much as possible.

Anyway, I hope this, along with the other answers provided, gives you some food for thought. And perhaps some others. This is a good topic and one which I think will be relevant to many users.

Good luck!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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