Beginner needs help creating serial number

[Josh Lewis]

Hi,

I've been using FM for about 1.5 years, but have just recently started developing my own layouts for my new photography business.

I'm trying to create a unique serial/record # for each new record, but can't figure out how. I want the number to start with a backwards date, like this... 081106 (for November 6th, 2008), followed by a one digit serial #. I'd like the serial number to start back at 1 each day, since I doubt I'll ever need to create more than 10 new records in one day.

Someone told me I could have a calculation that said "if day is > previous record's day, then serial # = 1" to reset the serial number each day. I don't know how to create a calculation to express this.

I've been trying to create the calculation, but I keep getting a message saying "The specified field cannot be found."

Thanks for any help.

[Vaughan]

I'm trying to create a unique serial/record # for each new record, but can't figure out how. I want the number to start with a backwards date, like this... 081106 (for November 6th, 2008), followed by a one digit serial #. I'd like the serial number to start back at 1 each day, since I doubt I'll ever need to create more than 10 new records in one day.

Trust me, you really don't. The amount of trouble that such "meaningful" serial numbers cause is unbelievable.

If you still want to, at least use the full century 2008 and not just 08.

Create a single record "preferences table" to store the two parts of the serial number... lets call them the "day" and "record".

The general process is: each time a new record is made, open the prefs record and check that the "day" number coincides with the current date. If it does then increment the "record" number and remember it, close the prefs record; If it does not coincide then set the "day" field to the current day, and set the "record" to 1 and remember it, close the prefs record. Use the remembered number for the serial number.

Doing all of this will entail scripting and controlling the record creation process.

What you have to think about (ie, the devils in the detail)

You create a record for a day (no 1) then another (no 2) then delete one of them. Are you happy for there to be gaps in the numbers? If you create record 2 then delete it immediately, should the next record be 2 or 3?

What if the database record is created a day or two after the photography, what should the serial number be? What if you need to back enter a record, will the serial numbers now be out of order? and if so how will the system know what the next number should be for last Tuesday?

But wait there's more: the battery in your computer goes flat and the clock resets to 1/1/1970 but you add lots of record before realising it. Or even more subtly, what if the clock is a few minutes fast and you create a record just before midnight? For bonus points, there are two people using the database around midnight, one clock is fast and the serial increments. The other is slow and make a new record after the first, but the date is different so it resets the day number and you end up with two no 1s for the same day, plus for the next day when it resets again later.

Many of these problems can be solve or prevented with some clever scripting, but they are all traps for the unwary and they need to be accounted for.

If you decide to implement the fancy serial number, use it *only* for display. Use a plain auto-entered serial number as primary keys for relationships etc.

[Vaughan]

The calculation would be:

Year( Get( CurrentDate ) ) &

Right( "0" & Month( Get( CurrentDate ) ) ; 2 ) &

Right( "0" & Day( Get( CurrentDate ) ) ; 2 )

The Right() function is required to add the leading zeros onto the month and day numbers.