Advanced Data Validation in Handsontable

Aleksandra Budnik Recipes / October 30, 2018

Advanced Data Validation in Handsontable

In this piece, I’d like to share more ideas I had (but decided to wait for ‘another time’ to use) while composing the Guide to Basic Data Validation. In the first part of this short series, I explained the validation process as it is in Handsontable.

This time I want to share with you some higher-level scenarios that I reviewed with our clients. I believe that businesses are looking for very similar recipes for reliable validation, so hopefully these patterns will help you to build your project as well.

Numeric data

Imagine you have a spreadsheet that contains a list of customers who share their phone number to receive information about a package delivery and your courier uses it to call them. The spreadsheet allows the courier to begin a new voice call simply by clicking on a client’s phone number.

To help implement such a feature it is technically possible to use <input type = "tel">, which is attached to any given cell in the Telephone column. What we are doing in this example, however, is using a custom renderer that adds a condition to the Telephone column for all the cells that are inside it. The condition states that the only valid characters a user can input are numbers and dashes.

If validation fails, the cell becomes red. This alerts the user that the telephone number hasn’t been typed correctly.

Mixed values – Regex

If you would like the validator to accept values that are mixed (numbers, letters and special chars), you can use regex. It takes fewer lines of code than doing the same using FOR loops, so it’s definitely an option to consider.

At first glance, regex may seem like a strange language. After some analysis, however, you will find that it is actually a clever way to handle mixed values in spreadsheets and input elements. If you want to learn more about regex, visit the MDN Regex documentation page, which I also used to improve my knowledge about regex.

The following example accepts only nicknames that contain digits, letters and an underscore. It should mark as invalid all the cells that include other characters or spaces. Additionally, I added a condition to accept nicknames with more than 4 and less than 16 chars.

Cross-column validation

Last but not least, I would like to share an example of cross-column validation. In the example below, you see a typical store app where an employee can alter the price of an item in the store.

This demo uses getDataAtRowProp to track changes made in the checkbox column. If it returns a value of true, the corresponding Discount field will allow the user to set a valid discount for the product. However, if it returns false and there is a discount entered, the cell will turn red, indicating that a discount is not available.

If you would like to learn more about cell dependencies, I highly encourage you to check out our Expand Your App with Cell Dependencies article.

Conclusion

As mentioned at the end of the first article, the validation process runs asynchronously. This means that even if a large amount of data is already being processed, users can add new content and they’ll still be able to work with the table.

I hope that these examples were helpful. If you want to find out more, check out our data validation tutorial.