Data validation

@Gustavo_Lopez, dependencies are totally a type of data validation. Only certain values are valid, but that list of valid values changes based on another field. It’s just a more advanced form of dynamic validation.

I’m +1 for needing these dependencies though. I just tried to make by first “base” and failed because my use cases all require one field’s allowed values being conditional based on another field. You’ll never be a competitor to Excel or Filemaker without validation. “Single select” just doesn’t cut it.

3 Likes

+1 for data validation!

2 Likes

I am evaluating Airtable for a project at my company and just discovered that it has no field validation on forms (or elsewhere). Without field validation, any form or database software is unusable if the quality if the data being input is important (when is it not important?).

4 Likes

+100 for data validation (please, please, please)

I was completely bamboozled when i discovered that airtable does not validate form data! especially the primitives like email and url!

it even makes it sound like it does when you click “customise field type”

Absolutely ridiculous! I could write these in a few minutes using redux forms.

In this example it is using a very standard regex for emails:

const email = value =>
  value && !/^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$/i.test(value) ?
  'Invalid email address' : undefined

If you don’t understand js es2015 dont worry - this is basically saying:

variable email = the result of a function that takes "value" as input 
tests it is not 'null' && then tests that it passes the regex (!/^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$/i). 
if it fails the function returns 'Invalid email address' 
if it passes it returns undefined 
(which makes sence when we are talking about validation errors
ie it passing means that there are no/undefined number of validation errors)

Now if I can write that into a comment in a couple of minutes and explain it in english for any airtable user to understand there is absolutely no reason why the product owners at Airtable cant prioritise this as a quick feature in the next sprint #strongsuggestion

6 Likes

Is there any way to accomplish field validation in Airtable?! This seems like a basic feature for a database.

1 Like

How do I set up data validation on airtable fields? (Has this issue been addressed yet?)

Also, a more basic version of this question:

Why are email address not being validated in the form view, if you specifically select the “email” field. How do I validate email addresses?

Thanks.

1 Like

That standard regex is neither standard, nor will it work very well, as it accepts lots of invalid addresses while rejectings valid domains (TLDs have not been limited to 4 letters for what feels like ages, for one; even before vanity TLDs, the limit was 6). Before anybody starts copying this for, I dont know, whatever, please do read How to Find or Validate an Email Address.

Don’t get me wrong; I would love to see data validation in Airtable, but it is a highly complex topic and getting it right even for seemingly simple use cases is hard (see date parsing libraries et al); let’s cut Airtable some slack here.

2 Likes

… and to expand on the other issue at stake here, i.e. user defined validation of data, while a feature I’d dearly love, I suspect the conumdrum ist that Airtable can either

  1. allow validation through its formula language only, which, barring a serious expansion / overhaul of the formula system, would probably be considered too primitive and coarse grained for many purposes (there isn’t even a regular expression match available, never mind real array searches), or

  2. set up a whole language runtime on top of the current Airtable stack to allow for fully programmable validations (e.g. in JavaScript), which opens a can of worms when it comes to security and performance.

1 Like

Realize that this is a hard feature to implement (for all reasons summarized above). Would still love to see it if possible. After all, a lot of what Airtable does already was hard, and that’s part of why we love it - plenty of inadequate competition handled the easy challenges well.

+1 to this request

1 Like

Here are some functions that I wrote for parsing URLs. Hopefully they are of use to those of you who need to perform some validation and parsing.


Remove Punctuation

The following characters are removed with this formula:

*Single quotes
*Double quotes
*Hyphens
*Periods
*Comas
*Underscores
*Slashes
*Ampersands

Please note that this formula does NOT remove spaces.

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(<target_field>), "'", ""), ".", ""), "&", "and"), "-", " "), "/ ", ""), '"', ''), ",", ""), "\\", ""), '”', ''), '“', ''), "_", " ")

Normalize Concatenated Human Names

Normalize human names and titles from concatenated first and last names

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(SUBSTITUTE(name_first, "'", ""), "_", SUBSTITUTE(name_last, "'", "")), ".", ""), "&", "and"), "-", ""), "/ ", ""), " ", "_")), '"', ''), "\\", ""), ",", ""), '”', ''), '“', '')

Encode Letters as Numbers

IF(LEFT(UPPER(title), 1) = "A", "01", IF(LEFT(UPPER(title), 1) = "B", "02", IF(LEFT(UPPER(title), 1) = "C", "03", IF(LEFT(UPPER(title), 1) = "D", "04", IF(LEFT(UPPER(title), 1) = "E", "05", IF(LEFT(UPPER(title), 1) = "F", "06", IF(LEFT(UPPER(title), 1) = "G", "07", IF(LEFT(UPPER(title), 1) = "H", "08", IF(LEFT(UPPER(title), 1) = "I", "09", IF(LEFT(UPPER(title), 1) = "J", "10", IF(LEFT(UPPER(title), 1) = "K", "11", IF(LEFT(UPPER(title), 1) = "L", "12", IF(LEFT(UPPER(title), 1) = "M", "13", IF(LEFT(UPPER(title), 1) = "N", "14", IF(LEFT(UPPER(title), 1) = "O", "15", IF(LEFT(UPPER(title), 1) = "P", "16", IF(LEFT(UPPER(title), 1) = "Q", "17", IF(LEFT(UPPER(title), 1) = "R", "18", IF(LEFT(UPPER(title), 1) = "S", "19", IF(LEFT(UPPER(title), 1) = "T", "20", IF(LEFT(UPPER(title), 1) = "U", "21", IF(LEFT(UPPER(title), 1) = "V", "22", IF(LEFT(UPPER(title), 1) = "W", "23", IF(LEFT(UPPER(title), 1) = "X", "24", IF(LEFT(UPPER(title), 1) = "Y", "25", IF(LEFT(UPPER(title), 1) = "Z", "26", "27"))))))))))))))))))))))))))

