How do I reset an auto-enter field

[Dr.Hamed]

I have an auto-enter serial number field that I need to reset every new month (starting with yy-mm001) how would I do that?

I managed successfully the calcualtion as: [color:red]Right(Year(DATE);2) & "-" & Right("0"&Month(DATE);2) & Right(("00"&serial);3)

where the DATE field is record creation date and the serial field is auto-enter field incremented by 1. I just need to reset the serial field "the last three digits" every month.

Best Regards

[John Mark Osborne]

To reset a serial number, you have to enter Define Database, locate the serial number field and enter options for that field. You can then the next value to whatever you desire. Or, you can script the process using the Set Next Serial Value script step.

[T-Square]

Gary--

You might want to re-examine whether you want to maitain this field as your key field in the future. Such "overloading" of fields (i.e., where you use one field to store more than one piece of information) can lead to longterm maintenance difficulties.

Instead, you can have a simple serial number to identify the record uniquely, and an unstored calculation field that gives you the display your users are accustomed to. Using a self-join based on the month and year, sorted by ID, and then using a calculation like Right("000" & Get(RecordNumber); 3) in the related file, may get you a result quite like you've described, without scripting a reset of the file structure.

That way, you do not run the risk of accidentally creating duplicate ID numbers (which always seems to happen to me when I try to get fancy with IDs.

HTH,

David

[Dr.Hamed]

I like to keep this format [color:red]yy-mmnnn (i.e 06-05323). With this format, i can tell easily the number of patients i see every month.

The thing i need is to automate the nnn to start with 001 at the beginning of every new month (without going into the serial field and changing its vaule).

[John Mark Osborne]

I agree with David about overloading your fields but it's your choice. Use the Set Next Serial Value script step to do what you want (as described in previous posts on this thread).

[Raybaudi]

Hi

since you own FM8 Advanced, you can try this file that contains a custom function:

-------------------------------------------------

MonthlySerial custom function

Author: Daniele Raybaudi

Format: MonthlySerial ( AEfield ; digit )

Result: text

parameters:

AEfield: text - the text field wich will hold the serial number; it must be setted as auto-enter, always evaluate

digit: number - the number of digit (#) that must increment by one and restart from one every new month

This custom function can be used for:

1) Autoenter a serial number that restart from YY-MM001 every new month

2) the format of the serial number is essentially: YY-MM###

3) the serial number may have how many digits (#) you choosed

The Auto-Enter field must be setted to always evaluate;

the custom function isn't recursive, so you can use it as a simple calc;

the custom function will work only with FMP8 and above.

05/23/2006

*/

Let([

year = Right ( "00" & Year ( Get ( CurrentDate ) ); 2 );

month = Right ( "00" & Month ( Get ( CurrentDate ) ); 2 );

lastId =GetNthRecord (AEfield; Get ( TotalRecordCount ) - 1);

lastMonth = Middle ( lastId; 4 ; 2 )

];

year & "-" & month

&

Case(

month ≠ lastMonth or Right ( lastId ; digit ) = 10^digit -1; Right ( 10^digit ; digit-1 ) & "1";

SerialIncrement ( Right ( lastId ; digit ) ; 1 )

)

)

-------------------------------------------------

[color:red]Note that your ID repeats itself after 999 records (in the same month)!

MonthlySerial.zip

[comment]

Note how easy it is to get a duplicate by simply deleting a record. Serial numbers MUST be unique. There are other ways to count the number of records in a month, without risking the integrity of the entire database.

[Raybaudi]

Hi comment

if you delete a record, you 'll have not a duplicate ID number...

Example:

06-05001

06-05002

06-05003 Now I delete this; the next will be:

06-05003 But this is unique !

So I think that this is a "feature"... not a "danger" !

[comment]

Hey, Daniele - would you like to take a few more minutes to think about it?

[Raybaudi]

Michael, I don't understand...

Nor before, neither now after some hours !

Do you suggest that serial numbers must be unique AND assigned only once ?

If so, I can agree for many situations...but I'm not sure for THIS situation.

BTW the attached example makes exactly what Gary asked for. ( and he can decide himself if it will be better to make another field containing a REAL serial number to use for relationships )

[Razumovsky]

*Nevermind- misread the calc....

Edited May 23, 2006 by Guest
hasty reply

[Raybaudi]

Have you tried the example ?

06-05001

06-05002

06-05003

Now I delete 06-05002

What is the Id of the last record ?

06-05003

So the Custom get 003 and adds 1 and the next will be:

06-05004

No dupes here ! ( and inconsistent result for Gary, but why he has to delete the second and not the last too ?)

[comment]

Actually, I misread the calc too. But that's not important. Because if you cannot get a duplicate by deleting a record, you can do so by changing the sort order. But that's not important either. The real point is how do you prove this cannot happen under ANY circumstances? It's almost imposible to predict all possible chains of events.

[Raybaudi]

... Because if you cannot get a duplicate by deleting a record, you can do so by changing the sort order.

This is a serious issue ! (I didn't think at it)

Thanks comment

[Vaughan]

Gary: have your cake and eat it.

Use a plain auto-entered serial as the record's primary key. Use it for relationships. Never reset it. No need to ever display it on layouts.

Create another field to display your "record number" that rolls-over each month. Never use it for relationships or anything structural.

[Dr.Hamed]

Hi Raybaudi,

Your function is working great with minor limitations. When i change the year and keeping the month, the function will [color:red]not reset the serial:

06-05001

06-05002

06-05003

07-05004

07-05005

Edited May 24, 2006 by Guest

[Dr.Hamed]

Hi Vaughan,

Your point is well taken. As you suggested, I do have a field with auto enter serial ID that i do not play with.

[Raybaudi]

When i change the year and keeping the month, the function will [color:red]not reset the serial...

So use this calc:

---------------------------------

Let([

year = Right ( "00" & Year ( Get ( CurrentDate ) ); 2 );

month = Right ( "00" & Month ( Get ( CurrentDate ) ); 2 );

lastId =GetNthRecord (AEfield; Get ( TotalRecordCount ) - 1);

lastMonth = Middle ( lastId; 4 ; 2 );

lastYear = Left ( lastId ; 2 )

];

year & "-" & month

&

Case(

year ≠ lastYear or month ≠ lastMonth or Right ( lastId ; digit ) = 10^digit -1; Right ( 10^digit ; digit-1 ) & "1";

SerialIncrement ( Right ( lastId ; digit ) ; 1 )

)

)

---------------------------------

BTW: due to the error pointed out by comment, make always a new record after a "sort" by the AEfield... in other words make a new record with a button that has 4 steps:

1)Show All Records

2)Sort records

