serial # that starts over each month

[anna f]

I have donor numbers (format SC-yy-mm-xxx where xxx is a serial that starts over each month), ex. SC-04-03-087, that are put in a field that is a pop up (in case the referral is NOT a donor). I have automated the first sections of the number but cannot figure out how to make a serial # start over each month with 001. When the user clicks the "Convert to Donor" button, the field is populated with the donor # (except for the last three digits!). If the referral is not a donor, the user clicks in the field and chooses a deferral reason. I guess I can make the user manually enter the last three digits but to cut down on errors, I would rather automate it. Any suggestions?

Version: v5.x

Platform: Windows XP

[Vaughan]

"Any suggestions?"

You're not going to like my preferred suggestion: forget it. Make the serial number meaningless.

Any other solution will be complex and may allow the possibility of incorrect or duplicate serial numbers being generated, especially in a multi-user environment.

I can think of two methods:

1) The serial number generating script checks the month of the last record in the database and compares it to the current month. If they are different it resets the serial number to whatever is appropriate. You may need to upgrade to FMP 6 or later to get the necessary serial number changing script steps to make this work. You'll also need to make sure that all the user's computer clocks are synchronised, otherwise the serial numbers may be reset at inappropriate times. (It's the last day of the month. A user makes a record at 12:01 am so the serial number will reset to the next month. A few moments later another user whose clock is a few minutes slow makes a record at 11:59 pm so the serial number is reset again back to the previous month starting at 001 which will make duplicate serial numbers. When the next user makes a record the serial number resets again to the next month, so now the first few serial numbers of this month may also be duplicates. There are probably ways to trap for and prevent this from happening, but you can see how the complexity of the system starts to increase dramatically to make it robust and reliable.)

2) At the start of each month the sys-admin manually changes the serial number in the field definition (this solution will work for all version of FMP) They have to remember to do it.

[stanley]

Anna:

I have a client with a similar numbering system - for consistency's sake, when switching over from a paper system, they wanted to keep the system. However, I do NOT use their "serial number" internally in FileMaker (for relationships, etc) - it is just a cosmetic for the user, really.

You can make a self-join based on your yy-mm data, then when creating your "serial number" (remember to always use a REAL serial number for each record as well) you can set the new record's "xxx" value to "Max(your self-join:xxx)+1", then build the serial number as a calculation...

However, there are pitfalls there, no matter what, as Vaughan points out.

-Stanley