Jump to content
Sign in to follow this  

daily auto number

Recommended Posts

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!

Share this post

Link to post
Share on other sites


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,


Share this post

Link to post
Share on other sites

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'.

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Who Viewed the Topic

    1 member has viewed this topic:
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.