InfoPath 2010 Max ID Field value and reset Tracking# to 1 if first record for fiscal year

BrianAdams — Tue, 13 Jun 2017 16:37:22 GMT

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

Newbie Questions

InfoPath 2010 Max ID Field value and reset Tracking# to 1 if first record for fiscal year