Advanced rules for data input and validation

+1 Data validation for drop down lists.

+1 Data validation for drop down lists and multi select lists at the very least

Airtable could really learn a lot from their competition. Check out the screenshot below to see just a few of the validation options in Apple’s FileMaker. And these are just a few of the validation options that are available to us in FileMaker… you can actually validate every single keystroke, every single mouse click, and so much more.

AIRTABLE … are you listening? If you need some programmers to do this I can lend you some of my team. They could program this, test and implement in about 30 days. This request started April 2018 and is one of your largest and most active discussions in Product Suggestions – let’s get going on this!

@Brad_Laney That would be awesome. I would also be willing to lend a hand with testing and feedback, though I’m short on dev time at the moment :). The interface @ScottWorld gave as an example is a wonderful start.

Validation is one of the key features air table is missing that makes me hesitant to use it with a team at-large.

I totally agree this this can cause teams to have hesitancy!

If you want to use Apple’s FileMaker instead of Airtable, FileMaker is quite possibly the most advanced consumer-friendly database-building & app-building product on the market. There’s pretty much nothing it CAN’T do.

In addition to being an expert Airtable consultant, I am also a Certified FileMaker Developer of 25+ years. So if you need to hire a developer to help you out, please feel free to reach out to me through my website at scottworld.com.

I think the technical limitation AirTable has with validation stems from the fact that unlike typical relational databases that require Insert statements and transactions, with AirTable records are added and updated in real time. There is no submit or save event to trigger the validation on.
By the time you are entering data into the cells the record has already been created.
Form views at least have blur events on every field that can trigger validation, but in grid view, you can paste drag and otherwise enter data via automations that would break down with restrictive validations.

However I believe there would still be tremendous value in being able to set rules at the column level and then being able to track the “validity” of a cell.
For example, highlighting cells that are invalid, creating view that show or hide records containing invalid cells, as well as adding a function like “is_valid” to formulas and scripts and at the very least, restrict entering bad data in any records expanded view

This would allow AirTable to remain flexible with it’s data input methods but allow users to track and thus maintain data integrity.

Well, a super ugly yet affective hack here is to add a separate formula field with a bunch of IF statements to cover all the options you expect in a certain field. If those conditions are not met, then you can trigger any number of things to happen as a result. (yes…this is ugly, yet it might get people out of a jam)

You can also setup a “When record enters a view” trigger to set an automation for outliers to the expected values in a field: When record enters a view trigger – Airtable Support.

Mehh, i would rather have more marketing work flow guides. an airhorn field type would also be nice.

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

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:

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

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.

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:

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.

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.

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?

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

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

+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.

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

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

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”

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.

Really loving Microsoft Power Apps