Auto-enter Serial Numbers

[kelturner]

OK, I know this a really easy answer, but I'm having a hard time figuring it out. I have a numeric field and am using a calculation to return the first 7 numbers of another field (using: NumToText( Left(Record Number,7 ))), I would ALSO like to add two random or increasing serial numbers to this field. How would I do this, is there a function for it?

Thanks!

[Kurt Knippel]

Best way to go about this would be to include another "SerialNumber" field, which can be as simple as an auto-entered serial number or as complex as some kind of random generation scheme.

You then just append the contents of that field to the end of your other field. You do not need to use that field's data again, but it should not take up too much space.

[kelturner]

Thank you, this worked great!

[kenseye]

Each file has an identifying number such as a patient ID# or exam# or Rx# or lineitem# within their respective files. That # is used to open that specific record. Would it be wiser to create a unique # based on a calculation on the patient record # eg patient #3256 exam 6 might be exam #3256-006. A 5th line Item in a first order may be 3256-001-5?

[kenseye]

Or should it be based on date/time so wouldn't have to track serial #s for updating versions?

[Kurt Knippel]

If the serial numbers simply used as an identifying ID for the records then you are far better off using my ID scheme. See the discussion here:

http://www.fmforums.com/ubb/cgi-bin/ultimatebb.cgi?ubb=get_topic&f=8&t=000018

[kenseye]

Thanks for the last post. It simplifies serial number generating and also updating versions because no incremental serial number is stored with the file. Updating would just require importing the old data...Wow! this has been bothering me for months...

[kenseye]

Just a curious question...

any reason for positioning the date portion of the number last as compared to first ? Not that it makes any difference... but, if it is positioned first, it would be the one that changes the least as in a numeric sequence. Just thinking in the box...

thanks again

[Kurt Knippel]

I simply choose to make the most often changing numbers toward the front. To make it easier for me to compare keys sequentially. That is all. So long as you keep the same elements you can put them in any order you want.

You can also add prefixes and suffixes to the keys by adding:

PrefixText & " " & <the key as desgined> & " " & SuffixText.

Seperating the prefix and suffix from the body of the ID by at least a single space is all that you need to do. This keeps it all nice and indexable by Filemaker.

[kenseye]

Along that same line of the serial number...

I've developed a db with approx. 20 files with serial numbers that auto-increment. When updating to a new version of the database, the next serial number will be overwritten by the version update. What is your best solution for maintaining the correct serial number after version updates?

I have tried and external file holding the next number that increments after being used and it works ok, but I'm still uneasy about the numbers getting messed up. Is there another way?

[Kurt Knippel]

Why have more than 1 auto-increment serial number field? If they all start at a given point (say 1) and increment by a given value (say 1) they will all be at a known value at any given record number.

1 auto-entered serial number is all that you even need. Make the rest calculations based upon that number if you really need them as seperate numbers, or just base everything on that number.

I have never encountered a situation where more than 1 auto-entered serial number was actually a good idea.

That said, with Filemaker 5.5 you can actually set the serial number parameters via a script step.

When not in Filemaker 5.5, I used a method where I deleted all records, created a new record, did a Replace on the serial number field, starting at 1 and incrementing by 1. Since there was only 1 record it set the value to 1. Although honestly this was not worth the effort and I reverted back to just setting the numbers manually.