Jump to content

How do I reset an auto-enter field


This topic is 5327 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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" !

Link to comment
Share on other sites

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 )

Link to comment
Share on other sites

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 ?)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 11 months later...

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

Link to comment
Share on other sites

This topic is 5327 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

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