Idea for simple expression builder UI

Hoping to get some feedback here (or may be this will be useful to someone looking at solving a similar problem). We need to code a simple visual control for building basic expressions — this is needed to add derived (calculable?) fields to our reporting engine.

The Problem

In a report there is a number of predefined fields, each with their unique name (and a description). We need to have a way for users to create their own fields, which would be based on already available ones.

For example, if we have fields QtyBefore and QtyAfter, the user may want to add to their report possibility to display the delta fraction (which would be (QtyAfter - QtyBefore) / QtyBefore). We can't really predict all the possible fields that may be needed, thus it would not be reasonable to stuff the original report with all of this — so, we'd like to allow the user to be able to create such derived fields.

Yet we don't really need all the power of Excel-like formulas, and would like to create a mechanism that is simple, is (more or less) fool-proof, and is not too complicated to implement (being wise developers and all).

Proposed Solution

So here's the idea, let me know what you think.

The expression is originally built as a simple Operand 1 | Operation | Operand 2, where both operands and the operation can be selected from the drop-down, like this (Balsamiq is great for quick UI ideas!):

The fact that you select from the drop-down means you can't go wrong! So, you can create a very simple field with just that approach:

The drop-down uses field descriptions, which are user-friendly (and passes back to the server the field names, which are unique).

Being the observant kind you'll notice the last entry in the operand drop-down — “Complex operand” (there could be a better name for it, suggestions welcome). I think of adding small foot-note to the control to briefly explain its usage. Selecting that option will replace the drop-down with another simple expression, adding brackets for clarity:

The small “cross” icon on the bracket allows to revert from complex to simple operand selection.

Such recursion allows creating any number of levels of inserted statements (for addition and subtraction this is unavoidable).

That's all!

This could be easily expanded to have “Functions” and “Constants”, by adding more operand types. A predefined list of functions would use their own number of operands. This is getting too close to Excel-formula territory, though, and we don't want our users to suffer from headaches!

What I like about this idea is that if all the fields are selected (no ellipsis left), there is no way to construct an “incorrect” expression — all the fields and operations are predefined, and the precedence is defined by brackets (or “complex operands”).

A potential problem is that it can grow “wide” very quickly. Although I guess it is the case with mathematical expressions anyway. It could be partially solved by making division operand a horizontal line (effectively halving the width of that part of the expression), but I don't really want to overdo it.

Also, there could be too many fields in the report to choose from, endless drop-downs — that could be solved with a smarter drop-down, one that allows free-text searching (that would be nice anyway, actually).

By the way, evaluating such an expression can easily be done with Dijkstra's “Shunting-yard algorithm”. I learnt that in the Algorithms course, so studying is great!

Let me know what you think, perhaps you have ideas of simple improvements!


  1. Excel-like typing in formulas still seems simpler and more straightforward. Especially in case of something like A + B + C + D + E where it comes to 3 seconds of typing vs. 10 seconds of drop-down juggling. It's not difficult to validate formula as it's typed and give immediate feedback and field names can be hinted like it's often done for tags.

    1. With command-line style the main concern is that the users may not know how to find the fields they need — seeing them in a list is a great help.

      Though we can still show them beneath the command-line input, and validate on-the-fly, as you describe. Yeah, you may be right.

      Yet for less tech-savvy users just selecting things from drop-downs, with automatic solid protection from any kinds of errors may still be preferable, don't you think?

  2. How to implement that UI? Any hint.

    1. In this form we implemented it using jQuery, there are several interactions, but nothing too complicated.

      Though I have to tell you that since this article our custom expression UI went through 2 reworks, and the most recent version allows free-text entry of expressions, but emphasizes validation and error reporting, so if the user made a mistake we try to help them as much as possible.

      Perhaps I'll write about this soon, thanks for the reminder :)

    2. Will love to see you recent work! The initial UI looked very promising, so I'm very curious how far did you went, without making it too complicated.

  3. Having the same need here. We don't seem to be able to find a reusable UI component, particularly because there are custom functions (with custom parameters) that are specific to the data we handle. Seems like we'll follow your example and go in-house!

  4. Very interesting. Would love to see your last work on this subject.


Post a Comment

Popular Posts