How to Create Unique ID like 2021-1 in SharePoint list using Power Automate flow

[Anonymous]

Hi,

I'm having the SharePoint list in which the data is updating through flow, i need to update one column based on Fiscal Year . if fiscal year starts from October,   fiscal year and ID should get reset  (Ex - 2022-1, 2022-2,etc...)

Thanks,

[Expiscornovus]

Hi @Anonymous,

Just to double check. The format is FiscalYear-ID, correct? And not FiscalYear-FiscalMonth?

If the first is the format you want you could use an expression like in the example below:

In this example I assume that October is the starting month. Otherwise you need to change the number in the greaterOrEquals function to another month number.

if(greaterOrEquals(int(utcNow('MM')), 10), concat(add(int(utcNow('yyyy')),1), '-', triggerOutputs()?['body/ID']), concat(utcNow('yyyy'), '-', triggerOutputs()?['body/ID']))

[Anonymous]

Hi@Expiscornovus 

Thanks for your solution,  it's working fine

i need one clarity that if next fiscal year starts does the id  Ex.2021-123  will get reset to  2022-001 Please confirm

Thanks,

[Expiscornovus]

Hi @Anonymous,

Apologies, I missed that part of your requirement. No, the current proposed solution doesn't meet that requirement.

It just concatenates the Microsoft ID with the Current fiscal year. So, taking the examples of your screenshot the IDs would be:

2021-687
2021-677
2022-661
2022-679

If you want to reset you probably want to introduce a couple of additional columns to make this a bit easier. I would add a YearID and FiscalYear column to your list (both are of type number in my example). With a Get Items you could retrieve the latest item of the current Fiscal Year. Based on that you can determine the correct YearID and update the item.

Below is an example of that approach.

1. Add a Initialize a variable of type Integer, I called it NewRegNumber.

2. Add a Get Items action with the following filter query:

FiscalYear eq @{if(greaterOrEquals(int(utcNow('MM')), 10), add(int(utcNow('yyyy')),1), utcNow('yyyy'))}

I also sorted on Created descending and used a Top Count 1.

3. Add a Condition. Check if a the Get Items results are empty. If this is the case this means you have to reset and it is a new Fiscal Year.

4. Add a Set a variable action in the If Yes. Set it to 1 because it is a reset.

5. Add a second Set a variable action. Use the following expression to get the latest YearID from the get items:

int(outputs('Get_items')?['body/value'][0]['YearID'])

6. Add an increment variable to increase the YearID by one.

7. Add an Update item action outside the condition action.

Use the following expression to update the New Registration number:

if(greaterOrEquals(int(utcNow('MM')), 10), concat(add(int(utcNow('yyyy')),1), '-', string(variables('NewRegNumber'))), concat(utcNow('yyyy'), '-', string(variables('NewRegNumber'))))

Use the variable for the YearID field

Use the following expression for the FiscalYear field:

if(greaterOrEquals(int(utcNow('MM')), 10), add(int(utcNow('yyyy')),1), utcNow('yyyy'))

[JohnAxios]

@Expiscornovus Hey, new user here and I was just wondering what you used in the "undefined" expression in the Condition? Also, do you know of any way to test the whole flow to make sure it resets without waiting for the end of the month 😄Thanks so much.

[tamim0326]

@Expiscornovus i have the same question as @JohnAxios.  i am looking for the expression for "undefined".  can you help please. 

[Expiscornovus]

Hi @tamim0326 & @JohnAxios,

That value is 'true' written/added as an expression.

[tamim0326]

Thank you