Author: Caspar Camille Rubin

I’m a Cloud Systems Engineer working at the Universitätsspital in Basel, Switzerland, doing mostly custom SPFx Development for SharePoint & MS Teams and using the Microsoft Power Platform for Business Process Automation, helping drive forward our Digital Transformation.

Categories
Guides Microsoft Flow Microsoft List Power Automate Power Platform SharePoint SharePoint List SharePoint On-Premise SharePoint Online

How to copy all versions of a SharePoint List Item to another List with a Microsoft Power Automate Cloud Flow in 4 easy steps

Copy a list item with its version history from one SharePoint List to another in a Microsoft Power Automate Cloud Flow

A quick and simple guide by Caspar Rubin that is easy to understand and follow.

1. Initial Situation

We have two Microsoft SharePoint Lists.

List A and List B.

List A has elements with multiple versions and we want to copy those elements with their complete version history to List B.

As you can see below, List A has some sample data and multiple version per row.

List B has no data yet but the same columns as List A.

2. Setup

In your flow, initialize a new variable, give it a name, mine is called ListItemID, and set its type to Integer. Leave the value field empty for now.

We will use this variable at a later point in time.
Variables need to be initialized at the top level of a flow, so that’s why we do it at the start...

3. Get all data

To copy the list items we have to get the items and all their versions from List A first.

We do this by using the “Get items” action to get the newest version and the “Send an HTTP Request to SharePoint” action to get all versions in our Microsoft Power Automate Cloud Flow.

Click on the image to open it in a new tab

The *Uri in the “Send an HTTP request to SharePoint” action above is the following:

/_api/web/lists/getByTitle('List A')/items(items('Apply_to_each')?['ID'])/Versions?$orderby=VersionLabel asc

Just replace “List A” with the name of your list and “items(‘Apply_to_each’)?[‘ID’]” with the dynamic content ID from the “Get items” action.

4. Copy the Data

We have now retrieved all data from List A, including all versions.

To put that data into List B, we need to iterate / loop through all version and create new items or update existing items, depending on where we are in the loop of our collected data.

To find out if we have to create a new item or update an existing item, we use a Condition action and check the VersionLabel it is is 1.0 or something else.

Click on the image to open it in a new tab
  1. We use the following Expression to loop all versions.
body('Send_an_HTTP_request_to_SharePoint_-_Get_all_versions')?['d']?['results']

2. We use the following Expression to check if a version is a new item or if we need to update an existing item.

Important: Make sure you enter “1.0” on the right side of the argument, not just 1.0 because Power Automate will change 1.0 into 1 and the flow won’t work, it needs the ” “.

items('Apply_to_each_-_Versions')?['VersionLabel']

3. We use the following Expressions inside the Create item and Update item actions to fill in the data. Replace Title with the column name. For example, my four columns are:

items('Apply_to_each_-_Versions')?['Title']
items('Apply_to_each_-_Versions')?['Number']
items('Apply_to_each_-_Versions')?['Choice']
items('Apply_to_each_-_Versions')?['Date']

4. Use the “Set variable” action to set the ListItemID variable to the Dynamic content ID of the created item.

outputs('Create_item')?['body/ID']

5. Use the variable “ListItemID as ID in the Update item action

5. Test

When we run the flow, it does exactly what it’s suppose to do. It copies all items and all versions from List A to List B.

Click on the image to open it in a new tab

Data & Versions in List B:

How to copy all versions of a SharePoint List Item to another List with a Microsoft Power Automate Cloud Flow in 4 easy steps

A quick and simple guide by Caspar Rubin that is easy to understand and follow on how to copy all versions of a SharePoint List Item to another List with a Microsoft Power Automate Cloud Flow in 4 easy steps or how to copy a list item with its version history from one SharePoint List to another in a Microsoft Power Automate Cloud Flow.

Categories
Guides Microsoft Flow Microsoft List Power Automate Power Platform SharePoint SharePoint List SharePoint On-Premise SharePoint Online

How to copy “multiple people” and “multiple choice” column fields from one SharePoint List to another using a Microsoft Power Automate Cloud Flow in 4 easy steps

Create or update multi-select person, group, and choice fields in a SharePoint List using a Microsoft Power Automate Cloud Flow

A quick and simple guide by Caspar Rubin that is easy to understand and follow.

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.

5. 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.

How to copy “multiple people” and “multiple choice” column fields from one SharePoint List to another using a Microsoft Power Automate Cloud Flow in 4 easy steps

A quick and simple guide by Caspar Rubin that is easy to understand and follow on how to copy “multiple people” and “multiple choice” column fields from one SharePoint List to another using a Microsoft Power Automate Cloud Flow in 4 easy steps or how to create or update multi-select person, group, and choice fields in a SharePoint List using a Microsoft Power Automate Cloud Flow.

Categories
Guides Microsoft Flow Power Automate Power Platform

How to remove characters from the end of a string in Microsoft Power Automate Cloud Flows in 2 easy steps

Remove a certain number of characters from the end of a string value or variable inside a Microsoft Power Automate Cloud Flow

A quick and simple guide by Caspar Rubin that is easy to understand and follow.

1. Initial Situation

Let’s say we have a dynamic content string value where we want to remove the last three characters.
An example for this would be a date where we don’t want the days, only year and months.

Example “Compose” action

So basically turn this:

2022-09-06

in to:

2022-09

To do this, we need to remove -06 from the first string.

2. Expression

The Expression we use, to get rid of a certain amount of characters at the end of a string is the following:

substring(STRING,0,sub(length(STRING),3))

Let’s replace STRING in the above Expression with our dynamic content from the flow.

In our case, that’s the Compose – Date action.

substring(outputs('Compose'),0,sub(length(outputs('Compose')),3))

If you want to remove, let’s say, 5 characters instead of only 3, just change the number 3 at the end of the Expression to 5. Like this:

substring(outputs('Compose'),0,sub(length(outputs('Compose')),5))

The flow now looks like this:

Don’t forget to add the formula as an Expression!

3. Check

When we now run the flow, we get the following result:

How to remove characters from the end of a string in Microsoft Power Automate Cloud Flows in 2 easy steps

As you can see, the – sign and the days have been removed from the string value.

A quick and simple guide by Caspar Rubin that is easy to understand and follow on how to remove characters from the end of a string in Microsoft Power Automate Cloud Flows in 2 easy steps or how to remove a certain number of characters from the end of a string value or variable inside a Microsoft Power Automate Cloud Flow.

Categories
Guides Microsoft Flow Power Automate Power Platform

How to sanitize strings in a Microsoft Power Automate Flow in 5 easy steps

sanitize strings
sanitize strings

Remove all unwanted special characters and only allow specific, alphanumeric values in a string inside a Microsoft Power Automate Cloud Flow

A quick and simple guide by Caspar Rubin that is easy to understand and follow.

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 – 2. Trim()

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 – 3. Map()

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 – 5. Remove any unwanted characters

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

6. 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

How to sanitize strings in a Microsoft Power Automate Flow in 5 easy steps

A quick and simple guide by Caspar Rubin that is easy to understand and follow on how to sanitize strings in a Microsoft Power Automate Flow in 5 easy steps or how to remove all unwanted special characters and only allow specific, alphanumeric values in a string inside a Microsoft Power Automate Cloud Flow.