“I’ve got multiple SharePoint lists with a choice column, can I somehow update the available choices in all of them, e.g. with Power Automate?”
If you’re using Power Automate to synchronise multiple SharePoint lists, the lists should be the same. The same columns, with the same internal name, and the same available values. If there’s a change in one of the lists, you should do the change also in the others. But what if it’s more than a few lists? If you’ve got 10+ lists with the same structure, and you need to change the available choices in a choice column? Would you update them manually, one by one?
Get the new values
To update the choices you must get the actual choices in a form of an array. The easiest way might be to convert them from a string. Build a string of the choices with a fixed separator, and then split(…) them using it, e.g.
Choices:
Choice1;Choice2;Choice3;Choice4
split(Choices,';')Update the values with an HTTP request
Once you have the choices, you can update them in the column. The HTTP request goes as below:
Method: PATCH
Uri:
_api/web/lists/GetByTitle('<list name>')/fields/GetByTitle('<column name>')
Headers:
{
"accept": "application/json;odata=verbose",
"Content-Type": "application/json;odata=verbose",
"IF-MATCH": "*"
}
Body:
{
'__metadata': {
'type': 'SP.FieldChoice'
},
'Choices': { 'results': <array with choices> }
}Note: <…> represents a placeholder, replace it including the < and >.
All that’s left is to add a configuration list with the site addresses and list names, and update the choice column for each of them.
Summary
In most situations you build a flow over existing data structure. You define the structure manually, e.g. create columns in a SharePoint list, and then process only the data on some regular basis. But that’s not the only usage.
It’s possible to use flows also for one time tasks, e.g. remove SP list duplicates or copy SP pages, as already shown in the previous articles. And another such task might be to use Power Automate to update available choices in a SharePoint choice column. To not only modify the data itself, but change also its structure. With the right HTTP request (almost) anything is possible.
Hi,
Thanks for the regular blog. I always learn something new from it. Can this example be used to update or replace a choice column’s values from items from another list? Can you please show an example for how this could work?
Thanks
Hello Michael,
if you’re able to extract the values in an array, you can replace them. I’d use the same approach as in this article to get the unique values (with the Select and union(…)): https://tomriha.com/send-one-email-per-user-with-multiple-sharepoint-items-in-power-automate/, instead of emails extract the choice values. Once you have the unique values in an array you can use it in the HTTP request.
Hi Tom,
Great blog, very helpful! Do you happen to know whether it is also possible to edit the style of a choice using this HTTP request? What do I have to add to the body to pick a color for each choice?
Thanks!
Hello Kilian,
I don’t know I never tried this.
Before updating the choices, I would want to get a list of the choices that are currently available, then add the choices that aren’t in the list, yet. For example, each time an item is added or updated in a choice field that allows manually adding values, verify that the choices made exist and add them to the choices list if they are not on there. Is there a way to get the choices in the dropdown list first?
you basically make a GET request (empty header, empty body) to the same site address using this URI
Identifying your sharepoint list by GUID:
_api/web/lists(guid’xxxxxxxxxx’)/fields/GetByTitle(”)/Choices
Identifying your sharepoint list by Name:
_api/web/lists/GetByTitle(”)/fields/GetByTitle(”)/Choices
I’m doing this the exact same way … I just can’t get the POST to work – getting back a 200 OK, but no Choices are added 🙁 I’m frustrated ..
If anybody knows anything that needs to be done in 2024 to get this work – let me know
Hi!
Unfortunately that´s not working for me, I get the error message “The type SP.FieldCollection does not support HTTP PATCH method.” Is there a way to solve this?