cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kustomize
Frequent Visitor

Yearly running number

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

@Kustomize 

 

This should do it 

 

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

 

First( Sort( Filter('MyList',

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

ID, Descending )

).ID+1

 

 

 

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

@Kustomize 

 

This should do it 

 

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

 

First( Sort( Filter('MyList',

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

ID, Descending )

).ID+1

 

 

 

 

Hi @Anonymous,

 

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

 

Kustomize_0-1639355280106.png

Kustomize_1-1639355341063.png

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?

Anonymous
Not applicable

@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-ope...

 

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
Frequent Visitor

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.

Anonymous
Not applicable

@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 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,953)