Answered

Yearly running number

(0) Share

Report

Posted on by Kustomize

Hello,

I have a Sharepoint list form which I customised with PowerApps. One field contains a unique ID that is automatically generated, and the format was specified by my users to be YY#### where the first 2 digits are the current year followed by a 4 digit running number (which is sufficient) to keep track of how many items were submitted in that year.

My current formula is this:

Right(Year(Today()),2)*10000+Last('MyList').ID+1

which gets the job done for this year, but I was wondering what's the best approach to reset the running number to 1 next year?

Categories:

Building Power Apps

All responses (5)

Answers (1)

Sort by

Verified answer

Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

@Kustomize

This should do it

Right(Year(Today()),2)*10000+

First( Sort( Filter('MyList',

StartsWith( UniqueIDColName, Right(Year(Today()),2) ) ),

ID, Descending )

).ID+1
Kustomize 11 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous,

The formula looks good, I've updated my field for now but I have received these 2 warnings:

I anticipate around 7000 entries per year for this list, can filter and sort be able to handle such large datasets? From what I have read, including first till make the warning moot. But I wonder if it's limited to 2000 records, how would the formula know that latest item is included in the sort?
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@Kustomize

So, after some digging I found you get this warning because you are using a Number column with StartsWith, see here

https://docs.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-operations-for-sharepoint

To get around this you need to create a new UniqueID column of type Text, with the same data.

I also had to create a variable to convert the first 2 digits of the Year into Text, then use that inside StartsWith.

A way to test that this works is to go

File —> Settings —> General and adjust the Data row limit to a value less than the number of records in your current List. You should still get the correct new UniqueID.

Let me know if I need yo explain this further and with code
Kustomize 11 on at

Like
a
(1)

Report
Copy link

Link copied!

Yup seems like the warning went away. I'll accept the solution for now though I can only really verify in January whether it'll reset.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@Kustomize

You could do a test if you like.

- Add a date picker

- Set the OnChange to a variable eg myDate

- Select a future date with the date picker eg 20/02/2022

- where Today() appears in the formula, replace with myDate

- run your Patch

Should work as expected