1 Reply Latest reply on Nov 14, 2011 9:00 AM by philmodjunk

    Creating file numbers from calculated field (problem)



      Creating file numbers from calculated field (problem)


      I'm trying to create a database to help manage my criminal law practice. I used FMP years ago (like in the early or mid 1990s) to create simple databases, but haven't used it since at least then. 

      My paper files are assigned numbers based on the final two digits of the year, plus an incrementing three-digit number, followed by a letter to indicate which paper file we're talking about if there's more than one. (To give an example, a client may have enough paper to require two files, which would be, respectively "11038A" and "11038B." This would mean the client file was created in 2011; the client was the 38th client of the year; and, as noted, there are two files, labeled "A" and "B.") 

      For various reasons I won't bother explaining here, there are some "mistakes." File numbers are supposed to be assigned only once, but some old files exist where, for example, 07110A designates a file for "Bob Jones" but "Sally Doe" got assigned the same number. Not supposed to happen, but it did. 

      For the above reasons, I would like to create a field where a "suggested" file number is auto-generated, but which can be changed, if it needs to be. I already figure "FileNumber" isn't going to be able to function as a key field; I just want it to correlate to the paper files, so I can find them in the archives. 

      The main thing throwing me right now is not knowing how to make a number sequence start over at the beginning of a year and start generating numbers like "001" and then increment by one for each new client. 

      Is there a function for that? If not, any hints on how to create this number? 



      Rick Horowitz

      Trying to re-learn FMP. 

        • 1. Re: Creating file numbers from calculated field (problem)

          While you can open Manage | Database | fields and manually reset the "next serial value" setting for an auto-entered serial number, you can also use Set Next Serial Value in a script to reset the serial number setting. A script can run every data from either a server schedule or each time you open the file that can check the date and when it's the first business day of a new year, resets this file number serial, back to zero.

          Similar scripting can set the next value back one value so that you can change a new record's serial ID to a "B" format and then run such a script to avoid a gap in your numbering series if that is a concern for you. I definitely recommend that you specify a Unique Values validation on this field so that you can catch mistakes that would otherwise result in issuing the same file number for two different records in your database.

          You can include leading zeroes as part of the serial number field's next value setting if the field is of type text instead of number.