Categories
Guides Microsoft Flow Power Automate Power Platform

Copy multiple people and multiple choice fields

How to create or update multi-select person, group, and choice fields in a Microsft SharePoint List using Microsoft Power Automate (Flow).

A quick guide on how to handle multi-select person, groups, and choice fields in a Flow.

1. Initial Situation

Let’s say we have two Microsoft SharePoint lists with a multi-select person and a multiple-choice column each.

In this example, we want to copy values from List A to List B.

List A has the following data:

List A has multiple users and multiple devices in each row.
The Title column is irrelevant.

List B has no data yet:

List B is empty.

Now, let’s see how we can get the data from List A and create the rows in List B.

2. Trigger the flow and get the data from List A

But first things first… for our example, we need to trigger the flow and get all values from List A using the Get items action.

I’ve decided to manually start the Flow. For you, the trigger might be a scheduled event.

3. Using the Select action, copy a multiple person or multiple choice column. 

To create or update a multiple-person, group, or multi-select choice column in a Microsoft SharePoint list, you’ll need to use the Select action.

Add an Apply to each action and loop through all rows from List A using the dynamic content value from the Get items action.

Inside the loop, add two Select actions.

In the From field of the first Select action, insert the dynamic content People.

In the Map section of the first Select action, write Claims inside the key field and the expression item()?[‘Claims’] inside the value field.

item()?['Claims']
Make sure you enter the item()?[‘Claims’] string as an Expression!

In the From field of the second Select action, insert the dynamic content Devices.

In the Map section of the second Select action, write value inside the key field and the expression item()?[‘Value’] inside the value field.

item()?['Value']
Make sure you enter the item()?[‘Value’] string as an Expression!

4. Update the Microsoft SharePoint List B

All we now have to do is simply add a Create item action to create the items in List B.

In the People and Devices column, click on the icon on the right side of the field to change the input to an entire array.

Then select the dynamic content Output from the Select actions. Make sure you select the right dynamic content for the right field… (People / Devices)

Dynamic content Output People goes into the People column.
Dynamic content Output Devices goes into the Devices column.

Check

Let’s test the Flow and check if it worked.

The whole Flow in edit mode.
The Flow ran without any errors.

As you can see, all the data from List A is now inside List B.

Categories
Guides Microsoft Flow Power Automate Power Platform

Sanitize Strings

How to remove unwanted characters from a string in Microsoft Power Automate.

A quick guide on how to easily sanitize your text values in a Flow.

1. Initial Situation

Let’s say we have a string value. It does not matter from what trigger or action it comes from. In my case, I just have a Compose action with the following Input (value).

       amzAMZ160,.-;:_@)s9=)"*       =ç%&/"()=ç&Z"=                     

There are a lot of special characters and empty spaces in there that I do not want, but the end user has entered them anyways… 🙄 😀

2. Trim() – Remove all unnecessary empty spaces

The first thing I always do is use the trim() function on the string. This function removes all extra spaces from a string of text.

I do this right inside the Initialize variable action. Name it InitialString and as an Expression in the Value field, you enter the following code.

trim(outputs('Compose_-_InitialString'))
Check

After the first two actions, our output now looks like this:

amzAMZ160,.-;:_@)s9=)"*       =ç%&/"()=ç&Z"=

Notice that the empty spaces at the front and at the back are not there anymore!

3. Map() – Create an array

Let’s now map our string and put every character as a single value in an array. This way, we can then check every character for its legitimacy later.

To create an array from a string value, we use the Select action with the following two expressions as From and Map values:

From

range(0, length(variables('InitialString')))

Map

Make sure you click on Switch Map to text mode before you enter the following expression.

substring(variables('InitialString'), item(), 1)

Your Select action should now look like this:

Check

Let’s check our output so far. After running the flow, we now have an array with every character of the initial string.

4. Allowed Characters

Let’s now define the characters that we will allow to remain in the string. For me, it’s all alphanumeric characters.

That means a-z, A-Z, and 0-9.

Create a new array (Initialize variable), call it AllowedCharacters, and put the following code as its Value as an Expression.

createArray('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z')

If you want to allow some special characters or more letters, you can simply edit the createArray() function to your liking. For example, to add ö,ä,ü,Ö,Ä and Ü the function would look like this:

createArray('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','ö','ä','ü','Ö','Ä','Ü')

After that step, I also add another new variable, this time a string variable, that will hold our sanitized value at the end. This will also be the variable that you will use after the sanitization for the rest of your flow.

Let’s call it ValueAfterSanitization. Value stays empty for now.

5. Remove any unwanted characters

This is where the magic happens…

We will loop through every character from the InitialValue array (created by the Select action) and check if it is in the AllowedCharacter array or not. If it is, we append it to the string variable ValueAfterSanitization.

To start the loop, add an Apply to each action. For its Value, choose the dynamic content Output from the Select action.

Add a Condition action inside the Apply to each.

  • The first value is the AllowedCharacters array
  • operator is contains
  • The second value is the expression: item()

This means we check each character (item()) if it is somewhere in the AllowedCharacters array.

In the If yes section of the Condition, we now add the character to the ValueAfterSanitization variable.

The If no section can stay empty, those item()s are the characters that we don’t want in our string.

Check

Let’s do one last check. We add a Compose action with the Input ValueAfterSanitization.

At the top, we can see our trimmed string had 44 characters. We looped through each one of them and only added the characters we wanted to the ValueAfterSanitization variable.

In the last Compose action at the bottom, we can now see our sanitized string. Completely without special characters and empty spaces.

We went from this:

               amzAMZ160,.-;:_@)s9=)"*       =ç%&/"()=ç&Z"=                     

to this:

amzAMZ160s9Z

Conclusion / Summary

Instead of checking for characters that we don’t want to allow, we simply defined an array with the characters that we do allow.

This way, we don’t have to worry about different languages with weird (special) characters or emojis.

The whole Flow looks like this