Promptly generate unique ID in SharePoint list using Power Automate – Microsoft 365

 549 total views,  1 views today

In this “generate unique ID in SharePoint list” article, we will learn how promptly we can generate custom unique ID in SharePoint list using Power Automate.

Use cases – Unique ID in SharePoint list

Use case #1

In SharePoint, we have the “ID” column, which gives the unique number for each item in the SharePoint list, like 1, 2, 3 4, etc. However, for business needs, if we want to get along with the ID column some businesses needed strings, for example, LEAVEREQ_ID, EXPCLM_ID, TRAVREQ_ID, etc. then, the ID column alone is not good enough, we need to have the custom ID column which will be generated automatically.

Use case #2

Let’s say we want to clone two lists – that means whenever an item is created or updated in list A, list B will be updated automatically. Then we must need to have a common custom unique ID column in both lists, the out-of-the-box ID column approach will fail because the value of the ID column both in lists A and B always will be different, so, we cannot update the list B based on the ID column value – so, we must need custom ID column where this column will maintain same values in both lists.

Generate unique ID in SharePoint list using Power Automate – demo

Below is the custom list “Request Ids Generation”, we have created for this demo, along with below columns:

• Title – Default Title column
• CustomRequestID – a single line of text column
• RequestType – choice column with the values – Leave, Expense Claim, Travel

 

• 

Generate unique ID in SharePoint list using Power Automate – demo

Demo Explanation:

If the user selects the request type as “Leave”, the “CustomRequestID” column will be updated with “LVREQ(ID Column)”.

If the user selects the request type as “Expense Claim”, the “CustomRequestID” column will be updated with “EXPREQ(ID Column)”.

If the user selects the request type as “Travel”, the “CustomRequestID” column will be updated with “TRVREQ(ID Column)”.

Notes:

• This is just proof of concept we have created the request type and custom request ID – you can tailor this logic for your actual needs.
• Above custom ID generation PoC, we have created using the Power Automate, in the below section, we will explain the Power Automate.

Generate unique ID in SharePoint list using Power Automate step by step

This is the Power Automate flow which generates a unique ID in the SharePoint list based on the request type selection.

Now, let’s explain each action separately.

Step 1: When an item is created or modified

First, add the trigger action “When an item is created or modified” in the SharePoint list.

Pass the below parameters:

• Site Address – select your site URL
• List Name – select your list name

Step 2: Get the item from the Request ID list

Add a “Get Item” from SharePoint action.

Pass the below parameters:

• Site Address – select your site URL
• List Name – select your list name
• Id – Select the “ID” column from the “When an item is created or modified” trigger action.

Initialize variable in power automate – Generate unique ID in SharePoint list

Initialize the below two string type variables:

• CustomRequestID – this will hold the custom request ID value.
• RequestType – this will store the request type value from the “RequestType” column of “Request IDs Generation” list.

Set variable in power automate – Power automate get specific item from SharePoint list

Here we need to get the request type column value from the SharePoint list and store it in the request type Power Automate variable, for this, we need to use the set variable action of Power Automate.

Syntax to read a single specific column value from the SharePoint list in Power Automate:

body('Your_Get_item_Action_name')?['Column name for which you want extract value']

Example:

body('Get_item_from_Request_ID_list')?['RequestType/Value']

 

Power Automate get specific column values from the foreach loop (Apply to each)

The above example is for the single item that is outside the loop. However, if we want to read a specific column value from the loop, we need to follow the below syntax:

Syntax:

items('Your_Apply_to_each_control_name')?['YourColumnName']

Example:

items('Apply_to_each')?['Title']

Notes:

• Inside the body, we need to replace any spaces with the underscore(_) for the action name.
• If you have a choice column in the SharePoint list we need to add “/Value” after the column name, for example: [‘RequestType/Value‘] otherwise for the single line of text type of column, the column is enough.
• Need to construct the value from the Expression tab as shown below.

Set variable in power automate

Best way to extract value from Power Automate Get Item/Items action

As in the above example, sometimes we need to pass the column or column name with the “/value” node after the question mark in the body. Here we need to get the exact node position by referring to the Get-Item/Items action result output JSON.

Get Item JSON output example:

For a single item add or update, we will get the below JSON values from Power Automate Get Item action as result:

{
"@odata.etag": "\"1\"",
"ItemInternalId": "1",
"ID": 1,
"Title": "Leave request",
"RequestType": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 0,
"Value": "Leave"
},
"RequestType#Id": 0,
"Modified": "2022-10-15T13:45:21Z",
"Created": "2022-10-15T13:45:21Z",
"Author": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
"Claims": "i:0#.f|membership|global-sharepoint2020@globalsharepoint2020.onmicrosoft.com",
"DisplayName": "Global SharePoint Diary",
"Email": "Global-SharePoint2020@globalsharepoint2020.onmicrosoft.com",
"Picture": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_layouts/15/UserPhoto.aspx?Size=L&AccountName=Global-SharePoint2020@globalsharepoint2020.onmicrosoft.com",
"Department": null,
"JobTitle": null
},
"Author#Claims": "i:0#.f|membership|global-sharepoint2020@globalsharepoint2020.onmicrosoft.com",
"Editor": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
"Claims": "i:0#.f|membership|global-sharepoint2020@globalsharepoint2020.onmicrosoft.com",
"DisplayName": "Global SharePoint Diary",
"Email": "Global-SharePoint2020@globalsharepoint2020.onmicrosoft.com",
"Picture": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_layouts/15/UserPhoto.aspx?Size=L&AccountName=Global-SharePoint2020@globalsharepoint2020.onmicrosoft.com",
"Department": null,
"JobTitle": null
},
"Editor#Claims": "i:0#.f|membership|global-sharepoint2020@globalsharepoint2020.onmicrosoft.com",
"{Identifier}": "Lists%252fRequest%2bIDs%2bGeneration%252f1_.000",
"{IsFolder}": false,
"{Thumbnail}": {
"Large": null,
"Medium": null,
"Small": null
},
"{Link}": "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD/_layouts/15/listform.aspx?PageType=4&ListId=ecfbc6a1%2D9325%2D4b4c%2Da674%2D2a4199350599&ID=1&ContentTypeID=0x01000032B54E8D16354D92B914B3ADDE74FB009D1899444013834FBFEFEC2D884BDBDE",
"{Name}": "Test1",
"{FilenameWithExtension}": "Test1",
"{Path}": "Lists/Request IDs Generation/",
"{FullPath}": "Lists/Request IDs Generation/1_.000",
"{HasAttachments}": false,
"{VersionNumber}": "1.0"
}

