Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Base design: same programs, different years - junction table needed?

Topic Labels: Base design
247 1
cancel
Showing results for 
Search instead for 
Did you mean: 
sam-lsf-lst
5 - Automation Enthusiast
5 - Automation Enthusiast

I have been staring at this too long and thinking myself into circles, need some fresh eyes! 

I have a base set up with 4 linked tables: schools, which are then linked to 3 program tables for SFS registrations, YF events, and Grants (2 sub-programs, AP grants and YF grants). The overall structure: 

 

samlsflst_0-1724160973253.png

Each time a school participates in a program, a new record is created in the program table and linked to that school. A unique record name is generated indicating the program, year, and a unique id # (e.g. SFS-23-24-001). 

All of this is working great - in my schools table I have nice linked fields for each program where I can see what a school is up to. Awesome! Love it! 

samlsflst_1-1724161107960.png

Where I'm getting myself confused is zooming out a bit on tracking the programs across years. Right now in each program table I have fields for program (SFS, AP, YF) and for year (23-24, 24-25). But these fields aren't linked to each other, they're just separate fields with the same options. 

samlsflst_2-1724161207568.png

samlsflst_3-1724161215713.png

I want to figure out how to connect these fields. The end goal is to have my schools table be able to show a sort of "program history" with the program + years, like on the far right here: 

samlsflst_4-1724161642004.png

I'm guessing I need to create some kind of junction table for programs. I can see how I could list the programs as records and link those to my 3 program tables. But not sure how to connect the programs to their years?

I guess one workaround might be to make each program year an individual program record, e.g. 

- AP 23-24

- AP 24-25

- SFS 23-24

- SFS 24-25

but is there a better or more elegant way? 

Help so appreciated!! 

 

 

1 Reply 1

Hello Sam,

You could use a formula field:

Pascal_Gallais_0-1724163891023.png

Where the formula for "Program label" is:

Pascal_Gallais_1-1724163937868.png

Regards,

Pascal