Preventing duplicate student data across multiple bases in Airtable

[RSk]

Hi everyone,

I'm using Airtable as a database for my microlearning platform, which delivers courses over messaging channels like WhatsApp. Each base in Airtable corresponds to a different organization, and within each base, there is a "Students" table to store information about the students who enroll in the course.

To avoid duplication of student data, I need to ensure that each student is only entered once across all the bases. For example, if a student named John is enrolled in both Standford Course 1 (in the Standford University base) and Harvard Course 2 (in the Harvard University base), he will receive overlapping messages on WhatsApp for both courses.

To prevent this, I want to check for duplicate student entries across all the "Students" tables in my different bases. However, I'm not sure how to do this in Airtable. Is there a way to search for duplicate values across multiple bases in Airtable, specifically within the "Students" table?

Any guidance or suggestions would be much appreciated! Thank you.

[JLindem]

One way to eliminate duplicates would be through a script, such as this one. However, scripts and automations only work within bases, not across them.

The best workaround I can think of—to accomplish what you're trying—is to create a view in each student table that lists their name and email (for example) in each of the bases. Then, create a new base. In this base, sync all the student tables (with the view you created earlier) to the same table in this new base. Then, you will have a single source of truth of all the students, and an overview over all the student tables. 

When you add multiple sources to a synced table, you can see the source table names in a separate column. This can be useful for identifying which base the duplicate is from. In this synced table, you can now use the script linked earlier, or a search extension, to find duplicates. I think this will accomplish what you need. 

Some linked documentation:
Multi Source Syncing 

Airtable Sync Advanced Options 

[TheTimeSavingCo]

After you've implemented JLindem's solution of creating the synced table, you could also attempt to create a unique list of students by:
1. Creating a new table called "Unique Students" or whatever
2. Creating a linked field between the new table and the "Unique Students" table
3. Having an automation that will, when a new record gets created in "Unique Students" pastes the unique identifier of the student (e.g. their email) into the linked field

This would result in a table where each record would represent one student, and you could use this table to send out WhatsApp messages without worrying about sending duplicates

[RSk]

Thank you so much for the replies and solution. I will implement these and get back to you.

Appreciate it!

Thank you.