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!