Handling parsing and formatting in cells during user input

[pavish]

Our current number input parses and formats values(such as adding group separators) as the user types them. This leads to several race conditions, and a non-conventional input style.

Issues with our current input:

Screen.Recording.2022-04-06.at.3.05.40.PM.mov

1. Notice that it is slightly hard to follow the number while typing as the group separators keep changing positions. This is a major concern.
2. Editing in the middle shifts cursor position. There is an open PR which fixes this, however, that comes a few bugs and a lot of edge cases to handle.
3. Backspace on a grouping separator does not remove it, it does nothing.
4. While (2) and (3) are interesting technical problems to fix, I am not sure if it's worth spending more time on this.
5. Formatting while the user is typing is common in certain cases like phone numbers and dates. However, in these places, the length is known and the separators are always shown even when the input is empty. In a number's case, the separators are created dynamically since the length is not fixed. This is a bit unconventional.

The Spreadsheet approach

Both Google sheets and Excel follow the following editing interface:

Screen.Recording.2022-04-06.at.3.03.21.PM.mov

1. While user enters edit mode, only a plain un-formatted number is shown. The user edits this just as they would edit any field.
2. Once user exits edit mode, the numbers are formatted.
3. This is the easiest option to implement.
4. However, it does make the input a bit hard to follow while shifting from select mode to edit mode, since all grouping separators are removed.
5. This approach was suggested by @seancolsen in Mathesar money display and db options #1243 (comment) for money input.

Airtable's approach

Screen.Recording.2022-04-06.at.3.04.33.PM.mov

1. While user edits the cell, the existing separators are not removed.
2. User can edit the cell anywhere just like they would edit a text field. They can remove or add separators. But it's not automatically added while the user just types in the number.
3. Once user removes focus from the cell, the value is formatted as expected.
4. It is easy for the user to follow what they are typing since there is no sudden shift of cursor or change in existing value.
5. This is so far my favorite option.

---

I would like us to decide on an approach for us to go with.

My option would be to follow Airtable's approach. We already have the code for formatting and parsing, we'd just have to change our logic to do the formatting once the input loses focus.

[seancolsen]

We discussed this at today's meeting.

We all agreed that there are pros and cons to all three behaviors.

Here's a summary of people's ranked preferences:

Person	Rankings
Pavish	(1) AirTable, (2) Spreadsheet, (3) Current
Sean	(1) Current, (2) Spreadsheet, (3) AirTable
Ghislaine	(1) Spreadsheet, (2) Current, (3) AirTable

From those votes, it seems like the spreadsheet behavior is the winner.

It would be good to get your preferences, @kgodey

[kgodey]

After reading through the original post, my preferences turned out the same as @pavish, I think it's most forgiving to the user while also having the structure of separators to work with (it's hard to edit larger numbers using the spreadsheet model).

I might be missing some cons, though, @ghislaineguerin @seancolsen I'm interested in why this option was least preferred for you.

[seancolsen]

why this option was least preferred for you

Because the separators become incorrect as you edit the number, which can lead to data entry mistakes. I'd rather see a number with no separators than see a number with incorrect separators. Really, I'd rather see a number with correct separators, which is why I prefer "current" 😁

[seancolsen]

Here's a summary:

	"Current"	"Spreadsheet"	"AirTable"
Cursor position on screen is stable while typing	❌	✅	✅
Formatting is retained when entering edit mode, making it easy for me to find my desired edit location	✅	❌	✅
Free of risk from incorrectly reading value due to misplaced separators	✅	✅	❌

[kgodey]

Thanks for the summary, @seancolsen, it's very helpful. I'm going to think a little bit more about it before I post my final opinion.

[seancolsen]

A couple anecdotes:

• At my previous job, we maintained a B2B SAAS product used by insurance sales agents who were frequently typing currency numbers into forms. In response to several user requests, we pushed some changes that auto-formatted numbers as people typed (basically changing from the "spreadsheet" behavior to the "current" behavior). We got a lot of positive feedback about this change. It was a small change, but I probably got more customer praise for that than any other feature I worked on.

• TurboTax, the most popular tax filing product in the US, uses the "current" behavior for their currency inputs. Here's a demo

  

  I like their focus indicator and the way they've done their currency symbol (though it would be a little hard to do the same for our currency symbol).

  But you'll also notice that there's a lag in the formatting. They did a bad job with this implementation.

In my experience, inputs that auto-format numbers are common enough that they don't feel weird to me at all. Seeing my numbers correctly formatted while I type helps me to ensure I'm typing the values I intend. I appreciate that.

[kgodey]

After some thought,

• I don't think the spreadsheet option is good for us. Unlike spreadsheets, data type and formatting options in Mathesar do not vary on a cell-by-cell basis. We can make data entry an easier experience for users with the information we have.
• I played around with both the current implementation and Airtable's implementation. I prefer our current implementation (provided the bugs are fixed). It's easier to not make mistakes.
  • I'm wondering if we can show the formatting (separators, etc.) as grayed out (similar to how some phone number entry fields work), and make it really clear that the user only needs to worry about entering digits.

[seancolsen]

@kgodey

We can make data entry an easier experience for users with the information we have.

Yes. My thinking exactly.

show the formatting (separators, etc.) as grayed out

Wow, that's a really interesting idea. I can't recall having ever seen an input like this. Do you have an example of one in the wild? Using different formatting for some characters within the input would most likely be a lot of work. Anything's possible, of course. But we'd need to provide a significantly more complex abstraction on top of the browser's native <input> element. I'd rather not do something like that unless it's absolutely necessary.

make it really clear that the user only needs to worry about entering digits

Besides the idea of formatting the grouping separators as gray, do you have any other specific suggestions for making it clear to the user that they only need to worry about entering digits? My thought is that users will figure it out pretty quickly. If they attempt to type a grouping separator into the field, it's not a huge problem -- the grouping separator they type will not be displayed, and the user will learn not to worry about typing them.

Again, I want to highlight my experience at my last job where I implemented this auto-format behavior. Not only did we get a lot of positive feedback from customers -- also nobody complained about it. That was on a product where over 1k people were entering numbers into these auto-formatted text fields almost all day at their job.

[kgodey]

@seancolsen I can't think of a specific example, I'll post one if I run into one in the future. I don't think it's a necessary part of the solution though, I was just wondering how possible it was.

We should just go ahead and implement what you've implemented before.