How to Increment a Number in a SharePoint List Using Power Automate

An HR team I worked with ran employee training registrations through Microsoft Forms. Every new registration had to get the next number in a SharePoint list: 1, 2, 3 and so on. SharePoint’s own ID column wasn’t an option, because they wanted their own sequence that they could reset each year.

In this tutorial, I’ll show you how to increment a number in a SharePoint list with Power Automate: read the current highest number, add 1, and save it on the new item, without getting duplicates when two people submit at the same moment.

Quick answer: Before you create the item, use Get items with Order By set to RegistrationNumber desc and Top Count set to 1. Then calculate the next number with add(coalesce(first(body('Get_items')?['value'])?['RegistrationNumber'], 0), 1) and use it in Create item. Turn on concurrency control with a limit of 1 on the trigger so runs can’t read the same number.

The example

My SharePoint list, Registrations, has these columns:

Column	Type
Employee ID	Title (single line of text)
Employee Name	Single line of text
Register For	Single line of text
Registration Number	Number (internal name RegistrationNumber)

Employees fill in a Microsoft Form called Registration Form For Employees, and each response becomes a list item. If you haven’t connected Forms to a list before, see how to save Microsoft Forms responses to a SharePoint list.

Step 1: Start the flow from the form

Create an Automated cloud flow with the Microsoft Forms trigger When a new response is submitted, and select your form:

Add Get response details with the same form and the Response Id from the trigger, so you can use the answers later:

Step 2: Read the highest number so far

Add the SharePoint Get items action on the Registrations list. Under the advanced parameters, set Order By to RegistrationNumber desc and Top Count to 1, so it returns only the item with the highest number:

Use the column’s internal name in Order By, not its display name. If filter and sort options are new to you, see how to use the Get items filter query.

Notice that this happens before the new item exists. The original version of this post created the item first and read the maximum afterward, which meant it then had to update the item in a loop and could easily update the wrong one.

Step 3: Work out the next number

Add a Compose action, rename it NextNumber, and enter this expression:

add(coalesce(first(body('Get_items')?['value'])?['RegistrationNumber'], 0), 1)

• first(...) takes the single item Get items returned, so you don’t need an Apply to each loop.
• coalesce(..., 0) handles the very first registration, when the list is empty and there’s no number yet.
• add(..., 1) adds 1 to the highest number.

You don’t need variables for this. If you prefer them, use one Initialize variable action per variable, of type Integer, at the top level of the flow. For more on loops you can avoid, see how to get the current item in Apply to each.

Step 4: Create the item with the new number

Add Create item on the Registrations list. Map Employee ID, Employee Name and Register For from Get response details, and set Registration Number to the output of NextNumber. The item is saved with its number in one step.

Save the flow and submit the form a couple of times. The first response gets number 1, the next one 2, and so on.

Step 5: Stop duplicate numbers

Flows can run at the same time. By default, the trigger doesn’t limit parallel runs, so if two people submit within a second, both runs can read the same highest number and save the same next number.

To prevent that, open the trigger’s Settings, turn on Concurrency Control and set the Degree of Parallelism to 1. Runs then wait in line and read the number one after another. Microsoft notes that once you turn this on, you can’t turn it off again without deleting and re-adding the trigger.

For a sequence that must never repeat, even if someone edits the list by hand, consider using SharePoint’s own ID column, or a custom ID built from it. See how to create a custom ID column in a SharePoint list.

Starting from a SharePoint trigger instead

If your flow starts with When an item is created and then updates that same item with the number, use the trigger’s ID in Update item. Don’t use When an item is created or modified, because the update would start the flow again.

If you must use it, add a trigger condition so it only runs when the number is empty; see these Power Automate trigger conditions examples.

To change only that one column, see how to update only one field in a SharePoint list item.

Troubleshooting

• The first item gets no number: add coalesce(…, 0) so an empty list starts at 1.
• Duplicate numbers: set the trigger’s concurrency control to 1.
• Order By fails: use the internal column name, which you can see in the column’s URL in list settings.
• The number is a decimal like 3.0: make the column a Number with 0 decimal places, or wrap the result in int().

These Power Automate SharePoint tutorials go well with this one:

• How to Update SharePoint List Items Using Power Automate?
• How to Update SharePoint List Items Based On Condition Using Power Automate?
• How to Get More Than 100 Items with Get Items in Power Automate (and Past 5,000)
• How to Send an Email Based on a Microsoft Forms Response With Power Automate

Frequently Asked Questions