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.

[nitin]

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

[Alexey_Gusev]

checking conditional fields, I had an idea
It’s not a true ‘data validation’, but conditional form fields can be used for a basic check ‘contains/does not contain’. You may add required single-select conditional field with empty options list, so it can’t be filled and form can’t be submitted when the field is active

[Leana_Siochi]

Thank you @Alexey_Gusev !!! This was super helpful!

[Tobias_LGKR]

+1

Data validation or dependencies, however you like to call it would be helpful with long lists to choose from.

[Luke_Castle]

willinspire:

se 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,

Can you make this shareable @willinspire please. :pray:t4:

[QuantumStep]

Hi everyone,

Great insights shared here about the need for more robust data validation within Airtable. While Airtable's built-in features are limited in this regard, the DataGuard extension might help fill some of these gaps.

Dependent Dropdowns (Cascading Selections):

For those looking to create dropdowns where one field's choices depend on another (e.g., Category and Subcategory), while DataGuard can't create true cascading dropdowns, it can still help enforce integrity between fields. For instance, you can set required fields or ensure valid entries in specific cases, preventing inconsistent categories and subcategories.

Prevent Duplicates:

Many of you mentioned the issue with duplicate values. DataGuard allows you to mark fields as Unique, ensuring no duplicate records are entered, whether it’s for text, numbers, email addresses, or URLs.

Validating Emails, Phone Numbers, and URLs:

If you're concerned about invalid email addresses or phone numbers, DataGuard supports Regex patterns and built-in validation for field types like Email and Phone Number. This can enforce correct formats and prevent invalid data from being saved in those fields.

Required Fields and Maximum/Minimum Limits:

For fields that must not be left blank, DataGuard includes a Required rule that can be applied to almost any field type. Additionally, many fields (e.g., text, numbers, durations, etc.) can have minimum and maximum values, lengths, or ranges set, ensuring your data meets specific criteria.

Workflow and Date/Time Constraints:

For workflows that depend on certain conditions—like preventing an action unless a field is valid—you can use DataGuard to enforce many types of rules. For example, you can set Earliest and Latest Dates/DateTimes to ensure that time entries or deadlines respect your desired range.

Overall Data Entry Control:

DataGuard handles validation across a wide variety of data types, from text to dates to numeric values. Whether the issue is ensuring uniqueness, enforcing required fields, or validating input formats with regex, this extension can prevent many of the data entry issues you've mentioned.