Use Power Automate to create an attribute-based ID in MS Lists

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 nameColumn type
Ticket TypeChoice with the following options: “Question”, “Incident”, “Problem” and “Request”
Ticket IDSingle line of text used to store the Ticket ID
My Test list

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”.

New SharePoint "When an item is created" trigger

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
Initialize variable

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))
Condition and Set variable action

I know the expression is a bit ugly, so let’s break it down into smaller pieces to better understand what is happening.

  1. Concatenate “00000” and “ID” from the List (“0000010”)
  2. Is the length of the newly concatenated string (7)
  3. Is the difference between the length of our new string (7) and the required number length (5), (2)
  4. Substring our new string from position 2 through to 5,(“00010”)
  5. 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-“
Multiple Conditions for each Ticket type value

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.

My completed Flow

Save and test your Flow by inserting a new row into the Test list.

List items with new attribute-based ID column called Ticket ID

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

  1. Hi,
    why simple conditions instead of the switch? You can make it cleaner and save some extra api calls.


    1. Hi Bart,

      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!



  2. 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
    102 PRB-00001
    101 QST-00003
    100 REQ-00001
    99 INC-00001
    98 QST-00002
    97 QST-00001


    1. I have been able to do it by filtering the ‘Get items’ with a filter query and then counted the number of rows.


      1. Hello, Emmanuelle! Could you please share to do it? I’m new in Power Automate and I’m struggling on doing what you described.


    2. Hi Emmanuelle,

      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.



    1. 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!



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s