I’m new to Airtable and we’re setting up a database for our accreditation system. I have a table for Institutions and another table for Programs at said institutions. I want to link to the programs table from a field in the institutions table, which I have no problem doing with out-of-the-box functionality. However, I’m struggling to figure out how best to set this up so it doesn’t show redundant information.
Some of our institutions have only one program. Others have several programs. For each program in our system, we have several individual records (one for each step in our accreditation process) in order to store not only the program’s current status but also historical data on their prior statuses.
The problem is that since each program has multiple records, when linking to them in the institutions table, each program is linked multiple times (one for each status record). Please see screenshots below for illustration (Institution names blocked out due to confidentiality, but black and red redactions are two different institutions; the black one has two programs, the red one has one program).
Is there anything I can do via formula (or some easier common sense method I’m failing to think of) to get the Institutions table to only link to each GROUPING of program records rather than each individual record itself so that each program only appears once in the linked field of the Institutions table?
If it’s helpful to know, I’m currently using a formula on the Programs primary field that essentially pulls in Institution name + degree type + program name + status.