Resetting ID Number at begining of month

[Darrell]

I might have asked this question already so If I have asked it, I am sorry. Anyway here is the current calc. I have ID number for each record:"Right(Year( Date ),2)&Right( DayofYear( Date ), 4) &

Right( "0" & Status(CurrentRecordID), 6)". It comes out looking like this 03246019. At the beginging of the new month I want the last 3 numbers to reset back to zero. How can I do this?

[CobaltSky]

Hello dbetts22,

In order to be able to provide a considered answer to your query, it would be helpful if you would first answer a couple of questions:

Is your solution multi-user?

How many records does the database hold and how many is it likely to grow to?

How do users interact with the database - in particular how are new records created?

[Darrell]

The DB is Multi User.

It will Have an unlimted records

Users start a new record by clicking on a button which runs a script. The script just starts a new record, nothing fancy in the script.

[CobaltSky]

Ok, fine.

There are a number of ways it can be done, but in light of what you've said, perhaps the most suitable method would be to add a few steps to the new record script which will detect when it is a new month and reset the serial numbers accordingly.

What I suggest you do is first create an additional file with a single record in it, with two number fields - one called CurrentMonthID acn the other called Constant_key. Enter a 1 into Constant_key. Then in your existing file, add a calculating field with the formula set to 1 and create a relationship to the new file which matches the calc to Constant_key field and call the relationship 'MonthRef'.

Next I suggest that you create a number field in your existing file called 'Serial#', define it to auto-enter serial numbers, and then change the formula for your record ID field to something along the lines of:

Right("0" & Month(Status(CurrentDate)), 2) & DayofYear(Status(CurrentDate)) & Right( "00" & Serial, 3)

Once all the above is in place, you will be able to change your new record script to:

If ["TexttoNum(Year(Status(CurrentDate)) & Right("0" & Month(Status(CurrentDate)), 2)) >

[Darrell]

Ok I have run into a couple of problems. In the first formula you suggested "Right(gcurrentmonth,2...etc" when I put the formula in it said it couldn't find the field "gcurrentmonth" so ifigured out I needed to make a new field whcih I did. I made the field a date field and selected the creation date. When I plug in the formula i get the following Id number 3015001. I changed the date on my computer to check each month and get a different number for each month Feb=61, march=89. Shouldn't it read 0115001 for Jan and 0232001 for feb.... and so on? Not sure what I did wrong there. Anyway for the second part of the formula I started a new script and selected the IF statement and In the If statement in the specify area I put the calc in you provided in the previous message If["TexttoNum(Year(Status(CurrentDate)) & Right("0" & Month(Status(CurrentDate)), 2)) >

[CobaltSky]

Hi,

The first problem you encountered arose from an error in my previous post - which I corrected, but apparently not soon enough to stop it from tripping you up. Sorry about that. The first part of the formula should have been Right("0" & Month(Status(CurrentDate)), 2)... rather than Right(gcurrentmonth, 2).

The second issue you've mentioned seems to suggest that the relationship ('MonthRef') and/or the field ('CurrentMonthID') were either not in place or were named differently when you were trying to enter the If [ ] formula. With the relationship and field in place, the formula is working here.

[Darrell]

The calc box Hightlights TexttoNum and gives me the error "Either an operator was ommitted,this function cannot be found, or "(" was not expected here."

So not sure what I am still doing wrong. The other is fixed.

Thanks for your help so far. I really appreciate it.

[CobaltSky]

Hi,

That's puzzling - I just tried it out and it worked straight off here. What version of FileMaker are you using??!

[Darrell]

I figured it out, it is now working. Thanks for the help.