Select School Years based on Date Range

[jennm]

I am trying to build an automation or formula for this scenario:

1. Student #1 has been enrolled in school from the dates 7/1/2018-6/30/2023. I have a field in this table (Students table) for Enrollment date (7/1/2018) and graduation date (6/30/2023). I need the field " School Years" to populate with the respective school years this student was in school for. Ex: SY 2018-2019, 2019-2020, 2020-2021, 2021-2022, 2022-2023 - this is a multiselect field. This will be unique for every student, as their enrollment and graduation dates are varying...

I have build a table named School Years and have the start and end date for each school year as a record. I would love this to be automated so that every time we have a new student and their graduation date changes, it will update what school years they were here for.

This seemed so easy to do in my head and I have tried every way to get this to work, but I cannot seem to figure out how to have ALL school years they were enrolled for to show up in a single field as separate school years. I've attached a photo of what I hope it to look like. Please help

[Manuel_Tanco]

Hey there!
So I came up with a suggestion that might help out the situation you're describing.

So taking into account the fields in your screenshot, I would suggest adding two more fields (you can hide them from the table & views). These two formula fields take the years from the two dates (Enrollment & Graduation). You can use these formulas applied with your field names

IF({Entry Date},YEAR({Entry Date})))  <-- For Enrollment Field
IF({Graduation Date},YEAR({Graduation Date})) <-- For Graduation Date

For reference I will be calling this table Years

Now, the way I approached this is by creating another table that will only contain these SY Year Ranges composed of the primary field and two additional fields. Basically the Start Year will be exactly that, the start of your range. The End Year will basically a formula of

{Start Year} +1

The  primary field (SY Range in the image) is another formula field that will match your options in your original table. It uses the following formula to copy the values (Hopefully I wrote them correctly based of your screenshot, this should be double checked)

"SY "&{Start Year}&" - "&{End Year}

For reference I will be calling this table SY Dates

 Now when the Automation is pretty simple.

1. Trigger: When a record matches a condition from Years table: Entry Date is not empty and Graduation Date is not Empty
2. Find Records in SY Dates based on condition: Entry Date is >= {Years Entry Date} and Graduation Date is =< {Years Graduation Date}
3. Update Record in Years: For your SY Multiselect field, select the SY Dates primary field

Functional Example: Sample 

Let me know if this was useful or if I can be of more help