Create unique ID, based on date

[MircoKunz82]

I have a Sharepoint list in which employees write personnel applications using Power Apps.

One of the fields is the application date, which is set automatically by Power Apps.

Now, when a new entry is created in the list, I want to use Power Automate to automatically generate a unique ID, which is composed as follows:

YYYY ID, example:

2023-1
2023-2
...
2024-1

Now comes the special part of my request. I have an old list, which was previously kept using Excel. This also includes applications from previous years, which I need to migrate.

Now this ID should also be generated during the migration. For example, today 02.03.2023 I enter an application in the list whose application date is 22.11.2022 as an example.

Now I want to see what the last ID was in 2022 and then number it consecutively.

So it could be that there is an application 2022-45, and the new application of 22.11.2022 should then be numbered 2022-46.

Who can help me?

[grantjenkins · Accepted solution]

@MircoKunz82 I've built a flow to handle new items being created and getting a unique ID as per your requirements. When you move the other older items across, they should automatically get a unique ID generated as part of this flow. Hopefully this is what you're looking for.

For this example, I'm using the following List.

The full flow is below. I'll go into each of the actions.

When an item is created will trigger when we add a new item.

Because we can create/upload multiple items, we don't want multiple flows running at the same time, otherwise we might get duplicate unique IDs generated. To avoid this, we can turn on Concurrency Control on our trigger and set the Degree of Parallelism to 1 so the trigger will only run once the previous flow has completed (avoiding any overlapping).

Get items will return the last item that was created in the same year as the current item. It will order the results in descending order based on Application ID, set to only return 1 item, and uses the following expression for the Filter Query to exclude the current item and get items within the year of the current item's Application Date.

//Filter Query
ID ne @{triggerOutputs()?['body/ID']} and ApplicationDate ge '@{formatDateTime(triggerOutputs()?['body/ApplicationDate'], 'yyyy-01-01')}' and ApplicationDate le '@{formatDateTime(triggerOutputs()?['body/ApplicationDate'], 'yyyy-12-31')}'

//Order By
ApplicationID desc

//Top Count
1

Initialize variable creates a variable called Number of type Integer and sets the initial value to 1. This number will end up being appended to the current year to give us the unique ID.

Condition checks to see if any items were returned from Get items. If no items were returned, then we can assume there were no existing items in the List from the item's Application Date year, so we use the current number set for our Number variable. However, if there was an item returned, we get the number part of the unique ID, add 1 to it, then set that as the new value of Number. The expression used in the Condition is below:

length(outputs('Get_items')?['body/value'])

The expression to get the number part of the unique ID and add 1 to it is below:

add(int(slice(first(outputs('Get_items')?['body/value'])?['ApplicationID'], 5)), 1)

Update item uses the ID from our trigger to get the current item and updates the Title (from our trigger) and the ApplicationID using the following expression:

concat(formatDateTime(triggerOutputs()?['body/ApplicationDate'], 'yyyy'), '-', variables('Number'))

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[MircoKunz82]

@grantjenkins Works perfectly. Thank you.

[Martin_V]

Hi,

Does someone ran into this error with the solution of @grantjenkins   :

InvalidTemplate. Unable to process template language expressions in action 'Get_items' inputs at line '0' and column '0': 'The template language function 'parseDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#parsedatetime for usage details.'.

[LucaNeedHelp]

Hi @Martin_V ,

i have the same issue. Do you have another way, or know the what is wrong?