question

DoraYeh-2155 avatar image
0 Votes"
DoraYeh-2155 asked ChinmoyRoy-4977 edited

Sharepoint online list serial number

Hi,

I want automatically generate serial number in sharepoint online list.

example:
2022/02/10: A001-20220210-0001, A001-20220210-0002
2022/02/11:A001-20220211-0001

And I found the tutorial unique-ticket-id-generator-in-sharepoint.html , however I can't set the Counter (Number).

Please help me to solve this problem or maybe you have better solution you can tell me,thanks.

Best Regards,
Dora .


office-sharepoint-online
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

EchoDu-MSFT avatar image
0 Votes"
EchoDu-MSFT answered ChinmoyRoy-4977 edited

Hi @dorayeh-2155 ,

Please follow the steps:

1.Create a Calculated column named [DateCreated]

 #Format the value of [Created] to "YYYYMMDD" format
 =TEXT([Created],"yyyymmdd")

173061-1.jpg


2.Create a Number column named [Counter]: This is to store the counter which is the sequence that the ticket is created of the day.

3.Set [Counter] as hidden as it is not to be availalble for input

  • a.List settings >> Advanced settings >> Allow Managment of Content Type >> Yes

173053-2.jpg

  • b.Go to the Item Content type and set the [Counter] field as "Hidden"

173047-3.jpg


4.Create a Calculated column named [CalculatedCounter]

 #Repeats text a given number of times. Use REPT() to add a number of instances of a text string.
 =CONCATENATE(REPT("0",4-LEN([Counter])),[Counter])

173040-4.jpg


5.Create a Calculated column named [Ticket Id]

 #CONCATENATE( ): Joins several text strings into one text string.
 =CONCATENATE("A001 - ",[DateCreated]," - ",[CalculatedCounter],)

173063-5.jpg


6.Go to the List, click Integrate >> Power Automate >> See your flows

172989-6.jpg


7.On the Power Automate page, click New flow >> Automated cloud flow

172939-7.jpg


8.Create the below Microsoft Flow:

172997-8.jpg

9.Save and Test

10.This flow is automatically triggered when you create a new item. It takes a few seconds for the flow to run.

173020-9.jpg


Thanks,
Echo Du
===================================
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



1.jpg (78.6 KiB)
2.jpg (92.0 KiB)
3.jpg (59.2 KiB)
4.jpg (84.2 KiB)
5.jpg (82.6 KiB)
6.jpg (50.8 KiB)
7.jpg (101.0 KiB)
8.jpg (331.5 KiB)
9.jpg (74.6 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @EchoDu-MSFT ,

Thank you for sharing clear detail, it works!

BR,
Dora

0 Votes 0 ·

Hi @dorayeh-2155 ,

I'm glad to hear that your problem has been solved.

If the answer is helpful to you, you can accept it as answer. The behavior will help others who meet the similar issue in the future quickly find the correct answer.
173505-image.png

Thanks,
Echo Du


1 Vote 1 ·
image.png (34.6 KiB)

Hi,

How to prevent the latest ID from getting deleted when the Item is deleted.
Example I have
JAN_001
JAN_002
JAN_003

When I delete JAN_002 and enter a new item, the list should look like:
JAN_001
JAN_003
JAN_004

0 Votes 0 ·