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 ️

[Jon_Stephenson]

AirTable - You have recently received new funding so how about addressing the lack of data validation (please!)

[Josiah_Moser1]

Yeah this is absolutely a deal-breaker for me. Without data validation it won’t even do the basic tasks I want it to.

Is it so hard to have:
• Data tables with relationships
• Input from the public
• Data Validation

I can’t get all three for less than something like $500/mo without coding it all myself. Guess I’m learning to code now. :frowning:

[David_Spitz]

Agree with all of this… likely a deal breaker for me as well! Wah!

[nitin]

Hi guys, I’ve made a script to give us validations to limited set of fields. It runs as an app and ask input for the table for which you provide validations.

Airtable

Validate Inputs - Airtable Universe

This is a demo base used to test Validate Inputs Script App. ​ Copy this code to the scripting code and run....

Check out this Validate Inputs Base and let me know if you have any doubts.

Process is really simple, just create your normal table and another table with name tableName-Val having 3 Text Fields: Column, Regex, Remark.

Enter those coloumn names under Column and their respective validation regex and remark. Try inputting data via app and it will only add data which is valid and keep on asking new data if its invalid.

Let me know if there’s something I can improve it into.

Run the app and select table where you need to add validate data using your custom validations.

Currently these fields can have validations because they can be entered via a text box inside scripting app while other fields cannot be:

1. Single Line Text
2. Email
3. URL
4. Phone Number
5. Number
6. Percent
7. Currency
8. Rating
9. Duration
10. Date
11. Date Time
12. Checkbox

(the regex inside Validate Inputs base aren’t perfect, please use your custom regex for accurate validations)

So a table having not-supported validation columns like Single Select or Attachments or Multiple Select will be ignored and skipped by the script and rest of the fields will be asked to be entered.

Here’s the script link which will be updated as any improvement comes up:
https://gist.github.com/nitincodery/e64581d6988a34a0aadffd55831914ae

Copy above code to the Scripting Dashboard in the Validate Inputs Base, activate Scripting App and replace all the code with above code.

This is currently very limited use case of entering data manually to the table case, there are other use cases like forms but that has to be implemented by airtable side, there’s one use case imports on which it can be modified into and taking two CSV files one of data and one of validations and import only valid values and rest into another table as rejected values. If the second use case is required or useful, I’ll make that one too, or any other use case which I am unknown to, contact me I’ll come up with something for sure.

:slightly_smiling_face:

[Blaise_Meyer]

Hi Nitin! You are a genius. We love your ideas and would love to brainstorm with you. Please let me know if you’re available for a quick brainstorm sesh.

[Debbie_Block]

Looking forward to trying it out. THANK YOU!!

Debbie Block

Board Member/ Technology & Media Director
Sweet Dream Makers, Inc.
55 NE 5th Ave., Suite 400
Boca Raton, FL 33432
(561) 271-8058
www.SweetDreamMakers.org
“A bed for every child is a dream come true"

CONNECT WITH US:
Facebook: www.facebook.com/SweetDreamMakers
Instagram: www.Instagram.com/SweetDreamMakers
Twitter: www.twitter.com/SweetDreamsFL

Sweet Dream Makers is a 501(c)(3) nonprofit organization whose mission is to provide beds and essential furniture to families in need.

[Alexey_Gusev]

Hi,

I don’t receive user input needs valifdation, but at some point when i had to modify data passed by automation, i just insert scripting step in the middle. Why don’t use it to validate input?

[nitin]

Thanks, I am up to discuss about more possibilities we can do in this direction.

[Ambroise_Dhenai]

2022 almost and yet not a single move in this direction.

[Chef_Rude]

+1 I desperately want this too.

We have a form we are requesting input for, and one of the field types is “Email”, I assume it would do some kind of check. But it doesnt. I’ve had people type in “NA” and “…” and other nonsense to get past the “Required” field, and get away with it. Which is of course also throwing my automations with sending emails for a loop.

I mean, at the very least, email fields should have a required “something@something.something” check… then of course people will just type a string like that to get past it… ugh. Annoying.