Handling Multi Choice fields from SharePoint when sending emails

Introduction

One common requirement is to send automated email notifications based on data stored in SharePoint lists. However, dealing with complex data types such as multi-choice lookup columns in SharePoint can be challenging, as these fields are stored as arrays and can make the email content look cluttered.

In this blog, I’ll walk you through building a Power Automate flow that sends a well-formatted email based on SharePoint list data, specifically handling multi-choice lookup columns.

Sample

For this blog, I will be using a list with a People field “Manager” and a multi-option Lookup field “Pricing”

Problem

When dealing with multi-choice lookup columns in SharePoint, the values are stored as arrays, leading to lookup content in emails that look like this:

[
  {
    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
    "Id": 21,
    "Value": "CA"
  },
  {
    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
    "Id": 22,
    "Value": "CV"
  },
  {
    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
    "Id": 13,
    "Value": "FG"
  }
]

Solution

To address this, we will create a Power Automate flow that:

Stores the lookup field as an array.
Converts the lookup field into a concatenated string.
Sanitizes the list item into a readable format.
Builds an HTML table with the list data.

Instructions

I start by sharing the entire flow

Step 1: Initialize Variables

First, we need to initialize three variables in our flow:

Pricing Array: To store the lookup field as an array.
Pricing String: To store the concatenated string of lookup values.
List items: To convert and sanitize the list item into a readable list.

Step 2: Get Items from the SharePoint List

Query the list. Add any oData filters if required.

Step 3: Apply to Each Item

Use an “Apply to each” action to process each item from the SharePoint list.

Step 3.1: Set Pricing String Variable to null

Reset the Pricing string variable before moving to the next list item. Do this by setting value to the expression “null” or just enter a space.

Step 3.2: Set Pricing Array Variable

Set the Pricing Array variable with the value of your multi-choice lookup column.

Step 3.3: Apply to Each Lookup Value

Use another “Apply to each” action to iterate over each value in the Pricing Array.

Inside this loop:

Append each lookup value to the Pricing String variable.
Add a delimiter such as a comma or line break for readability.

concat(items('Apply_to_each')['Value'],',')

Step 4: Craft the desired List Item look

For People fields, just pick the DisplayName or Email as needed.
For Lookup fields, reference the Pricing String variable

Step 5: Set List Items Variable

Step 5: Create HTML Table

Use the “Create HTML table” action to build an HTML table from the List items Array variable.

@{variables('List items')}

Step 6: Send an Email

Finally, use the “Send an email” action to send the email. In the body of the email, include the HTML table created in the previous step.