For background, I use advanced lists instead of SKY student lists to export enrollment rosters to our internal enrollment tracker so that we can view easily on a Google Sheets dashboard. I do it this way because student lists don't provide the ability to display future years, which we need for enrollment projections prior to the end of the current year so that we can see both new and returning students in one place.
With that said, up until this year I have always used the advanced list for the upcoming school year to also track re-enrollment numbers, and I have used the contract return date as a stand-in for determining how many students have completed re-enrollment. However, this year we started a new system where I am making more use of non-integrated contracts, and that is causing issues because now I have students who are re-enrolling and have two different contract type IDs (one integrated and one non-integrated), which makes them appear twice on the advanced list and double count in enrollment numbers.
To make matters worse, some students have only the non-integrated RE contract, so I need a way to essentially tell the list “count only the normal integrated contract and ignore any non-integrated one, unless the student only has a non-integrated contract, and in that case count that one”.
I have been racking my brain for days to figure out a way around this. SQL has a data call that will only display unique values, which in theory I could use with the user ID field, but the filter will only allow for numerical values. I will post my objects, display fields and filters below. Any help would be welcomed. Thanks!!
----------
Objects:
User base
> User register (outer join)
>> School year (outer join)
>> Grade level (outer join)
> User detail (inner join)
> User role (inner join)
> Contract (inner join)
Display fields (in order):
- User Register.School Year
- User Base.First Name
- User Base.Last Name
- User Base.Grad Year
- User Base.User ID
- User Register.School Level
- Grade Level.Grade Level Description
- User Register.Enroll Date (this is what I use to differentiate new vs. returning students)
- Contract.Return Date
- User Register.Repeated
Filters:
Where:
> School Year is (the next year; currently 2024-25)
> And (Contract Type ID is (New Enrollment Contract) OR (Integrated RE Contract) OR (Non-Integrated RE Contract))
Join:
> User Role.Role is (Incoming Student or Student)
> Contract.School Year is (the next year; currently 2024-25)
