Filter or populate choice/lookup column based on another list without Power Apps? Or format column based on another list?

Question

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:

1. 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.
2. 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:

1. Restrict what appears in the drop-down options to only display Schools associated with the specified Course in my secondary list
2. 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.

Any ideas?

Answer 1

Well, I wasn't able to find a way to populate a choice field with data from another table without using Power Apps. What I ended up doing was populating a multi-line text field with the valid schools, so that when the user is selecting from the full set of schools in the School drop-down, they can have a column right there displaying the valid schools, making it easy for them to know which ones they should or shouldn't use.

I won't post all the details here, but I also built another flow to monitor the Course column to update the list of schools if the Course changes.

Flow to Display Valid Schools for New Items

Here's the flow. When a new item is added to the list I retrieve it to see what Course it's for. I initialize an array variable, then populate it with the schools associated with the Course from the RefSchool field in my Reference list. I use a first Compose to push all of the array values into a string, then a second Compose to get rid of the extra characters and words, and add a space after each comma. Finally I update the item in the multi-line text field in my Requests list with the list of valid schools.

Flow Details

Here's the filter I use for Get Items so I'm only pulling items where the RefCourse field matches my Course field in the main list:

This is the expression I use to merge all of the array values into a single entity:

union(variables('VSchoolsArray'),variables('VSchoolsArray'))

This is how they look in the list, though - with characters I don't need. In addition, to keep the list more compact, I want to get rid of the "High School" text in each school name.

This is the expression I use in the second compose block to get rid of the extra characters and "High School" phrase, and to add a space after the comma separating each school:

replace(replace(replace(replace(replace(string(outputs('Compose')), '[',''), ']', ''), '"', ''), ' High School', ''), ',', ', ')

This is how the field looks after:

In my Update Item block, then, I just use the output from my second compose to populate my multi-line text column: