daily auto number

[freecolours]

Who can help me with this: I want to create a field that will auto enter the current date and a two digit serial number;

like this: 200802-01, next record 200802-02, etc

but every day the serialnumber must begin with 01 again;

like this: 210802-01, next record 210802-02, etc

I hope I'm clear enough with my question!

[thierry.be]

Hi,

I am relatively new to Filemaker but I think I can help you. But there are probably better (read faster ways to implement this)

I would say you make a field

- current date

- teller

- calculation field : current date & "-" & teller

Everytime you create a new record , just make a script that checks the the date of the last record. If it is the same then today add teller with one of not set teller to 01

Hope this works,

Thierry

[CobaltSky]

Hello freecolours,

Your request is a little tricky, so there will be a number of steps required to get you to where you want. What you are wanting to do can be achieved if you:

1. Create a calculating field called "DayID" (result type Text), with the formula:

Left("00", 2 - Length(Day(Status(CurrentDate)))) & Day(Status(CurrentDate)) 

& Left("00", 2 - Length(Month(Status(CurrentDate)))) & Month(Status(CurrentDate))

& Right(Year(Status(CurrentDate)), 2)

---

2. Create a number field called "Serial".

3. Create a self-join relationship called 'DaySerial' based on DayID (ie a relationship that links the file to itself, using the DayID field on both sides of the relationship definition).

4. Create a calculating field called 'NextDaySerial' (result type Number) with the formula:

Max(DaySerial::Serial) + 1

---

5. Enter "Options..." (within 'Define Fields') for the 'Serial' field, go to the 'auto-enter' tab and select 'looked up value', then define the look-up to copy a new value in 'NextDaySerial' based on the 'DaySerial' relationship. Select the option for "If no exact match use:" and enter 1 into the text box.

6. Create a calculating field called 'SerialID" (result type Text) with the formula:

DayID & "-" & Left("00", 2 - Length(Serial)) & Serial

---

That should do it. Now you can place the SerialID field on your layouts and it will automatically generate numbers in the format 200802-01, 200802-02, 210802-01, 210802-02 with the serial number at the end beginning with 01 again each day. None of the other fields mentioned above need be displayed on any layouts - they will operate 'behind the scenes'.

[freecolours]

Hello CobaltSky ,

YOU ARE SO AWESOME!! Thanks for your great help! It works great.