cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vk1
Level: Power Up

Generating Unique ID with 2 criteria

Within Sharepoint, I would like to create a unique ID that resets the count every month and by service type code. So if 2 requests are made within the month of March for the same service and an additional one made in April - the April request will count back to '01'. If 2 different service requests are made within the same month - both request counts will end in '01'. I need a "counter" to append at the end of the following taxonomy:

 

yr/mm | service type code | counter

1904 123 01

1904 123 02

1905 123 01

1905 456 01

1905 456 02

1905 789 01

 

Basically, the counter resets by yr/mm and by type of service code and combined there can't be duplicates of the final unique ID - '190512301' for example. I appreciate any insight to this. I've scoured several articles but I'm having a hard time applying these 2 conditions to program a "counter".

2 REPLIES 2
Super User
Super User

Re: Generating Unique ID with 2 criteria

@vk1 Hi there!

This one is fun, thanks for bringing it up!

I am especially sharing my usual disclaimer on this one: There are likely more elegant ways to do this, but this is the hammer I have for this particular nail-looking-thing.

 

Going to just do theory until you agree on the proof of concept, let me know what you think and we can build a practical model if it sounds like it will fit your situation.

I'll pretend that you want to update the ticket number immediately after the row is created in the Sharepoint list, if that's not the case, we can adjust.

  • First step will be to get the last ticket number (unless there is a way to store it while the Flow is waiting to be triggered.
  • Next, parse out the last ticket number to 1. get the month to see if it is the current month and 2. if it is, then we grab the last ticket number ("03" as an example) and set that as a value for an integer variable ("varTicketNumber", if you want). 3.  If the last ticket is not from the current month, then we set "varTicketNumber" to 0.
  • Next step is to increment varTicketNumber by 1 (I think this only works if it's an integer so the leading 0 will go away for numbers less than 10, right?)
  • It might be fun to assign a variable to the current month, as well.
  • Then, (this might be optional) set another string variable ("charTicketNumber"?) to "0" & varTicketNumber if it's less than 10, or just varTicketNumber if it's 10 or greater.  (you can probably convert it, or use some kind of LEN function to do the same thing, but I think you get the idea).
  • Finally, append all that together to make your actual ticket number and write that to your list 🙂

What do you think?  I'm sure it will need some tuning.  I'm new.

Highlighted
JonRead
Level: Power Up

Re: Generating Unique ID with 2 criteria

I am new to Flow and interested in some instructions on how to something similar to this.

 

For me, I need to create a "Case" which is in format YYMM-(sequential number) within a list called Case in SharePoint online with the sequential number re-starting for each month so

1909-1

1909-2 ...

1909-9999

1910-1

1910-2 ...

1910-9999

 

Then, once that is created there would be "Claim(s)" created against the "Case" and these would be within a list called Claims in SharePoint online with format 1909-1-1, 1909-1-2, 1910-9999-1, 1910-9999-2, etc. The Case will be selected within the Claim so that detail will flow into the Claims list as Case ID.

 

I am guessing the "Claims" is a repeat of the "Case" one to add a new sequential number to another item.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (6,377)