Auto-Matching records across tables using non-primary fields

[Jonny]

Hey. Thank you in advance or your help on connecting data between two tables. Linked records does not seem to apply here, but maybe I’m missing something.

I am using Airtable to track disciplinary data for a school. We have two tables, one to track Pink Slips, and a second to track historical Disciplinary Cases. The first table “Pink Slips”, is where new pink slips are entered, using a primary field of a sequential ID assigned by Airtable for each new pink slip. One of the fields in this table is “Student ID”. The second table, “Cases” contains a historical record of all disciplinary cases (some resulting from pink slips), akin to a court case, with all data related to the resulting actions, dates, etc… The primary field in this table is “CaseID”. All of the data in the “Cases” table is imported from a school database, and also contains a “StudentID” field. For reasons that i would rather not get into, the two tables cannot be merged into one.

As/after the admin enters new pink slips into the Pink Slips table, we would like to see if the StudentID being entered matches a StudentID from the Cases table. If it does, we would like Airtable to automatically link the past case records (sometimes multiple), to the new Pink Slip record being entered, and display those records within the Pink Slips table, in addition to a count of how many Cases appear for that Student. On top of that, we would like to be able to create an interface which summarizes/groups any new Pink Slips for StudentID X, with past disciplinary Cases related to that same StudentID. Is this possible? Thank you!

[TheTimeSavingCo]

Hi Jonny, I would suggest creating a new table called “Student IDs”, and link the “Cases” and “Pink Slips” table to it.

This should provide you with the link required, and you can use lookups, rollups and counts to do what you need, and the summarized information of pink slips and cases for each student would be found in the Student IDs table. From a data-entry perspective, there should be a minimal change

I’ve thrown something together here for you to check out

Let me know if I can help further!

[Jonny]

Interesting, thank you for going the extra mile here! So let’s say the first step is to import just the StudentID’s from the historical Case records to this new table. Then, I would import the full historical case records (which include the same StudentID’s) to the case table. Can I automatically link the historical case data, to the correct StudentID in the StudentID table? This will also be a weekly process going forward, as we have to keep the case data updated regularly.

Thanks again for your help!

[TheTimeSavingCo · Accepted solution]

Hmm, I think you could just import your historical case records and then convert the field that contains the Student IDs to a “Link to another record” field and select “create a new table”

Doing so will automatically create all the student IDs as records in a new table and will be linked as you require

I believe you could then import the pink slip records and convert the field that contains the student IDs for the pink slip records to a “Link to another field” field and select the new created table from the previous step, and everything should be automagically linked

The fact that it’s going to be a weekly process is interesting though. I believe some of airtable’s import functionality allows you to add the data as new records in an existing table, so once the tables are set up like above you should be able to just import and add the new data as records

I assume there’s some sort of privacy requirement or business need that prevents users from updating the table directly or using Airtable forms?

[Jonny]

Hey Adam, thank you so much for the rapid response today. I finally had a chance to sit down and try your suggestion and it worked! I was able to use the StudentID table to create counts, so all is well. I also tested importing new Case records, using the CSV importer, and it automatically setup the links for new records upon import. Phew.

The only thing I might run into eventually, is that I have 20,000 records in the Case Table, and now another 20,000 StudentID table, which puts me close to the 50K record limit for the pro account. I guess we’ll cross that bridge when we come to it, and hope that AT changes their pricing by then! Thanks again for your help on this. It saved me a TON of time!

[TheTimeSavingCo]

Ahh, I’m glad I was able to help!