Need help with linked records and possible formulas

[Megan_Woolston]

Hi there,

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.

Thank you!

[AlliAlosa]

You can’t limit what’s displayed in the linked record field, but you can use rollup field to only show the program name once. Add a rollup field to the [Institutions] table, pointed at the primary field on the [Programs] table, with a formula like:

ARRAYJOIN(ARRAYUNIQUE(ARRAYCOMPACT(values)))

You could probably get away with just ARRAYUNIQUE(), but I like to use all three of those aggregate functions to catch possible errors in data entry.

I hope that helps!

[Megan_Woolston]

Thank you! That’s not quite what I was hoping for (was hoping I’d be able to still have them act as linked records rather than just text) but it’s certainly good enough if that’s the only option.

Is there any way to separate the multiple programs in the rollup field by a line break within the cell rather than a comma (or at least add a line break after the comma) to make it visually easier to spot at a glance which institutions have more than one program? It’s currently reading:

“AAS Early Childhood Development-REDACTED Community College, AAT Early Childhood Education-REDACTED Community College”

and I’d prefer it to appear as:

“AAS Early Childhood Development-REDACTED Community College
AAT Early Childhood Education-REDACTED Community College”

I saw in another support thread that someone recommended using the SUBSTITUTE formula, but I can’t figure out how (or if it’s possible) to add that on to the ARRAYJOIN formula you shared.

Thanks so much!

[AlliAlosa]

Hi there @Megan_Woolston, you can definitely do that! Try changing the formula I gave you to this:

ARRAYJOIN(ARRAYUNIQUE(ARRAYCOMPACT(values)), "\n")

Hope that helps!

[Megan_Woolston]

Perfect, worked like a charm. Thanks so much for your help!