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?
Trying to re-learn FMP.