Assigning Sequential IDs to Records in Dataverse Using Power Automate

Introduction

To create a Power Automate flow that assigns sequential IDs to items when a new row is added to Dataverse. We can leverage the existing auto number field, which automatically generates IDs for new records. However, this only applies to newly created records and does not allow for assigning IDs to existing records. This flow will allow us to manually assign sequential IDs to existing records while ensuring that new records receive a unique, sequential ID.

Requirement

To create a Power Automate flow that assigns sequential IDs to items when a new row is added to Dataverse.

Solution

Step 1. Add a new column to the table called "Project ID" (Datatype: a single line of text) [e.g., Values could be P0001, P0002, etc.].

Step 2. Choose a trigger " When a row is added, modified or deleted".

Step 3. Choose "List rows" [ new_projectids is the logical name of Project ID coumn]. We are attempting to retrieve the single record with the highest ID.

Step 4. Initialize a variable to store the next project ID.

Step 5. Once the record with the highest ID is retrieved, the numeric part of the ID needs to be extracted (e.g., from P0001, extract 0001 to increment it).

Use "Compose" Action.

Expression: substring(last(body('List_rows')?['value'])?['new_projectids'],1,sub(length(last(body('List_rows')?['value'])?['new_projectids']),1))

Step 6. Increment the ID using the "Compose" action.

Expression: add(int(outputs('Compose')),1).

Step 7. Format the number to 4 digits (ex: 0000) using the "Compose" action.

Expression: formatNumber(outputs('Compose_2'),'0000').

Step 8. Concatenate the formatted 4-digit number with "P" using the "Compose" action.

Expression: concat('P',outputs('Compose_3')).

Step 9. Use the set variable to set the concatenated output to the "NextProjectID" variable.

Step 10. Use the "Update row" action to update the Project ID to the record.

Summary

This solution outlines how to use Power Automate to assign sequential IDs to records in Dataverse. By creating a flow, sequential IDs can be manually assigned to existing records from Dataverse, while ensuring new records receive unique, sequential IDs from Flow. This approach guarantees consistent and orderly identification of all records in the system.