Advanced list gurus, I need your help

Brian LeBlanc

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):

1. User Register.School Year
2. User Base.First Name
3. User Base.Last Name
4. User Base.Grad Year
5. User Base.User ID
6. User Register.School Level
7. Grade Level.Grade Level Description
8. User Register.Enroll Date (this is what I use to differentiate new vs. returning students)
9. Contract.Return Date
10. 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)

Jess Moxsky

@Brian LeBlanc
I know this isn't ideal, but in these cases I tend to export what I can from the Advanced List tool and then do further manipulation in Excel. You can use some pretty simple functions to drill down into the list with some COUNTIF or IF. Feel free to DM me if you have any questions should you decide to go the Excel route. Best of luck!

Brian LeBlanc

@Jess Moxsky That's probably where I'm going to land, unfortunately. Is there a way to tell a COUNTIF formula to exclude duplicates (like maybe a duplicate user ID)? That might be the easiest way to pull this off. I asked support and they did some sandboxing but couldn't figure out a way to do it either, so I think it's unfortunately beyond the capability of an advanced list filter. Which is a shame, because SQL does have that ability, it's just not available in this particular deployment of the language.

Jess Moxsky

@Brian LeBlanc
What I would give to be able to just write a query in SQL
I believe you should be able to filter unique IDs - have you worked with the query builder in Excel? I think the tools there might be really helpful in this case.

Brian LeBlanc

@Jess Moxsky You and me both…sigh. I'm data-dumping into Google Sheets daily, so I'd really like some sort of bespoke solution that just works in there without me needing to constantly babysit it. I'm wondering if there's some sort of Apps Script code that I can write that would do the trick. I'll keep digging…for now the workaround I'm using is fine, if a little bit duct-taped together.

John Ronan

@Brian LeBlanc,

I must admit that I did not re-immerse myself in your question since I first read it, but from skimming the various replies, it seems if you had complete SQL at your disposal, you could pull this off. In cases like this, I often dump the data into Access and go from there.

Jennifer Welch

@Brian LeBlanc and everyone else! I'm jumping in with a question but I think I can see what the answer will be. Is there a way to filter on Enroll Date+2 days? Without being able to edit the SQL I assume not?

John Ronan

@Jennifer Welch
I sure don't know it.