I'm working on a project where I have a main list called "Requests". It includes a "Course" column (pre-populated) and a "School" single-value choice column. I want to filter the options in the School column so that only schools offering the stated Course are shown.
I have about 65 courses and 20 schools - because there are so many combinations, I need to store that data in another "Reference" list.
The examples I've been able to find to address this won't work for me. Most are one of two approaches:
- Basic city/country model, where each city is only associated with a single country. In my case, if the courses are "cities" and the schools are "countries", a city could be associated with any number of countries and the many:1 approach won't work.
- Using Power Apps to adjust the form and populate the options in the choice field. I need my users to be able to work in Grid mode, but more importantly I don't have access to Power Apps (organizational restriction).
So, is there a way using only SharePoint Online and Power Automate to either:
- Restrict what appears in the drop-down options to only display Schools associated with the specified Course in my secondary list
- Use column formatting to check the secondary list so that if a user does select a school that isn't associated with that course there is at least a way to flag it after they choose it
Because the list of schools associated with a course could be quite long, it won't always fit in a single-line text field, and I can't use a multi-line text field for a lookup, which adds to the complexity.
My final option would be to not automate either the options in the choice School field or any formatting related to it, but just to use Flow to check my main list for the specific course, then pull the names of schools from my secondary list associated with that course and push that list in to a multi-line text field in my main list. No automation, but at least the user would have that reference available to manually see which schools they should be choosing from.