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.

NY

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

    Like

    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!

      NY

      Like

Leave a Reply to Bart Jablonski Cancel reply

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

WordPress.com Logo

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

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s