General URL Cleaning

*Remove the protocols http:// and https://
*Remove “www” sub domains
*Remove all sup pages and subsequent query strings

IF(FIND("/", IF(LEFT(IF(LEFT(url, 4)="http", IF(LEFT(url, 5)="https", SUBSTITUTE(url, "https://", "", 1), SUBSTITUTE(url, "http://", "", 1)), ""), 4)="www.", SUBSTITUTE(IF(LEFT(url, 4)="http", IF(LEFT(url, 5)="https", SUBSTITUTE(url, "https://", "", 1), SUBSTITUTE(url, "http://", "", 1)), ""),"www.", ""),IF(LEFT(url, 4)="http", IF(LEFT(url, 5)="https", SUBSTITUTE(url, "https://", "", 1), SUBSTITUTE(url, "http://", "", 1)), "")))=0, IF(LEFT(IF(LEFT(url, 4)="http", IF(LEFT(url, 5)="https", SUBSTITUTE(url, "https://", "", 1), SUBSTITUTE(url, "http://", "", 1)), ""), 4)="www.", SUBSTITUTE(IF(LEFT(url, 4)="http", IF(LEFT(url, 5)="https", SUBSTITUTE(url, "https://", "", 1), SUBSTITUTE(url, "http://", "", 1)), ""),"www.", ""),IF(LEFT(url, 4)="http", IF(LEFT(url, 5)="https", SUBSTITUTE(url, "https://", "", 1), SUBSTITUTE(url, "http://", "", 1)), "")), LEFT(IF(LEFT(IF(LEFT(url, 4)="http", IF(LEFT(url, 5)="https", SUBSTITUTE(url, "https://", "", 1), SUBSTITUTE(url, "http://", "", 1)), ""), 4)="www.", SUBSTITUTE(IF(LEFT(url, 4)="http", IF(LEFT(url, 5)="https", SUBSTITUTE(url, "https://", "", 1), SUBSTITUTE(url, "http://", "", 1)), ""),"www.", ""),IF(LEFT(url, 4)="http", IF(LEFT(url, 5)="https", SUBSTITUTE(url, "https://", "", 1), SUBSTITUTE(url, "http://", "", 1)), "")), FIND("/", IF(LEFT(IF(LEFT(url, 4)="http", IF(LEFT(url, 5)="https", SUBSTITUTE(url, "https://", "", 1), SUBSTITUTE(url, "http://", "", 1)), ""), 4)="www.", SUBSTITUTE(IF(LEFT(url, 4)="http", IF(LEFT(url, 5)="https", SUBSTITUTE(url, "https://", "", 1), SUBSTITUTE(url, "http://", "", 1)), ""),"www.", ""),IF(LEFT(url, 4)="http", IF(LEFT(url, 5)="https", SUBSTITUTE(url, "https://", "", 1), SUBSTITUTE(url, "http://", "", 1)), "")))-1))

URL Cleaning and Subsequent TLD Extraction

Source code: pastebin[dot]com/raw/c9WmR6vJ
(the actual code is too long to include here)

5 Likes

These are great!

While not, strictly speaking, pre-made data tables, I’ve added a link to your reply to @Jeremy_Oglesby’s wikified post collecting handy data and routines.

Thanks for posting them!

2 Likes

I have some data sets you can grab as well…

WorldDB

==> https://resonova.com/research/worlddb

This database contains every country name, ISO alpha2 code, ISO alpha3 code, national flag, currency name, currency ticker, national language, time zone, international dialing code, national capital, GPS grid location, and alternative and native language spelling of every country in the world.

It also includes every US state, state flag, state seal, state map, image of the states largest urban areas, image of every state’s rural area, capital city of every state, and a list of the largest largest cities in the US. … among other things.


DataDB

==> https://resonova.com/research/datadb

Basically a smaller version of Crunchbase

1 Like

+1 for the cascading/dependency type of data validation, particularly - I was looking into switching from Excel, but being able to limit selections to certain subcategories based on the category in another field is too important for my data :frowning:

1 Like

+1 for data validation. Its a huge feature missing. Everything else is awesome!

6 Likes

+1 for validating email address validation. The form is misleading because why select “email” as type unless we are validating email addresses? We have been using Airtable for forms and it’s a lot of extra work to go back at the end and correct email addresses that aren’t real email addresses. Bummed this doesn’t exist yet.

+1 for data validation

@willinspire Is it possible to get a copy of your “WorldDB” data set? Looking to use it to input and track city and state on a record.

Thank! Very impressive DB.

Hey Jay,

Ideally, this should be built out for linked fields not drop down selects. Ideally, you would conditionally format the field to filter for values which match a criteria in your destination table.

The dynamic sub text would read something along the lines of “Use variables from other fields within destination table”.

PLEASE be sure to send an email to support@airtable.com with your suggestions.

image

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.

validate-inputs-3.0

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.

:slight_smile:

3 Likes