In the above JSON, we can visualize the result to format the right value for a specific column.

For our demo, we have constructed the value as “RequestType/Value” after the body in the set variable action because of the below:

 "ID": 1,
"Title": "Test1",
"RequestType": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 0,
"Value": "Leave" 
}

Note:

• In the above JSON, the ID, Title column has one-to-one direct value but for the “RequestType” it has a separate “Value” node as this is a choice column.

If else condition in power automate using switch case

In this section, we will learn how to use the if else condition in Power Automate using a switch case.

In our demo, we have added a switch control to handle the if else condition for the request type condition.

In the switch control, we have added three cases:

• Case 1 for “Leave” – if the request type is “Leave” this case will be executed.
• Case 2 for “Expense claim” – if the request type is “Expense claim” this case will be executed.
• Case 3 for “Travel” – if the request type is “Travel” this case will be executed.
• Default case – if none of the conditions are satisfied then this case will be executed.

Each case has two actions:

• Set variable for custom request id and
• Send an HTTP request to SharePoint action to update the “CustomRequestID” column in the SharePoint list.

Construct a custom request ID when the “RequestType” in the SharePoint list is “Leave”.

String Concat function power automate

In order to generate a custom request ID, we have used the string Concat() function of Power Automate.

Concat function example when the “RequestType” is “Leave”.

concat('LVREQ',triggerOutputs()?['body/ID'])

Inside the Concat function pass the below when the request type is “Leave”:

• LVREQ,
• ID column – select the ID column from the trigger output of “When an item is created or modified”

Concat function example when the “RequestType” is “Expense Claim”.

concat('EXPREQ',triggerOutputs()?['body/ID'])

Inside the Concat function pass the below when the request type is “Leave”:

• LVREQ,
• ID column – select the ID column from the trigger output of “When an item is created or modified”

Concat function example when the “RequestType” is “Travel”.

concat('TRVREQ',triggerOutputs()?['body/ID'])

Inside the Concat function pass the below when the request type is “Leave”:

• LVREQ,
• ID column – select the ID column from the trigger output of “When an item is created or modified”

After that add the “Send an HTTP request to SharePoint” action to update the “CustomRequestID” column for each case scenario, in the below screenshot for example we have shown the “Leave” request scenario, rest other two scenarios are the same logic.

Parameters in Send an HTTP request to SharePoint for the update operation

Site Address: Your site URL

Method: POST

URI:

_api/web/lists/GetByTitle('Your list title')/items('Item ID for which you want to update the value')/validateUpdateListItem 

Body:

{
"formValues":[
{
"FieldName": "YourCustomRequestIDColumnInternalName",
"FieldValue": "The value that you want to pass for the update"
}
]
}

For how to update SharePoint single list item using Power Automate, refer to our previous article – Best way update list item in SharePoint using Power Automate – Microsoft 365

Summary: Generate unique ID in SharePoint list using Power Automate

Thus, in this article, we have learned the below with generating the unique ID in SharePoint list using Power Automate:

• Learned how to generate custom unique ID or Request ID in SharePoint Online list using Power Automate.
• Learned how to use the if else condition in Power Automate using the switch control.
• How to the string Concat function in Power Automate.
• How to get a specific item from the SharePoint list using Power automate.
• How to use initialize and set a variable in Power Automate dynamically.

See Also: Power Automate Tutorial

You may also like the below Power Automate tutorials:

• Best way update list item in SharePoint using Power Automate – Microsoft 365
• 3 ways instantly disable Action in Power Automate
• Instantly in 2 steps Copy and Paste action in Power Automate
• Discover exception handling in power automate using Try Catch Finally Scope – in 3 steps instantly
• Instantly Solved: Error from token exchange: Bad Key authorization token in Power Automate – Microsoft 365
• Scheduler Power Automate: Move Old documents in SharePoint Automatically using Scheduled Cloud Flow
• Conditional trigger power automate: Trigger Power Automate (Flow) based on condition in SharePoint Online – Verified
• Power Automate Send Email: Difference between “Send an email notification (V3)” and “Send an email (V2)”
• HTML Table in Power Automate: Learn HTML table formatting using Microsoft Power Automate step by step
• In just 2 marvelous steps export import power automate flow quickly
• Validate is it really Power Apps = Low Code No Code? Power Apps low code no code
• Get started with Power Automate

 

About Post Author