Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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

Topic Labels: Base design
419 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