Serial Number ID

[Darrell]

I want to assign and ID number to every record in the database. I want it to look like this 04202003001 04202003002...etc. The first 8 numbers represent the date and I need them to change with the date everyday to reflect the date. The last 3 numbers are just sequencel and I would like them to reset veryday back to zero So I can start the next days record looking like this 04212003001, 04212003002..etc. How do I make this happen? Thanks in advance.

[LiveOak]

There is probably a simpler way, but it hasn't occurred to me at present.

First create fields:

FirstPart(calculation, text) =

Right("00" & Month(Status(CurrentDate)), 2) & Right("00" & Day(Status(CurrentDate)), 2) & Year(Status(CurrentDate))

SecondPart(number)

SerialNum(text) <--- This must be text or you will lose the leading zeros!

Then create a self relationship:

SelfByFirstPart with FirstPart <--> FirstPart

Finally the new record script will look like:

New Record/Request

SetField["SecondPart", "Max(SelfByFirstPart::SecondPart) + 1"

SetField["SerialNum" , "FirstPart & Right("000" & SecondPart, 3)"]

The only downside of this approach is that if you delete the highest serial numbered record for the day, the number will be reused by the next record created.

OBTW, I very much dislike using serial numbers that "mean things" to people. I think serial numbers should be unique numbers, period. Every time I see record numbers set up to mean something to the humans, it later causes trouble because then they want to "edit" the numbers at a later date (because they "mean something") and that breaks relationships to records in other files. I would keep numbers used by humans which "mean things" and unique serial numbers used to connect related files completely separate. For instance, "this order really happed yesterday, we have to change the record number", etc.

-bd

[CobaltSky]

Using a variation of the same principles, I suggest that you:

1. Create a date field called 'Created' that auto-enters the record creation date, and a self-join relationship called 'RecordDate' that matches the 'Created' field to itself.

2. Create a text field and define it to auto-enter a calculation with the formula:

Right("00" & Month(Status(CurrentDate)), 2) & Right("00" & Day(Status(CurrentDate)), 2) & Year(Status(CurrentDate)) & Right("000" & NumToText(1 + Count(RecordDate::Created)), 3)

...and in the auto-enter calc window, make sure you turn *off* the checkbox option at the lower right for 'Do not calculate if all referenced fields are empty'.

That will do it.

This method has a couple of advantages over that suggested by LiveOak. It requires only two fields and one relationship as against the three fields, one relationship and one script required by LiveOak's method. Moreover it will work correctly no matter how new records are generated (ie it doesn't require that records be generated by script in order for them to be correctly serialised).

In addition to the concerns raised by LiveOak, there is one other that you should consider. If your solution is multi-user, with either solution there is a risk that if two users create a new record at exactly the same instant, duplicate numbers will be issued (since neither will take account of the record being created by the other user when determining the number to be allotted).

If this is a concern, then there are ways around it, using a script to reset an inbuilt serial number generator for the first record of each date, and then having FileMaker issue the numbers. However this would either require that you contrive to ensure that a script will run each day before the database is used, or that you ensure that all your new records are created by script - so it will not be as flexible as the solution I've suggested here.

If your solution is single-user - or if for other reasons, you feel that there is no danger of records being created simultaneously by users, then one of the suggested methods would be ideal.

[LiveOak]

The only potential problem I see is that the count of related records might not be the same as the highest serial number. If you create 4 records for a date 001...004 and delete 002, the next number assigned would be 004, a duplicate!

Ray's system is fine, as long as deletion of records is prohibited (at least until no more records are created for that date).

-bd

[CobaltSky]

Good point - thanks for picking up on that Brent!

To correct this (and still have serials issued by auto-entry rather than by script) I'd suggest that a value list called 'DateSerials' be created and defined to use only related values via the CreatedDate relationship, taking values from the serial field. Then the auto-entry by calculation formula should be changed to:

Right("00" & Month(Status(CurrentDate)), 2) & Right("00" & Day(Status(CurrentDate)), 2) & Year(Status(CurrentDate)) & Right("000" & NumToText(1 + Right(ValueListItems(Status(CurrentFileName), "DaySerials"), 3)), 3)

This method still works off the same two fields - as per my previous post, and still works without a script, but will allow deletion of records on the same day without the risk identified.