Unique Code System for Records

[apt]

Hi, I'm new to this forum and hope I'm posting in the correct place.

I'm in the process of converting our paper based record system where I work to a FileMaker database but have come across a problem when trying to replicate the way we assign unique codes to each client/record. Currently we assign what we refer to as a T No, 235G for example where 235 is the number of records made from the first of Jan that year and the G is the year (G currently =2005). My problems is how do I recreate this system using FileMaker.

What I want to be able to figure out is how I can make FM assign a Letter (G for example) to a year (2005) and then in 2006 make it change the letter to the next letter in the alphanbet (H) automatically and so on. I think I can sort out the numbering system using a serial number field and then I can simply combine the 2 fields together to create the T No.

I'd also want to be able to do a check to see if that T No already existed.

A bit of a tall order I know but if someone could put me in the right direction I would really appreciate it.

Regards

A.Thompson

Edited September 8, 2005 by Guest

[comment]

Why bother to recreate the old numbering scheme at all? The best way to ensure codes are unique is to use an auto-entered serial number, which will NEVER be changed, reset or otherwise modified. This number should have no meaning. It is used by the solution - not by the user.

If you want to DISPLAY - for the user's benefit - a more meaningful code, you can append the yearly letter (although I fail to see why "G" would be more meaningful to a human that plain "2005"). To get the letter, use:

Middle ( "ABCDEFGHIJKLMNOPQRSTUVWXYZ" , Year ( yourdate ) - 1998 , 1 )

"yourdate" can be the record's creation date field, or Status (CurrentDate). If the latter, use a text field with auto-entered calculation.