Skip to main content

Preventing duplicate student data across multiple bases in Airtable

  • April 11, 2023
  • 3 replies
  • 150 views
R
Forum|alt.badge.img+6

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.

This topic has been closed for replies.

3 replies

J
Forum|alt.badge.img+7
  • JLindem
  • Inspiring
  • April 11, 2023

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
    Forum|alt.badge.img+31

    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 


    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

    Adam, Airtable Consultant - thetimesaving.company
      R
      Forum|alt.badge.img+6
      • RSk
      • Author
      • Inspiring
      • April 13, 2023

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

      Appreciate it!

      Thank you.

      Terms & ConditionsAccessibility statement