cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Auto-increment a value based on a value in the most recently created item of a SharePoint list

Hi all! I am relatively new to PowerAutomate and I'm looking for a little push in the right direction. I have a simple SharePoint list in which my end users can add items. They only have to provide Title, because I want PowerAutomate to generate the ID. 

The first ID should be CC20-001. Of course, the next item should get CC20-002 and so on.
We cannot use the SharePoint list ID (because sometimes an item is deleted and then there no longer is a successive sequence).

In PowerAutomate, I'm having trouble determining the ID number value of the latest added item.

Also, when I have that value, I need to add two or one (or zero) leading zeros, depending on the length of the value of course.

Thanks in advance for your help.

Regards, Ruud

6 REPLIES 6
Jcook
Super User
Super User

Hello,

Could you share a picture of your flow,

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





abm
Super User
Super User

Hi @Anonymous 

 

This is what you need to do.

 

1. Your trigger is When an item is created.

2. Next we need to find the last created item to get the previous custom id. So to do this you need to do a Get Item lists filtered by created on date time. Set the Order by and  top  count 2.

3. Ignore the first record as it is going to be your most recent record created (trigger)

4. Get the second record from the Get Item list and find the custom counter value

5. Use split expression to retrieve the latest count value

6. Increment the value

7. Find the length and format the value

8. Update Record using the triggered ID

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials
RobElliott
Super User
Super User

Hi Ruud @Anonymous , strangely enough I'm running a training session later today on this very subject of complex IDs so I had some screenshots already prepared. In this example the ID to be produced goes from PROC0001 to PROC9999.

The way I do it is to have a separate list to hold the next increment value. In the main list the ID is a single line of text column. In the flow I then use several nested conditions:
0-sp-oncrementList.png

 

1-trigger-initVar-get.png

 

2-firstCondition.png

 

3-secondCondition.png

 

4-thirdCondition.png

Then at the bottom of the apply to each, outside all the conditions, add:

5-updateMainList.png

 

6-updateNextIncrementValue.png

 

7-SP-MainList.png

Hopefully you can follow the steps but come back with any questions about this.

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

abm
Super User
Super User

Hi @RobElliott 

 

Quick question regarding multiple users trying to create records at same time? How accurate is the next value? 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

@abm good question. I haven't tried it where 2 users are saving their items in the main list at exactly the same time. It's only a couple of seconds but I can see that there might be a problem if the first user hasn't updated the Increment value before the second user does the Get items. In my business situation there is only 1 user who will be creating the items but I do need to find a couple of volunteers to road test saving at the same time. Thanks for the heads-up.

Rob
Los Gallardos

Hi @RobElliott 

 

Thanks for your reply.

 

I was thinking the same.  From my suggested solution to get order by recently created record (Top count 2  - second record) doesn't guarantee the same as flow is running as async. 

 

@Anonymous  if you have single user creating then you could go with @RobElliott suggested solution.

 

If looking for multiple users creating the list records then try my solution but my belief is that it doesn't guarantee the exact results for multi-user updates.

 

If you looking for a full proof solution then enable the versioning on the counter list where @RobElliott  mentioned. Then checkout the list item before the update. Once update is done then check-in the list record. This way you can make sure you are always getting the correct incremented value. Also you need to do some error handling or retries if the list is already checked out.

 

Thanks

 

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (1,795)