EDIT: I missed a major constraint in this question (I can't use Power Apps), so the question is no longer valid for me. I'm leaving it in case it's useful to anyone else, but I've posted a new, clearer question here: Filter or populate choice/lookup column based on another list without Power Apps? Or format column based on another list?.
I'm working on a project where I have a "Course" column (pre-populated) and a "School" column that I want to filter to only display schools offering the specified course. Most of the examples I see are the city/country model, where each city is only associated with a single country. In my case, I have about 65 courses and 20 schools, and any one of those schools can offer any number of those courses. So I can't just do a 1:1 lookup.
I'll also be getting the data relative to each school, rather than each course (so, an Excel sheet for each school with the names of the courses that they offer), and I'll need to do some additional filtering later in the process to assess if they have a limited number of spots in each course.
Is my best bet to just use a single list to hold my reference data, with three columns? I'd need ReferenceSchool (to hold the school name, which will be identical to the one of the choices in my School column), ReferenceCourse (to hold the course name), and Cap (for use later on when I'm allocating students to the schools). Then, for each school, I just create as many list items as they have courses.
In PowerApps I'd then need to filter to show the ReferenceSchool where Course = ReferenceCourse. Is that the right approach? I'd rather find out how to get the data in place before I get into the PowerApps part.
Does that make sense? And anything else that I should keep in mind? Is it like SharePoint Designer used to be, where once I modify the edit form in PowerApps it won't automatically update to show new columns or changes to columns unless I manually update it every time in PowerApps, or is there a way to get PowerApps to just impact this one drop-down field?
After a bit more research, I'm wondering if I could skip power apps and, instead, use Flow to define or restrict the options available in my choice field or lookup field. Any ideas on that approach?