1

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?

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

enter image description here

{
 "__metadata":{
"type":"SP.FieldChoice"
},
"Choices":{
"__metadata":{
"type":"Collection(Edm.String)"
},
"results":["A","B","C"]
}
}
7
  • Thanks - so the choices that will appear are your results (the hard-coded A, B, C)? If so, I need to dynamically pull the values from my three columns (Area School, Alternate 1, Alternate 2). To do that, do I just need to intialize variables and populate them before this step, and then call them in this step? Or can I use the "Add dynamic content" to let Flow pull them in for me directly? Then I would call it when an item is created to get the initial choices, and I guess set up a trigger if any of those 3 fields are modified to adjust accordingly. Is that right? – Darryl Jan 22 at 16:21
  • @Darryl. Yes the hard-coded ABC are the choices. You may need to test with the columns(Area School, Alternate 1, Alternate 2) to check if you need to create variables to get the text string or dynamic content will work. For triggers, as you are modifying the item with the flow, so I will suggest you to use a trigger when item is created/modified and set a trigger condition to avoid infinite loop. – Jerry_MSFT Jan 25 at 1:51
  • Thanks for the follow-up, Jerry. Do you have a good resource for how to avoid the infinite loop? I've searched and read a fair bit, but still haven't been able to fully get my head around this and would appreciate any suggestion you have. I'll try to see how I can best call the three "reference" columns and post with a follow-up to outline the eventual solution. – Darryl Jan 25 at 17:28
  • Sorry, forgot to tag - this question is for @Jerry_MSFT – Darryl Jan 25 at 22:19
  • @Darryl. As I am not an expert on Power Automate, so the suggestion will only be based on my own experiences. For what is trigger condition, you can refer to this article:timlinenterprises.com/… – Jerry_MSFT Jan 26 at 1:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.