Auto Enter a serial number based on previous value

[john.daly]

First sort all the records based on the value of the field 'Mouse'.

Then goto the last record.

Set a global field (gHighestNumber) to equal the field Mouse. Since it is the last record it will be the highest number.

Create a new record.

Set 'Mouse' in the new record to equal gHighestNumber + 1.

You need to write a script to do it and if you have to use alpha and numeric charachters in the Mouse field then you will have problems. Can you use just numerical values? If not you will have to split it into the numeric part and the alpha part.

Hope this helps.

[LiveOak]

BE CAREFUL! In a multi-user environment globals should NOT be used to store serial numbers, last check numbers, etc. The value of a global disappears when a guest closes the file. It is NOT saved for use by the next guest. FM only maintains the value of a global in a single user file.

Say you have a field "Category" with the values "Mouse", "Snake", "Dog", etc. Create a self join relationship (a relationship to the file you're in) with "Category" matching "Category" called "SelfByCat". Create a number field called "TagNo". Create a script to create a new (mouse) record:

New Record/Request/Page

Set Field (Category, "Mouse")

Set Field (TagNo, Max(SelfByCat::TagNo) +1)

Because you a using a related field as the argument of the Max function, the highest TagNo for the mouse records will be extracted by the Max () function and the TagNo field of the new record set to this number plus one.

This allows you to have a separate set of serial numbers for each category.

-bd

[This message has been edited by LiveOak (edited January 23, 2001).]

[gschreib]

Thanks for the responses. The script suggested by bd should be helpful, but it doesn't accomplish exactly what I would like. While Mouse records will be added sequentially within a group of entries, the groups of information being entered will often be out of sequence (my co-workers will be entering their own records at their convenience). For this reason, the Max function will not be ideal. It would be perfect if in the "define fields - options" menu I could check "Serial number", drag "value from previous record" into "next value", and increment by "1". Is there any way to accomplish this using calculations/scripts? Thanks.

[gschreib]

I am trying to generate a database to keep track of the mice that are studied in the research laboratory where I work. Each mouse recieves a number tag when it is weaned. Generally, mice with sequential numbers will be entered into the database in order. However, we currently use 3 different sets of number tags.

To facilitate data entry, I would like the field "mouse" to auto enter the previous value for "mouse" + 1. When necessary, this value will be changed manually. The next value however, should be based upon this change.

For example, if I start by entering 19345, FMP will automatically display 19346. If I change 19346 to A3280 though, I want FMP to automatically display A3281 for the next record. What is the simplest way to accomplish this?

Please keep your response very simple. I have only been using FMP for a few days. Thanks.

[gschreib]

I think I solved my own problem. The following appears to work well so far.

Each animal recieves a unique tag number which may or may not be alphanumeric. To reflect this, I set-up a field called TagNo (text, Indexed, Auto-enter Previous, Unique). To facilitate data entry, I created the following script:

New Record/Request

SetField["TagNo",Replace(TagNo,

Length(TagNo)-Length(TextToNum(TagNo))+1),

Length(TextToNum(TagNo)),

TagNo+1)"]

This simple solution took me days to figure out. Now I need to figure out how best to set-up my files, define relationships, and add portals. You may see more posts for help from me soon.