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

[Anonymous]

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

[Jcook]

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

[abm]

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

[RobElliott]

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:

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

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]

Hi @RobElliott 

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

[RobElliott]

@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

[abm]

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