Automatic Custom Incremental Reference Number

[Aleisha]

Hi, I am new to Sharepoint Lists and Power Automate and would really appreciate some help.

I aim to create a reference number for each new item created in the Sharepoint List that will consist of the date the new item was created and an incremental number, overall looks like YYYYMMDD00001. With each new item the reference number will increment by 1, so the next new item will look like YYYYMMDD00002.

I would like the incremental number to automatically reset back to 00001 each new year.

Can someone please help me (and explain in simple terms) how this can be achieved? Ideally I would like this to be automatic, the simplest it can be and kept to the one and only existing list. Is this even possible??

Would appreciate any help 🙂 Thanks.

[Nived_Nambiar]

Hi @Aleisha 

is item created on each day ?

Like if the reference number for today is 2023051500001 , then next day it should be 2023051600002 right ?

[Aleisha]

That is if there is only x1 new item for the 15th of May, Yes, that's right! Of if there are multiple in a day then the reference numbers would look like:

202305150001
202305150002

202305150003

202305160004

202305160005

then ideally on 1st Jan 2024, would change too and etc:

202401010001

202401010002

202401010003

[ZsoltDaBeast]

Hi @Aleisha,

I was also looking the solution for your case however I haven't found anything so I started to find a workaround of it and use a random number each time when a new item created. I know this is not what you are looking for but can be a temporary solution for you.

Create a "Compose" with "rand(0,9999)" and do a check if file exist than condition so than you can ensure the same file won't get the same suffix number.

I hope this may help temporary.

[Nived_Nambiar]

Hi @Aleisha 

Try this logic.

What the bot does is it checks the last added item in the list, checks its number and then add date (current date) to it.

See the below flow

I have used below sharepoint list as an example

Step 1: use get items to pick items that were created on 1st jan , , using the Ref Num column as filter query

expression used: 

concat(formatDateTime(utcNow(),'yyyy'),'0101')

Step 2: use condition to check if the current date is 01 Jan and there are items in sharepoint list from output of get items in step 1

expression used : 

concat(formatDateTime(utcNow(),'yyyy'),'-01-01')

expression used: 

length(outputs('Get_items')?['body/value'])

If the condition is True, it means date is 1st Jan and it is first item to be created, so we will set the Ref Num column as YYYYMMDD00001 

expression: 

concat(formatDateTime(utcNow(),'yyyyMMdd'),'00001')

If the condition is False it means we date is not 1st Jan or it is the not first item created at starting of this year

In No branch , we will do the following thing

here we will get the items from user list but by reversing the Created , by this the output of get items will have items with recently created items at top, from which we need the first item.

Next use compose action to create the RefNum for newly created item 

expression: 

concat(formatDateTime(utcNow(),'yyyyMMdd'),formatNumber(add(int(skip(first(outputs('Get_items_2')?['body/value'])?['RefNum'],8)),1),'00000'))

Use create item to create the item with output of compose action as new RefNum 

This way you can increment the reference number and also able to reset its value at 1st jan .

The output you will get is 

Hope this helps !

Mark it as solution if it resolves your query !

[Aleisha]

Hi @Nived_Nambiar 

I have the below warning, what can I do about this?

[Nived_Nambiar]

Hi @Aleisha 

It is warning from get items action to use the filter query as it will efficienty query the items . But in our case, i am using order by to order by created time and taking top count as 1. This way we will get most recent item. 

So you can ignore this warning and can run your bot 🙂

[Aleisha]

Hi @Nived_Nambiar 

Sorry to be a pain, I tried to run my flow and I got an error.

[Nived_Nambiar]

Hi @Aleisha 

Could you share screenshot on how you have written filter query ? something is wrong with the filter query 

Make sure you have written this way which i have mentioned in my message earlier.

[Aleisha]

Hi @Nived_Nambiar 

I believe it to be the same..

[Nived_Nambiar]

Hi @Aleisha 

I think there is some issue with expression 

Show how you have written the expression 

[Aleisha]

Hi @Nived_Nambiar 

Expression is: 

concat(formatDateTime(utcNow(),'yyyy'),'-01-01')

[Nived_Nambiar]

@Aleisha 

Everything is correct only, could u check the column name is correct or not , also what is the datatype of RefNum column ?

[Aleisha]

Hi @Nived_Nambiar 

I did not use the original column name, have corrected now. The datatype for the RefNum column is Single Line of Text.

I now have another error:

I really really appreciate you coaching me through this flow!! 

[Nived_Nambiar]

Hi @Aleisha 

I think space between column created issue , do one thing to get correct column name to be used in filter query, check the output of this get items action and take the one which contains Created Decs variable, just hover over it and you will get correct column name to be used 

like below 

here i have used to check Department Column name, here i see Department is correct column name when hovering over dynamic content, i will use that column name in filter query

Try that way 🙂

It would help you in resolving issue 🙂

[Aleisha]

Hi @Nived_Nambiar 

I am having trouble with the "Ordered By" field, I have used Created Decs as you have suggested but that creates an error in my flow. Does this need to be a Dynamic Content, or an expression?

[Nived_Nambiar]

Hi @Aleisha 

 i could not find the Created Decs used in the get items here?

Could u share a screenshot of your flow

what i have used in flow is Created desc , which is to order the items by created date in descending order.

[Aleisha]

Hi @Nived_Nambiar 

Flow is as below:

Expression: 

concat(formatDateTime(utcNow(),'yyyy'),'-01-01')

Expression/s: 

formatDateTime(utcNow(),'yyyyMMdd')

concat(formatDateTime(utcNow(),'yyyy'),'-01-01')

length(outputs('Get_items')?['body/value'])

Expression: 

concat(formatDateTime(utcNow(),'yyyyMMdd'),'00001')

Expression: 

concat(formatDateTime(utcNow(),'yyyyMMdd'),formatNumber(add(int(skip(first(outputs('Get_items_2')?['body/value'])?['ReferenceNumber'],8)),1),'00000'))

I seem to still get the below error:

[Nived_Nambiar]

Hi @Aleisha 

It should be Created desc not Created Desc, 

see below

[Aleisha]

Hi @Nived_Nambiar 

Oh gosh, I am learning very quickly the Sharepoint Lists and Power Automate are VERY specific.

I now have this error with the flow and I have copied your expression as you have suggested.

concat(formatDateTime(utcNow(),'yyyyMMdd'),formatNumber(add(int(skip(first(outputs('Get_items_2')?['body/value'])?['ReferenceNumber'],8)),1),'00000'))