I'm an Idiot! Help with Dates desperately needed

[NisquallyAttorney]

I'm a new Filemaker user and thought I was a reasonably intelligent guy. I've scoured the relatively poor Filemaker manual and online help files as well as the FM Knowledge base and can't find the answers so I'm posting here hoping some kind, FM veteran will take pity on me.

So, here's the deal. I've been trying to create a field that automatically enters and displays the Year in four digit format. I've created the field, made it a date field, and then tried a number of options including "Calculated value" and then selecting "Year (Date)" as the date function and get the lovely message "This filed cannot be found".

The above issue is only part of my problem. Here's what I really want. I want to create a field that automatically enters and displays a number with the following format: year-serial number As an example it might look like this: "2003-00005" Each time I create a new record I need the serial number portion to advance by 1. Then, when the year changes to a new year, I need FM to automatically change the year to the next year, and restart the serial numbering at "00001" And it's got to do this automatically when a new record is created.

Am I crazy for attempting this? If I can't do this, I still need a field to give me only the Year (rather than the month and day also) and to automatically change that when the year changes. That should be do-able but I have no idea how and can't seem to find a decent reference anywhere.

Thanks, in advance for your assistance. Sorry for the extremely long post.

Joe

[Kurt Knippel]

To get the "Year" displayed, setup a Date field and then on your layout, format the field to only display the year. It will need to be a custom date display, but can be setup in like 10 seconds.

[Peter Fenner]

If your year field updates to be the current year and you reset serial numbers to start at 00001 again you will have duplicated ids. Surely you want your year to stay the same as the year the record was added?

If this is the case then create a date field which (in options) is set to automatically enter the creation date.

Next create a calculation field (text) as follows:

Year(Creation Date Field) & "-" & YourSerialNrField

The serial nr does not take long to change back once a year (in define fields) to 00001.

[Lee Smith]

Hi Peter,

Try

Year(Status(CurrentDate))

HTH

Lee

[Ugo DI LUCA]

Hi Lee,

Nope. Pete is ******* right here I think as Status(CurrentDate) will update while its creation date will still be the same.

I think Joe would like the records created next year to have 2004, but he doesn't want his old serial to change to 2004.

I would personally hold another serial num and change the calculated serial (00001) within the define fields calculation for this auto-enter serial num using a selfjoin on Year and a auto-enter from previous record.

litterally ---> Case(PreviousYear#CurrentYear, ---> reset the serial by calc)

Don't ask how to do this thus ...

[Peter Fenner]

Status(CurrentDate) won't update if you store the calculation result. But its highly dangerous. If you are modifying your database and make changes to the calculation that has Status(CurrentDate) in it then it will update the record you are currently on - It could be nightmarish, especially if it is your serial id. Status(CurrentDate) is often very useful, but I would not use it unless you really want the status of the current date.

Pete

[NisquallyAttorney]

Thanks for the help, everyone. I think Peter's solution has me most of the way there! Thanks so much, Peter.

The solution was: Year(Creation Date Field) & "-" & SerialNumber

One odd behavior though is that the SerialNumber field is in the format "00001" and properly displays the four placeholders. When the number gets to the tens then it shows three placeholders, etc. When the SerialNumber field is output in our formula as text, it displays as "1". The zeros are gone. Any way to retain the leading zeros of the SerialNumber field?

Also, there are a couple of things I'm still not clear on from the conversation back and forth. At the end of the year it is pretty clear that the year field will change only on the new records. So, records created this year will retain 2003 and records created next year will automatically adopt 2004 as their year. This is good. The difficulty is the serial number. If I manually tell FM that the next number in the series is "00001" again, will that affect any of the numbers that are already entered in records, or will it only change the numbering of the records yet to be created?

Again, thanks for your help. I was a mighty frustrated individual last night when I went home. Hopefully as I work with FM, I'll be able to contribute something back some time.

Joe

[Peter Fenner]

Hi Joe

SerialNumber is a text field that will not get rid of the leading zeros

CreationDate is a date field that is set to input the creation date

SolutionID is a calculation (text) field as follows: Year(Creation Date) & "-" & SerialNumber

No your previous series of numbers will not be affected at all.

Pete

[paulage77]

The zeros are gone. Any way to retain the leading zeros of the SerialNumber field?

Set the desired calculation result to Text instead of number