3)Go to record[Last]

4)New Record

[comment]

LOL, couldn't the script set the field as well?

Gary: you should clarify what you want to happen if a record is deleted. Should the subsequent recorda re-number themselves, or are these numbers supposed to be set in stone? Daniele is very brave to answer your question without this detail.

[Raybaudi]

Hi Gary

This is a better and simpler solution.

Without the custom function and without the script.

MonthlySerialREV2.zip

[Raybaudi]

LOL, couldn't the script set the field as well?

LOL (that was a temporary workaround)

[Dr.Hamed]

you should clarify what you want to happen if a record is deleted. Should the subsequent recorda re-number themselves.

No the subsequent records should not re-number themselves. Its ID should not be used again, unless it is the last created record.

For example if i deleted an old record (06-03201) it should not show up with new record creation. Whereas if creating the last record 06-05223 and deleted it, the new record should take the same number.

[comment]

Well then Daniele's method should work for you - until two users create a new record simultaneously. That's what I don't like about these custom numbering schemes - there's always something that can trip them.

In any case, it can be simplified a bit.

Untitled.fp7.zip

[Dr.Hamed]

Hi comment:

One problem with the script. Create some records:

06-02001

06-02002

06-02003

then change the year and create more records.

07-02001

07-02002

07-02003

Now if you change back the year to 2006 and create more records, they will start from 001 again.

06-02001

06-02002

06-02003

How to avoid these duplicates.

[comment]

Thanks for proving my point. So what do you plan to do - go back in time to add patients?

[Dr.Hamed]

Dear comment and raybaudi,

I can't get your methods to work when incorporated into my database, although they work fine as separate files.

I attach my file for your evaluation. Please advise.

account name: admin

Cases.ZIP

[comment]

Details, details. You have sorted the wrong side of the relationship - and in the wrong order.

In case it wasn't clear - I am not a fan of either metod. I only posted the file to show it can be done with fewer resources. I still think it is prone to failure at some point.

[Dr.Hamed]

Thanks for the clarification. I got it.

[Donnie]

Ok, these are great examples, as I am trying to do "sort" of the same thing, although I only need a yearly change in my prefix. On October 1, or when they close the accounting books for that fiscal year, my invoice prefix needs to change to the new year. Attached is the file I currently have, but I really like the examples on this post. I'm not a coding expert by any means, and I'm not sure how to take out the "month" areas of the examples shown to put into my system. Any thoughts? Thanks a ton!

Donnie

Copy_of_Invoice_Log.zip