Data validation

[Adam_Reece]

I’ve just started using Airtable, and straightaway I’m wondering if it’s possible to replicate the data validation function available in Excel.

Specifically, what I’d like to do is select from a Single Select list (the field would be called Category), and in the next column/field have a Subcategory, which would give me a Single Select list with a unique list based on the selection I made in the Category field.

For example, if my Category selection was Utilities, my Subcategory selection list would include Phone, Electricity and Gas. Alternatively, if my Category selection was Travel, my Subcategory selection list would include Fuel, Public transport and Flights.

Thanks.

[willinspire]

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

[Sara_Helmers]

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

[Jason_Dainter]

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

[Mariel_Reed]

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

[Kirsti_Griffith]

+1 for data validation

[Daniel_Ordonez]

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

[Allen_Moldovan]

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.

[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:

[Quinten_De_Wild]

Nice work @nitin!

Still @airtable, you should get this on your roadmap sooner than later…

[Stella_Chung]

Hi! Does this script/app still work? If so, what is the github link? The one posted leads to a 404 error page.