Record validation

[jferard]

The problem

It's a common need to prevent the creation of invalid records in a data set. This enforces the quality of the data. And everybody knows that usual spreadsheets are not very good for this task.
With Grist, we can use Access Rules to prevent some values to be inserted in some columns. E.g. If (numeric) column "A" values must be greater than 10, we can have a rule: allow update or creation only if newRec.A > 10. But a record may be invalid even if each of its values is valid. E.g. I can accept any value for (numeric) columns "A" and "B", but reject a record that would not meet the condition newRec.A + newRec.B > 10.

What happens

If I try to enforce such a condition with an Access Rule, here's what happens when I want to have "5" in the column "A" and "7" in the column "B" of a new record (creation):

• I type "5" in the column "A"
• The condition becomes false ("B" is "0" so "A" + "B" = 5 <= 10)
• The value "5" in the column "A" is rejected
• The record creation is aborted.

The hack is to copy-paste "5\t7" on the row of the table.

What I would expect

• I type "5" in column "A": the value is greyed (or any other visual marker for temporary status) and not saved
• I type "7" in column "B": the value is greyed (or any other visual marker for temporary status) and not saved
• I press "enter": the Access Rules are enforced and the decision whether the record should be accepted is taken. ("Enter" means: I'm finished with the creation/update of this record.)
• If the record is valid: the color of the values is set to black (or any other visual marker for saved status) and the record is saved
• If the record is not valid: all values stay grey and a message appears, the record is not saved.

In other words, Grist would allow that during the update or creation, a record might become temporarily invalid. That implies that record creation would be validated after all values are entered (pressing "enter" is the signal) and the values should be saved only after that validation.
Obviously, this would be a non trivial change in the user interface of Grist. It seems to me that the benefits (quality of the data) outweigh the disadvantages (loss of flexibility), but I may be wrong!

Related

• Enhancement: add explicit field and record validation using py exceptions (or anything else) #849
• https://community.getgrist.com/t/access-rules-restrict-creation-of-new-record-until-all-mandatory-fields-are-filled-in/2822 (hack that forbids the creation of second invalid record)
• https://support.getgrist.com/enter-data/#editing-cells : "[Enter] saves your entry and moves your cursor to the next row".

[paulfitz]

Very interesting @jferard ! One related idea that has come up is a kind of draft mode. Where you hit a key to start accumulating changes that should be submitted as a single coordinated change. This comes up for people using Grist more like a database than a spreadsheet, or when there are integrations tracking individual changes that should not see intermediate states. What do you think of that kind of approach?

[jferard]

The idea is very similar. But if I understand this draft mode, it is the user that decides when to enter the draft mode and when to "commit" the changes. In my proposition, there would be an automatic entering in the draft mode at the beginning of the record creation or update, and an auto commit when the user presses "enter". Thus you can think of my idea as an "auto draft mode".
The draft mode you describe seems more versatile and more powerful than my "auto draft mode", but maybe more confusing for the user: "I can't enter the value in the column A! What is happening?" And after two hours seeking for a hint on the internet: "Ah, yes, I should use the draft mode and then commit the whole record 😠". (But I also imagine that there would be other use cases than the record validation for the draft mode.)
Maybe this "auto draft mode" could be a feature included in the more powerful regular draft mode you mentioned.

[paulfitz]

I hear what you are saying. The auto draft mode tweak sounds reasonable. I read that as: where a change is rejected because of access rules, the change isn't just rejected but preserved in the browser, and we effectively enter a draft mode. There could be decoration to explain what is going on, including an easy way to exit the new mode.

I'd be nervous about changing the default behavior of "enter", that is a bit scary for an app with a lot of existing users.

[jferard]

I'd be nervous about changing the default behavior of "enter", that is a bit scary for an app with a lot of existing users.

I understand that. Maybe that could be an opt-in at table level.

where a change is rejected because of access rules, the change isn't just rejected but preserved in the browser, and we effectively enter a draft mode. There could be decoration to explain what is going on, including an easy way to exit the new mode.

Imagine you have a condition on the record : "A" + "B" + "C" < 10:

• at the beginning, the condition is met;
• you enter "2" in the column "A": the condition is still met;
• you enter "5" in the column "B": the condition is still met;
• you enter "5" in the column "C": the condition is not met anymore, hence you enter draft mode.

But how do you leave the draft mode? If you correct the value in the column "C", it's ok. But the problem may be in the values in columns "A" or "B". If you can, in any way, leave the draft mode without fixing the whole record, the record is still created. Since you entered the draft mode in column "C", one can't expect a "rollback" of the whole record: values in col "A" and "B" are definitely saved.
So that would be equivalent to a column rule : "C" < 10 - ("A" + "B"). That doesn't seems to be a real "record validation" to me because the record is created and some values are saved. But I may be missing something...

[dsagal]

Is there a need to support this in a regular table view, or could such functionality (auto draft mode) be tied to particular UI? E.g. I could imagine the following: in a page where such functionality is needed, one could lock the table for editing, making it read-only with a button to open an editable record. Opening the editable record would enter the draft mode, where all the changes remain in the browser, until a "Save" button is clicked on the record. That would submit all changes as a single update.

In other words, this would be a more typical app interface, rather than the spreadsheet-style one-cell-at-a-time approach.

[jferard]

I imagine this particular UI would be like a form, with a "submit" button, but accessible from the document, not through an external link. Or a card-like widget with a submit button. That would be nice and would allow the owner to protect the data set from invalid records.
But, if it's a card-like widget, the user would have to enter the values one by one: no copy paste of a row or several rows at once. That would be a strong limitation, because that kind of copy-paste is very handy.
And if it's not a card-like widget, if it allows to copy paste several rows at once, then it's... a table (or something that looks like a table). So why not make this an option of the regular table view?

---

Beyond this UI question, there's a semantic question: what is the minimal element in a table that makes sense?

• In the spreadsheet world, this is the cell: the user will write the title in the merged cells A1:A10 and be happy with that, or use the sheet as a card and scatter the data and the header all over the sheet, or... the possibilities are limitless (and that's why spreadsheets are so popular).
• In the database world, this is the record (the "row") that makes sense. Even if you access specific columns, you first select rows rather than independent cells.

Since Grist "combines the flexibility of a spreadsheet with the robustness of a database", why not allow to choose the behavior, depending on the needs of the user?

(Okay, that's easier said than done and I'm aware that there are probably technical issues here, but that's another question.)

[emanuelegissi]

I vote for this feature! ☺️

[jferard]

Here is a workaround that I have found. You have to add two columns to your table :

• Validate : it's a Toogle/Switch column that allows the user to valide the record
• ErrMessages : it's a text column that displays the errors in the current record.

Now imagine you have three columns : "Anum" (a numeric column), "Bdate" (a date column), "CintGE10" (an integer column with a value >= 10)

Here's the "ErrMessages" formula :

col_names = ["Anum", "Bdate", "CintGE10"]
items = [(f, getattr(rec, f)) for f in col_names]
msgs = [
    "Column {}: empty".format(f) for f, v in items if v is None
  ] + [
    "Column {}: invalid type".format(f) for f, v in items if isinstance(v, grist.AltText)
  ]
if rec.CintGE10 < 10:
  msgs += ["Column CintGE10 shoud be >= 10, but is {}".format(rec.CintGE10)]
return "\n".join(msgs)

Of course, you can set the error checks (and associated messages) that you want. Here, I wan't the columns to be not empty and of the correct type and, as said, "CintGE10" >= 10.

Now, the trick is to accept the validation only if the "ErrMessages" are empty. This is ensured by the following access rules on the table :

condition	permission	message
rec.Validate	C	Can't create a pre-validated record
newRec.Validate and newRec.Messages	U	Can't validate an incorrect record
rec.Validate and newRec.Validate	U	Shoud unvalidate before update

Then you filter the records to keep only the validated ones in your widgets.

This is better than nothing, and may probably be improved, but there is a main drawback : a valid record may not be validated and thus ignored.