Currently I have a web application where a user can use dropdown lists to generate SQL SELECT statements like so:
Column Select Dropdown | Operator Dropdown (= != > < <= >=) | Value select dropdown
The user can do this multiple times, and the "filters" are currently all ANDed together.
I want to add the possibility of creating OR statements. I could very easily add ORs in the case where the columns are the same, but what about complex logic statements like
((A OR B OR C) AND (D OR E)) OR (F AND G)?
How can I let users create such statements in a user-friendly way?
EDIT: To specify, user-friendly for a general audience. Currently I work with developers that occasionally hand-code SQL queries for a non-technical client that needs specific information from our database. The goal is that this web app will remove the need for us to hand-code them by giving the client an easy-to-use tool to do it themselves.
EDIT2: Currently the app is not being used by end users. The only data I have as to its use are previous handwritten SQL queries and thus the kind of queries the client asks for. Given that I could simplify it (e.g. limit the users' ability to generate queries to the kinds of queries they tend to ask for), but I want to see if anybody has experience communicating boolean logic simply and completely in GUIs.
Thank you for your time.
When you need to handle ( (A or B) and C) or (D or E or F), you're working with a tree-like data structure. In my experience, there's no easy way to represent decision trees to users in a "pretty" or "intuitive" way. Its doubly hard in ASP.NET webforms.
However, one tried and true approach is the following: single textbox accepting a where clause. Trust me, the single-input approach really is the most simple and intuitive user interface, and it also has the advantage* of allowing rapid input/modification of query filters.
** Another advantage, from the technical side, is being able write your own lexer/parser and AST. How often do you get to do that in a basic crud app :)*
You're already going to be training your users how to use your ad hoc query engine, you may as well train them that typing (account.Balance < -2000 and account.Type == 'Checking') OR (account.Number = 123456) returns exactly what it says it returns.
If you go with this approach, provide the user with a dropdown list of available columns, so that double-clicking on an item inserts the item into the textbox at the cursor location.