Monday, February 5, 2018

Appending field values into a string in Microsoft Flow

An aerial shot of brightly-colored jigsaw puzzle pieces

Photo by Hans-Peter Gauster on Unsplash

I’m working on implementing a Flow which triggers on a SharePoint list. This list has a managed metadata column where you can select multiple terms. In my Flow I want to append the terms into a single string which I’m persisting on my Microsoft Graph schema extension techmikael_GenericSchema.

The approach I’ll outline does not only apply to taxonomy fields, but for any input which has an array of objects. Below is a sample object array.

[
  {
    "TermGuid": "74c34307-80e8-4e21-a63b-81337e369a5c",
    "WssId": 29,
    "Label": "Term A",
    "Path": null,
    "Value": "Term A|74c34307-80e8-4e21-a63b-81337e369a5c"
  },
  {
    "TermGuid": "db79ec09-34ad-4c60-8f4c-d41248ee09ac",
    "WssId": 450,
    "Label": "Term B",
    "Path": null,
    "Value": "Term B|db79ec09-34ad-4c60-8f4c-d41248ee09ac"
  }
]

The output should pick the Value property and join them together with a semicolon like this:

Term A|74c34307-80e8-4e21-a63b-81337e369a5c;Term B|db79ec09-34ad-4c60-8f4c-d41248ee09ac

Steps

[Update – 2018-02-06]

I asked John Liu if he had some input and of course he did, and here are the updated, simpler steps :)

First add a “Data Operations – Select” step, and click the “Text mode” switch to get one input box instead of key/value pairs.

image

In the map field, pick the property of your data.

Below the select action add a “Date Operation – Join” action. In the From field pick the output of the select, and add pick your join character.

image

The part is that this works for empty arrays coming in as well :)

[Original]

There might be multiple ways to achieve this, but this is what I came up with:

First add an “apply to each” element loop which takes the array as input. Inside the loop add a “Data Operations – Compose” step.

image

I have renamed my Compose step to “ComposeSector”, and the input is the Value property of the object. The property should be available in the dynamic content, or you can add it via an expression as well, referencing the name of the loop and the object property itself.

@items('Concatenate_Terms_Values')?['Value']

Below the loop add a “Data Operations – Join” action. As the input go to the expression syntax and type:

outputs('ComposeSector')  which points to the compose step in the loop. You might have to click OK a couple of times before it accepts. And I’ve added a ; as the join character.

image

If you want to handle empty arrays as well, use the following expression:

if(empty(outputs('ComposeSector')),array(''),outputs('ComposeSector'))

When running the Flow you see the values are outputted as expects, and are ready for use :)

image

Summary

Not all data operations are straight forward in Microsoft Flow. Ideally I would have liked to the join operator directly instead of a loop and the join action – but as that’s not possible we can always work around the issues at hand. By grouping the steps involved in a scope it also makes the Flow easier to read.

Divide and conquer #FTW