Automatic ticket numbering by location
Hello. I worked with someone on a previous discussion, but for some reason cannot locate it now. I have one data base file with the work orders from many different community locations. I wanted a solution for the Work Order Number to be a combination of the community abbreviation, the year, and then the number of work order for that year. This worked great until 2020 rolled around and I can see now that everything works except for the last part; it did not restart the numbering for this year. Because of this, it changed the year within the work order, but it simply continued on the numbering from 2019.
I have provided the formula below. Within it, the "Community Code" is the abbreviation and the "Request Date" is the date a request was submitted. I've also included a picture in case my explanation of the issue doesn't suffice.
=[Community Code]@row + "-" + YEAR([Request Date]@row) + "-" + RIGHT(10000 + COUNTIF([Community Code]$1:[Community Code]1, [Community Code]@row), 4)
Any help with this is greatly appreciated.