We need to add to our web app reports generator a formula editor. The formula syntax is can have:

  1. Data entities fields composed by S{table id, field id}.
  2. Aritmatic symbols: +,-,*,/
  3. Static numbers: .

Sample formula: S100,4*<0.5> //***Means: Multiply field 4 of table 100 by 0.5 Notes:

  • Tables have between 20 to 100 fields (hard to remember).
  • The goal of the editor is to encapsulate the syntax by some graphic UI and not to simplify the syntax.

As seen above, the syntax is not UX freindly, aspacally the {table, field} part.

What is the best recomnded UX design here?

  • Is it correct that there is no possibility to reference to certain cell, only to value of some field in current record (row)? – Alex Ovtcharenko Jun 29 '14 at 11:50

I have implemented something like this before (formulas a little more complicated). If you are using table and field names in your formulas, the most important thing is to avoid the user having to type them in. Indeed they shouldn't be aware of the underlying data storage at all.

My solution was to provide draggable elements that wrapped the table / field names from the data base and displayed a user friendly label. It is nicer for the user not to see the "technical" names.

enter image description here

The formula itself was built up from pairs of elements seperated by an operator (clicking on the operator gives a menu to change it), and the user can drag a formula onto any of the elements (except for the operator) to build something more complicated (like in the screenshot). Nested elements automatically have brackets rendered around them.

The only typing the user can do is clicking on the text box to enter numeric values, so the validation is very easy (no formula parsing required).

I also had other formula types (e.g. max and min like you have in excel). enter image description here

I implemented this in Winforms quite some years ago, and I had to do all the rendering and handling the mouse pointer / drag drop position myself. Definitely much easier to do it in Jquery / HTML5.

| improve this answer | |

Can you leverage an existing syntax, such as Microsoft Excel's formulas? If your user base has prior experience is spreadsheets it may be more comfortable for them to enter something like:


Where A is your table reference.

| improve this answer | |
  • The goal of the editor is to encapsulate the syntax by some graphic UI and not to simplify the syntax. – N.D.B Jun 29 '14 at 12:17
  • 1
    Respectfully, the simpler your syntax, the less need there may be for a separate graphical component. My solution may not be the best for your needs, and I'll give your goal more thought, but I'd ask you to consider simplifying your syntax as well. – Tim FitzGerald Jun 29 '14 at 12:24

Formula usage is functionality for extra users, some of them are familiar with excel, other can be familiar with programming languages and so on. In any case, your should help users to make formula writing and reading with minimum additional tools required.

So, at first use sensible names for tables and fields — using ID numbers is the lack of understandability of formulas. It is easier to remember names than abstract numbers.

Also, I'd recommend to use syntax similar to object oriented programming: [table name].[table field] — for instance, Contacts.Phone. It is simple to understand and to remember.

Naturally, this approach will make formulas longer, but in case of app reports the usage of such funcationality is rare. So the users will understand the formulas, correct or create their own, especially if you'll give simple and quick reference help in formula editor.

| improve this answer | |
  • The goal of the editor is to encapsulate the syntax by some graphic UI and not to simplify the syntax. – N.D.B Jun 29 '14 at 12:18

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.