I have a requirement to reset the Tracking# field of a list to 1 at the first record for a new fiscal year. The current formula for the Tracking# field is "="FY"&(YEAR(Created+92)&REPT("0",6-LEN(ID))&ID)", example "FY2018000001" would be the first record for fiscal year 2018. Fiscal year is 1 Oct to 30 Sep. I would also like to reset the Tracking# to eliminate all the test records auto generated in the ID field, to get a visible count for the user to see how many records have been entered for the year. I can see being able to edit the field to eliminate gaps, but it needs to calculate the next value in the Tracking# field. I believe it would need to use a function for the highest value in the ID field and then look at the corresponding Tracking# field and then add 1, or making another field to keep track of the next value in the tracking field and then concat the fields to make the Tracking# field
this is an example of the values for the ID and Tracking# fields
| ID | Tracking# |
| 26 | FY2017000001 |
| 90 | FY2017000002 |
| 91 | FY2017000003 |
| 103 | FY2017000004 |
| 122 | FY2017000005 |
| 123 | FY2017000006 |
| 124 | FY2017000007 |
| 125 | FY2017000008 |
| 126 | FY2017000009 |
| 127 | FY2018000001 |
| 128 | FY2018000002 |
| 129 | FY2018000003 |
| 130 | FY2018000004 |
| 131 | FY2018000005 |
| 132 | FY2018000006 |
| 133 | FY2018000007 |
| 134 | FY2018000008 |
| 135 | FY2018000009 |
Thank you in advance