AnsweredAssumed Answered

Calculating Date + Max Record Number

Question asked by ChrisNixon on Jan 8, 2013
Latest reply on Jan 8, 2013 by ChrisNixon


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?