Calculating Date + Max Record Number
Hey guys, this is my first post in this forum so I apologize for my lack of knowledge when it comes to FM. Currently we are running FMServer Advanced 11 using single-machine setup, and FMPro Advanced 11 for developing/editing existing databases.
My field is called "Case Number:" and currently it displays a hard-coded Year, and the current record count plus 1 (max_serial_no + 1). The field (zd_CaseNumber) is a Calculated Value and the calculation is as follows...
zd_CaseNumber = "2013-" & If(IsEmpty(max_serial_no); 1; max_serial_no + 1)
My problem lies in when a new year occurs, we need the database to auto update, and reset the max_serial_no to 0. For instance, when the New Year rolled over to 2013, the first record then created would be "2013-1", then "2013-2", etc.
Can anyone assist in making this possible?