Advanced rules for data input and validation

[Davide_Cencini]

Hi, I discovered Airtable just a few weeks ago and instantly fell in love with this application. It’s so cool! I am still new to it, but I’m learning so much.
However, as a former Access user, I’m surprised it doesn’t feature almost any advanced rules for data input and validation in fields. I’m even more surprised that so very few people are asking for them.
Data validation is important. For a solid database, it’s a core feature. Say I want to record a customer’s IBAN for money transfers. An IBAN always has exactly 27 characters; it’s not all right if someone by mistake inputs 25 or 28 characters or puts spaces into it. I need to set the IBAN field’s lenght to 27 characters and request that you fill out those 27 characters, not one less, not one more!
Same goes for zip/postal codes, or tax codes. Validation rules are needed for such fields. A lot of stuff can get messed up if this kind of data is not recorded correctly, such as payments, shipments, fees. It’s a serious matter!
Phone numbers and email fields should be validated, too, because they should not accept invalid characters.
I should be able to set Date fields to accept only certain ranges of data. Such as: “you cannot input a delivery date that is before today”.
I can’t even find such a thing as setting a field to “required”.
Even though the application is fantastic, these shortcomings are a major kickback for Airtable. Please consider introducing these features, I’m sure many users will be grateful!
As I said, I am new to the application so pardon me if I’ve said something wrong or I missed out on issues that have already been addressed.
Love ️

[Alexey_Gusev]

Hi,

I may suggest to add field with REGEX. And then use 2 automations ‘when record matches conditions’.
First removes all ‘not_ok’ (btw there is no action 'delete record ', but it’s quite easy to workaround)
Second - perform your usual action with ‘OK’

Regex is from here (and some useful validation checks also):
https://community.airtable.com/t/new-regex-formula-functions/36818/2

[Ludom_Initiativ]

Hi,
The tricky part here - that formulas, regex or not, cannot solve in any way - is form validations: every single solution I know that include forms (many of which are open source) provides the ability to validate the input, through a set of rules and/or a regular expression.
One would for instance at the very least expect that Airtable validates that an email address input an email field be an email and not include backspace or full comments, and that an URL really is a web address.

This creates a lot of work and nightmares for Airtable admins / developers. I have a new data quality problem almost every single week because of this on one project or the other.

I understand that it could create some issues regarding the adoption of the paid plans, but what about making this a Pro/Enterprise plan feature actually?

@AIRTABLE, any way you could at least tell us if it’s going to be part of the roadmap at some point or if we have to develop advanced bricks (like custom integrations with other form tools)?

Best,
Ludo

[Chef_Rude]

Yes… this exactly explains what I am struggling with.

Its data input validation in forms, not just “Oops, someone typed the wrong thing, tell me its no ok”

[Allen_Moldovan]

I would hope something within the new interface platform will solve this. My fingers are crossed for a form element which facilitates this type of validation.

[Eric_Brown]

Really loving Microsoft Power Apps

[nitin]

Script for above post:

gist.github.com

https://gist.github.com/nitincodery/36908042adf49d442fbf99cac3ddf916

airtable-validate-inputs.js

/*
Create Tables and select the one you need to input data.
Create Validations Table for each Table as TableName-Val.
Create Three Fields as Text in Validations Table as Column, Regex, Remark
Enter Column Names to Validate into Column Field in Validation Table.
Give Regex to validate and Remark to show when invalid data entered.
*/

/* Ask to input data in table whose validation table exists. */
let allTables = base.tables.filter(table => table.name.endsWith("-Val")).map(table => table.name.slice(0,-4))

This file has been truncated. show original

[Brian_Davis]

We are looking for a robust solution to record filtering, linking and validation based administrated rules.

[Alex_Whitton]

Interesting chain - perhaps in support of Airtable vs the above comments - what do you want? If you want the feature rich elastic search, input, robus fast navigation and loading and filtering - yet you want to load up the grid CSS and front end usage from your browser and increase the database calls things will slow down

As it is from my perspective this grid is incredible - the optimisation work they have done is a little mindblowing - 4 years previous was the last time I used Airtable and the changes in performance (for me) are staggering (I’ve worked with commercial web app grids for the past 4 years as background - banking data, 1 second sync with millions of rows)

For any interested and devevlopers above, i’m using Airtable to prototype and test apps with customers - in 2 weeks we’ve built an ERP based on a commercial ERP database with 600 tables - sync to Notion or Coda via API’s for docs goals, and return - the list goes on.

I’ve never found this possible in excel or other grids the lack of rigiditiy - and yet flexibility - is such a sweet spot so I wanted to share some love for Airtable

If you can write js or use databases connect up tables to sql or even large excel or power BI - and control your data in different ways via the API which is excellent

I’m trying to break Airtable right now so I’ll share again when I find the weak spots :joy: for now I’m blown away - you’ll lose speed in the above validations, for those that think this is simple I’m going to call you out - imagine 1000 steps that can’t be skipped, no short cuts, that’s what it takes to optimise like Airtable has done - 1 per week, over 10 years? In just 4 years the changes I note are very large and would take ridiculous hours and people
Gratitute to all of you fellow perfectionists - let’s see what they come back with I wouldn’t complain also if they achieved it - BUT don’t slow down the grids too much or my use cases don’t exist for Airtable any more

Simple beginners tip (I’m the beginner here): I’m having success controlling validation by simple design structure - formulas as 1sts columns so they don’t overwrite in backlinks as an example - sync tables where the rule states if delete or filter status is different look up changes also

This was the example that brought me back to Airtable - the setup here and integrations opened my mind to Airtable being faster (than my previous experience) lots of smart design here to make this work, I was inspired:

[Allen_Moldovan]

Looks like you broke it, great job

[Alex_Whitton]

Yes! Just received a notification also :grinning: