Unanswered

Unique ID using Year and Week

(0) Share

Report

Posted on by OwenW

I am trying to create a flow for a SharePoint List where each entry has an ID based on the Year, Week, and an incrementing number which is 1 bigger than the number of other rows that share the same Year and Week. I.E. entries in 2023 week 1 would be 2023-01-0001, then 2023-01-0002, then for the next week the counter would start again so it would be 2023-02-0001, then 2023-02-0002 etc.

I have created a column in the SharePoint list which extracts the Year and Week from the created date in the format Year-Week.

Using this column I have tried to make a flow as follows:

So my thinking behind it is that it gets all the items, filters for only the items where the Year-Week column matches the new entry, counts how many rows there are in this filtered array, and then puts the Year-Week number followed by the count +1 in a new column.

The parts I am struggling with is the Filter and the Length part.

For the Filter part, am I correct in thinking I would do

Where the first Year-Week is from "When an item is created", and the second one is from "Get items".

Then I'm not sure how to write the formula for the Compose part for it to count the number of rows from the Filtered Array, and +1 to it. I think I'd use the 'length' expression but don't know how to specify to use the results of the Filter section.

This is my first flow so am very lost!

Any help would be appreciated, thanks 😁

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (2)

Verified answer

OwenW 17 on at

Like (0)

Report

I managed to figure it out (albeit in the format of 2023-2-1 without the zeroes in front of the week or increment number). I was very close, the Filter Array part was correct, but on the compose bit this is the formula I did this:
length(body('Filter_array'))

Thought I should post this comment in case anyone else needs help with this too!

If anyone could help with getting it in the format 2023-01-0001 that would be great...

Was this reply helpful? Yes No
Verified answer

ManishSolanki 15,091 Super User 2025 Season 2 on at

Like (2)

Report
Hi @OwenW

Pls make use of formatNumber formula to add leading zeros. In the given example, it will convert length of 'Get items' into 4digit format:
formatNumber(length(outputs('Get_items')?['body/value']),'0000')
Pls modify the above formula as per your need.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report
If you want to get the leading zeros in front of the file number, you will have to do some string gymnastics to compare the length of leading zeros with combined string and then take the substring of that.

I'm making an example that shows an outline of what needs to happen, but you can probably squeeze it into one long expression once you get it organized.

I'm assuming you already know how to get the file number, so from your example 2023-2-1, the file number is 1 the last digit.
I'm also assuming you know the amount of digits you want the file number to be, so 2023-01-0001, the file number 0001 is 4 digits.

In my example below, my file number is 44. I want it to be 5 digits 00044.

I have a variable of leading zeros which is five digits '00000' since that is what I want my final result to be.
I have a variable of file number '44' which will be my example file number.

If I concatenate the two strings of leading zeros and file number concat('00000','44') the result will be '0000044' which is 7 digits.

I can subtract the initial number of digits for my leading zeros length('00000') which is 5 from the length of the concatenated length(concat('00000','44')) which is 7. So it will be 7-5 = 2

If I take the substring of '0000044' using the difference of 2, substring('0000044',2), the result is '00044' output.

Below are the steps, you can test and adjust expressions until you get the format you want in your output.

Format File Number

The Compose expression in the example is:
substring(concat(variables('LeadingZeros'),variables('FileNumber')),sub(variables('LengthofConcat'),variables('LengthLeadingZeros')))

The compose expression only using the LeadingZeros variable and FileNumber variable is:
substring(concat(variables('LeadingZeros'),variables('FileNumber')),sub(length(concat(variables('LeadingZeros'),variables('FileNumber'))),length(variables('LeadingZeros'))))

Good luck!

Was this reply helpful? Yes No
OwenW 17 on at

Like (0)

Report

Thanks, this sorted the formatting of the final digits!

To format the 'Week' column, i used the following code in a SharePoint calculated column:

=Text(Concatenate(Text(If(Int(([Created]-Date(Year([Created]),1,1)+(Text(Weekday(Date(Year([Created]),1,1)-1),"d")))/7)=0,52,Int(([Created]-Date(Year([Created]),1,1)+(Text(Weekday(Date(Year([Created]),1,1)-1),"d")))/7)),"0")),'00')

Was this reply helpful? Yes No