Base design: same programs, different years - junction table needed?
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:
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!
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.
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:
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!!
Hello Sam,
You could use a formula field:
Where the formula for "Program label" is:
Regards,
Pascal
