In this blog post I will show how to use Power Automate to create an attribute-based ID in Microsoft Lists. Attribute-based translates to business readable with formatting. This pattern is based on work I did at my previous employer where the List was being used for data imports into a legacy application that required a:
- Prefix (“QST-” for question, “INC-” for incident, “PRB-” for problem and “REQ-” for request)
- Unique number up to 5 character with leading zeros (00123)
Our demo list, called Test, has the following schema:
|Column name||Column type|
|Ticket Type||Choice with the following options: “Question”, “Incident”, “Problem” and “Request”|
|Ticket ID||Single line of text used to store the Ticket ID|
Create a new Flow from the Test list > Automate > Power Automate > See your Flows > Create new > Automated from blank. Provide a Flow name, i.e., “New Ticket ID”, select the SharePoint “When an item is created” trigger and click “Create”.
Set the “Site Address” and “List Name” to your target site and list.
Create a new Initialize variable action and set:
- Name to “varTicketID”
- Type to “String”
- Value to “ID” from the “When an item is created” trigger
Insert a new Condition action and set:
- Choose a value to “Ticket Type value”
- Condition to “is equal to”
- Choose a value to “Question”
In the If yes branch add a Set variable actions and set:
- Name to “varTicketID”
- Value to the following expression:
concat('QST-', substring(concat('00000', triggerOutputs()?['body/ID']), sub(length(concat('00000', triggerOutputs()?['body/ID'])), 5), 5))
I know the expression is a bit ugly, so let’s break it down into smaller pieces to better understand what is happening.
- Concatenate “00000” and “ID” from the List (“0000010”)
- Is the length of the newly concatenated string (7)
- Is the difference between the length of our new string (7) and the required number length (5), (2)
- Substring our new string from position 2 through to 5,(“00010”)
- Concatenate our newly formed string with “QST-” for Question, resulting in “QST-00010”
Repeat the Condition and Set Variables above for each of the remaining Ticket Types:
- Incident as “INC-“
- Problem as “PRB-“
- Request as “REQ-“
Insert a new SharePoint Update item action and set “Site Address” and “List Name” to your target site, list and:
- ID to “ID” from the “When an item is created” trigger
- Ticket ID to “varTicketID”
My completed Flow looks like the image below.
Save and test your Flow by inserting a new row into the Test list.
If all goes well, you will have created a new attribute-base ID. For most List work I have encountered this pattern is not required and should only be used when necessary. The real trick to this pattern is mastering the complicated expression. Breaking it down into smaller sections will make this expression manageable when you must do like manipulations.
Thanks for reading.
8 thoughts on “Use Power Automate to create an attribute-based ID in MS Lists”
why simple conditions instead of the switch? You can make it cleaner and save some extra api calls.
Valid points, I agree it will be cleaner and less “expensive”. I not aware of a way to count API calls for a single Flow. Do you?
Thanks for reading!
Hi, thanks for your blog. I am starting with Sharepoint Lists and Power Automate and your posts help a lot !
Would it be possible to base the value of the digit not on value of “ID” but instead to count the number of items per ticket types already recorded and increment the new Ticket ID by 1 ?
Based on your exemple we would have :
ID Ticket ID
I have been able to do it by filtering the ‘Get items’ with a filter query and then counted the number of rows.
Hello, Emmanuelle! Could you please share to do it? I’m new in Power Automate and I’m struggling on doing what you described.
Yes, that is possible but you will have to look up the most recent max value every time the Flow triggers. I would only do this if your list has a low transaction rate to help avoid concurrency issues. I have done this in the past and transaction volume was measured in hours so there were never any Flows tripping over each other.
I hope this helps and thanks for reading.
Why didn’t you use a calculated column? This would auto-update itself when the entry is changed?
Hi Fab, a calculate column would work for use within the list but could not be used in apps like Power Automate that cannot evaluate calculated column values.
Thanks for reading!