Row and cross-field validation

[dave.stewart]

Following on from this post about referencing validation errors we need to look at validating rows.

We saw this topic:

• Validate a specific row or cell

Right now, it seems that there are significant limitations the Handsontable that make row validation more onerous than it should be:

• there is no way to validate a single row
• for each validated cell, there is no context to the row’s other values (the model, in classical terms)
• for each validated cell, there is no way to optimise the fetching of the row data (i.e. do it once per row)

As for workarounds:

• we were considering validating models outside the table, then setting cells as “invalid” but there doesn’t seem to be a way to do this, as validation-specific metadata seems to be held inside a private hash called priv

@aleksandra_budnik - can you tell us what the progress in this area is?

If it is not going to be solved any time soon, we may consider forking the library in the interim, and adding this functionality and optimisation ourselves.

Clearly, this is far from ideal as we will miss out on any future improvement or updates, but right now, cross-field validation is by-far the most important feature to us!

Can you advise?

Many thanks

[dave.stewart]

Here is an example of cross field validation, where Settlement Date must be before Term End Date (and vice versa) so the rules are:

  settlementDate: 'from_today|earlier_than:termEndDate',
  termEndDate: 'from_tomorrow|later_than:settlementDate',

Our Vee Validate validator handles the actual validation for us, in a validator which we wrap in table creation to allow messages to be shown within the context of the table:

In the GIF above, you can see how setting one field to an invalid date should invalidate both, and correcting either should validate both.

Actions:

1. Set valid Settlement Date
2. Set valid Term End Date (later)
3. Invalidate Term End Date by setting it earlier than Settlement Date
4. Correct cross-field validation by setting Settlement Date earlier than Term End Date
   (additional actions demonstrate setting values to correct validation)

At this point, both fields should validate again.

I have been experimenting with validateCell() but I can’t get the table to redraw with the updated validation.

Is there a way for us to achieve this?

[dave.stewart]

Oh wow - I managed it!

I’ve been through a few iterations, and this feels like a lot of external code to make it work, but it works.

In our afterValidate() callback, as each cell validates, we check to see if its validator is dependent on another prop. If so, we write to an array of “changed” cells, which we will use at the end of the edit lifecycle in afterRender:

      afterValidate (isValid, value, row, prop, source) {
        // track errors for showing in tooltip
        if (!isValid) {
          this.errors.set(row, prop, this.validation.error)
        }
        else {
          this.errors.remove(row, prop)
        }

        // cross-field validation check
        if (source !== 'user') {
          const dependents = this.validator.provider.dependents
          const dependent = dependents && dependents[prop]
          if (dependent) {
            this.validation.changes.push([row, dependent])
          }
        }
      },

The errors, validation and validator members are part of our external validation machinery.

When all the validation hooks are done, the table will render.

In afterRender() we check for any changed cells, then set the data for all dependent cells to the same data effectively, forcing validation on those cells:

      afterRender () {
        const changes = this.validation.changes
        if (changes.length) {
          // get existing data
          const data = this.hot.getData()

          // build updates
          const updates = []
          changes.forEach((change, index) => {
            const [row, dep] = change
            const col = this.hot.propToCol(dep)
            const value = data[row][col]
            updates.push([row, dep, value])
          })

          // apply updates
          this.hot.setDataAtRowProp(updates, 'user')

          // reset changes
          this.validation.changes = []
        }
      },

This “build then update” approach is the most performant I’ve managed so far, and allows us to do things like auto-fill and copy / paste.

It seems to be working for now, but I’m open to improvements.

Do you have any ideas @aleksandra_budnik ?

[aleksandra_budnik]

The first idea was to check what we’ve done in the advanced validation blog post. We’ve also mentioned a cross-column validation here https://handsontable.com/blog/articles/2018/10/advanced-data-validation-in-handsontable (but here’s a checkbox and a numeric).
Your example is a bit different as here in the blog we’re just working with booleans so it would take me some time to try to create an another approach for your case. At this point I do not see any reason why this validation wouldn’t work. Great job, Dave.