What is the most efficient way to re-populate Choice and Lookup columns (Multiple value) in an Update Item action?

Question

I often find myself in this scenario:

• Create a flow with the SharePoint list When an item is created or modified trigger
• Add an Update Item action (on the same item that triggered the flow)

(For reference, I use a service account to run the flow, and a conditional structure on the Modified By email address to avoid an infinite loop when the flow updates the item).

Even if I want to update a single column value in the item, the Update Item action requires that you re-populate all column values in the item (if you leave column values blank, it will update the column values to be blank).

This is bothersome, but simple enough if the column is of type Single Line of Text, or even a Choice or Lookup column (Single) - the 'Dynamic Content' that is available from the original trigger can easily be added to the Update Item action.

However, when the column type is one of the following, it seems to become trickier:

• Choice (Multiple)
• Lookup (Multiple)

The Choice (Multiple) column seems to require data in this format:

[
{
  "Value": value_a
},
{
  "Value": value_b
}
]

And the Lookup (Multiple) column seems to require data in this format:

[
{
  "Id": 1
},
{
  "Id": 2
}
]

In order to get these values in the required format, I find myself having to create a loop on the original column values and manually appending each value to an array variable (similar to the approach in this article), and then using that array variable to re-populate the same column fields.

This is VERY messy and time consuming when there are several multiple choice columns in the list.

It would be EXTREMELY HELPFUL if there was 'Dynamic Content' in the format required by the Update Item action.

The 'Dynamic Content' in the Power Automate interface that is available on these fields are:

 - ChoiceMultiple
 - ChoiceMultiple Item
 - ChoiceMultiple Value  

 - LookupMultiple  
 - LookupMultiple Item  
 - LookupMultiple Value 
 - LookupMultiple Id

But I don't know what these different values refer to (and they often get put in a loop when I try and use them - even if I am putting them in a field in 'array input mode').

Am I overlooking something?

Is there a way to repopulate multiple value fields without having to manually construct an array of objects in the format required in the Update Item action?

In order to troubleshoot the issue, I did a Compose on the body value that is grabbed via the trigger action:

Choice column (Multiple) data looks like this:

"ChoiceMultiple": [
  {
    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
    "Id": 0,
    "Value": "Choice A"
  },
  {
    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
    "Id": 1,
    "Value": "Choice B"
  }
],

"[email protected]": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference)",

"ChoiceMultiple#Id": [
  0,
  1
],

"ChoiceMultiple#[email protected]": "#Collection(Int64)"

Lookup column (Multiple) data look like this:

"LookupMultiple": [
  {
    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
    "Id": 1,
    "Value": "Value A"
  },
  {
    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
    "Id": 2,
    "Value": "Value B"
  }
],

"[email protected]": "#Collection(Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference)",

"LookupMultiple#Id": [
  1,
  2
],

"LookupMultiple#[email protected]": "#Collection(Int64)"

Answer 1

Try this suggestions for your requirements:

A. Instead of using "Apply to each" action to get the lookup and choice column values in required format, you can use Select action to generate values in specific/required format very easily. It also reduces the number actions / API calls in your power automate flow

B. If you lookup and choice fields are not required/mandatory in SharePoint list, you don't need to pass the value to these columns again in update item action. Try using update item action without passing any value in these non mandatory fields.