SharePoint Online - How to dynamically populate options in choice column using values from other columns?

Question

I have a list in SharePoint online to manage transfer requests for students to move from one school to another. In my list, there are three columns (Area School, Alternate 1, Alternate 2) with the names of three schools the student is interested in transferring to. These columns are populated when the list item is created, based on a MS Form through Flow.

The column I need help with is Approved School. This column is used after the list item has been created so the transfer office can show which of the three schools the transfer has been approved for. It's a Choice column (drop-down), and I would like the options displayed be the values from Area School, Alternate 1, and Alternate 2 for each list item. To do that, I need to find a way to dynamically populate the options for each list item by pulling in the values from the Area School, Alternate 1, and Alternate 2 columns.

My exhaustive list of schools in the Form may be: Pine Elementary, Maple Elementary, Oak Elementary, Spruce Middle, and Birch Middle, so for any given list item, any of these schools could be the values for the Area School, Alternate 1, or Alternate 2 columns.

This shows a couple of examples of what the options should be for the Approved School column, based on the values that are in the list for Area School, Alternate 1, and Alternate 2:

List item 1: Pine Elementary (Area School), Maple Elementary (Alternate 1), Oak Elementary (Alternate 2)

• Approved School options should be Pine Elementary, Maple Elementary, Oak Elementary

List item 2: Pine Elementary (Area School), Spruce Middle (Alternate 1), Birch Middle (Alternate 2)

• Approved School options should be Pine Elementary, Spruce Middle, Birch Middle

I've seen lots of requests for "static" filtered options (if column A is "sports" then column B should only give the options of "football", "hockey", and "soccer"), but haven't found a way to dynamically populate the choice options with the actual values from several other columns.

The one workaround that I've considered is to have an additional step: the options for Approved School could be static and force the user to choose between "Area School", "Alternate 1" and "Alternate 2" (so they are choosing which column the approved school shows up in, instead of the actual school itself), and then use a calculated column to display the end result... if the user chooses "Area School", the calculated column looks at the value in the "Area School" column and displays it. It's just not as elegant as allowing them to choose the actual value in the drop-down for Approved School.

Any ideas?

Answer 1

If you want to dynamically change the choice option, you will need to use Rest Api with the action send http request

Here is a demo I made to update the choice column, which works. Remember to change the Uri with your field name and the body. Results is the collection holding your new choice value

{
 "__metadata":{
"type":"SP.FieldChoice"
},
"Choices":{
"__metadata":{
"type":"Collection(Edm.String)"
},
"results":["A","B","C"]
}
}