Serial #s

[kenseye]

Capt Kurt recommended this version of generating a serial # that would be immune to problems when updating versions:

Right("000000"&Status(CurrentTime)&"-"&

Right("000000"&Status(CurrentRecordID)&"-"&

Right("000000"&Status(CurrentDate)

This works great, but the serial numbers are pretty long....

Anybody every try auto enter SerialID# = Next#

and defining Next#= Max(serialID#)+1

Then Serial ID would auto increment based on the last serial ID without having to keep track of the last number entered for version update.

Capt. Kurt, are there any disadvantages to this?

[kenseye]

sorry my parentheses are off...

[d94dsj]

This works, but is quite slow when you have more than a couple of thousand records in your file, because of the Max function. I tried using this scheme, but my user's found it to be an annoyingly long time when creating new records (which increases with the number of records unfortunately).

You also have to define it through a lookup, or else FM will complain about circular references, if I remeber correctly.

Nowadays I always use CaptKurt's scheme (or a modification to it - see the thread in the section "Relational Database Theory" about this) and it works great.

There are a few downsides to this approach:

* They are long, which means that will more quickly reach the limit of 60 characters in a concatenated key, compared to ordinary serial numbers. I haven't yet discovered this to be a problem, though.

* Users could get confused if they select the primary key from a popup-list where you choose to display the key and something else (a name for instance). Also, if you use this in find mode (a pop-up list), after they have selected the right key (in the list displaying also a descriptive field), it is the key that's visible since calculations are not shown in find mode (normally I have a calculation over the primary key field, showing a name or whatever is relevant)

But, still I think it's the best alternative there is for a primary key.

Daniel

[kenseye]

Thank you Daniel.

Just wanted to be sure... actually relieved...

I wouldn't want to change my solution any more unless it will make things better.

[BobWeaver]

One other problem with the Max()+1 method is that in a multi-user system, two users creating a new record at the same time could possibly end up with the same serial number.

[ March 08, 2002, 01:34 PM: Message edited by: BobWeaver ]

[Kurt Knippel]

quote:

---

Originally posted by kens:

Capt Kurt recommended this version of generating a serial # that would be immune to problems when updating versions:

This works great, but the serial numbers are pretty long....

---

Only to a person looking at the data. To the machine or the database engine this number is tiny. And the purpose of this number is to ensure proper uniqueness and integrity of the data, not to make the number easy to read. Use another field for that, make it available to the user, make it modifiable, just do NOT use it for any functionality.

quote:

---

Anybody every try auto enter SerialID# = Next#

and defining Next#= Max(serialID#)+1

Then Serial ID would auto increment based on the last serial ID without having to keep track of the last number entered for version update.

Capt. Kurt, are there any disadvantages to this?

---

This has all of the disadvantages of a simple sequential serial number. It only serves to add some complexity to the generation of the number.

It solves nothing and actually leave some addtional room for error.

[Kurt Knippel]

quote:

---

Originally posted by DanielS:

* They are long, which means that will more quickly reach the limit of 60 characters in a concatenated key, compared to ordinary serial numbers. I haven't yet discovered this to be a problem, though.

* Users could get confused if they select the primary key from a popup-list where you choose to display the key and something else (a name for instance).

---

I try to avoid using the key in any finds. The user never actually want to find by this anyway. The user wants to find by something that generally crosses many records and using these kinds of keys is most often not needed.

If I am looking for Joe Smith, I want to find ALL Joe Smiths in the database, not simply one that was chosen by the PrimaryID.

When working with compound keys I will usually use codes for all my other values. Even assuming that I want to seperate them by a space, and that each code is 3 characters long, you can still get at least 10 codes within the 60 characters and that is ALOT of stuff for a compound key.

[danjacoby]

And now for what may be a silly question:

Couldn't a calc field with Status(CurrentRecordID) be used as a unique identifier? It seems to me that each record gets its own RecordID when it's created, and according to the Help file it doesn't ever change.

[BobWeaver]

If you import records from another Filemaker database, those record ID's will change.

[danjacoby]

quote:

---

Originally posted by BobWeaver:

If you import records from another Filemaker database, those record ID's will change.

---

Quite true, but...

If you're using the record ID as a keyfield to create a relationship, then the records you're importing can't already have the same relationship you've created within the file you're importing the records into.

Unless, of course, you're trying to merge two complete sets of records from two different copies of the same solution (or unless it's a self-relationship). But if you're willing to live with that particular limitation...

[BobWeaver]

I was just pointing out that you have to be careful about what you read. Sometimes there are exceptions. However, I recall there are a couple of other reasons not to use the record ID as a unique identifier, but I can't remember what they are. CaptKurt explained it once upon a time.

Alas, I have a short memory... but it's very wide

[d94dsj]

quote:

---

I try to avoid using the key in any finds. The user never actually want to find by this anyway. The user wants to find by something that generally crosses many records and using these kinds of keys is most often not needed.

If I am looking for Joe Smith, I want to find ALL Joe Smiths in the database, not simply one that was chosen by the PrimaryID.

---

CaptKurt, it's dangerous to assume that you know what your users need to search for. Even if you are an experienced system designer, I can assure you that you do not always know what the users' needs are before you have conducted interviews, and field tests with an actual solution. In some cases, they need to enter a foreign key and other search criteria to perform a search. Of course, they should be aided in choosing this foreign key - Usually it's enough to select it from a list displaying the value from some other calculated field.

Have you ever heard the expression "Assumption is the mother of all f***-ups"?

quote:

---

When working with compound keys I will usually use codes for all my other values. Even assuming that I want to seperate them by a space, and that each code is 3 characters long, you can still get at least 10 codes within the 60 characters and that is ALOT of stuff for a compound key.

---

I'm not sure I follow you here. What do you mean by "codes"? Is this pertaining to a particular sitation/tecnique? Otherwise, I would think that you would need the entire keys to uniquely identify the components of the compound key.

Daniel

[d94dsj]

>>Bob and Dan

It's a bad idea to have a key that is a calculation, just because of the fact that it can change. Not knowing when (imports, cloning and so forth) then becomes the problem.

But in CaptKurt's scheme, the recordID is auto-entered into a text field. So it's taken at the moment of creation. This also means, that we cannot use the RecordID alone in the key, because FileMaker no longer can guarantee the uniqueness of the key. That's why we have to add other "unique" information, and the best there is, is time and date.

Another benefit of using CaptKurt's scheme is that you have a fixed key length. This could be advantageous if you want to perform text calculations on the key, or to know how many keys will fit into a text field (that would be 64,000 / 20 + 1 = 3047). Unfortunately, this is much fewer than if you had used an auto-entered serial number, but at least it is deterministic. I guess you can't eat the cake and still have it

Daniel

[Kurt Knippel]

A user should NEVER have to search by an ID field. This is for the system ALONE. The user wants to find Joe Smith, not 456542-0786544-009898.

This is NOT an assumption on what users do and do not want this is simply a system design parameter.

quote:

---

]I'm not sure I follow you here. What do you mean by "codes"? Is this pertaining to a particular sitation/tecnique? Otherwise, I would think that you would need the entire keys to uniquely identify the components of the compound key.

---

By codes I mean abbreviations for longer identifiers. Let's say that my compound key is made up of ParentID - PrimaryID - State - UserType - Area - Level. (The actual meaning of the terms is immaterial for this discussion).

The key could literally be made up of the actual data in the field but this could quickly exceede 60 characters.

So I use codes, basically shortened versions of the fields in question. So that a compound key that might normally look like:

565543-676543-123432 - 098786-234321-009843 - California - Student Events Coordinator - San Diego County - Eighth District

Could be shortened to:

565543-676543-123432 - 098786-234321-009843 - CA - SEC - SD - 8

To the system it does not really matter which method is used, but you can squeeze more into your compound keys by using codes rather than the entire text of the fields.

The actual implementation is a little more complex, because you need to translate the actual text into these codes, but usually this can be accomplished with realative ease.

[Kurt Knippel]

quote:

---

Originally posted by kens:

There is only one instance in by solution where I do not use CaptKurt's serial #. In this instance the user must enter the inventory number for a specific spectacle frame taken from our display. Entering the 18 digit # was not practical so I resorted to tracking the last # entered in my preference file.

---

I NEVER have the user interact with this ID field. I aways use a field that has meaning to the user, even if the system itself is totally based around the PrimaryID, the user never needs to interact with it.

To the user a 2 digit code is just as good, even though we both know it is not.

[kenseye]

There is only one instance in by solution where I do not use CaptKurt's serial #. In this instance the user must enter the inventory number for a specific spectacle frame taken from our display. Entering the 18 digit # was not practical so I resorted to tracking the last # entered in my preference file. I still worry about possible screwups. Otherwise CaptKurt's serial # has worked well even with updating verrsions. No more sequence worries.

Thanks for all your input.

[d94dsj]

Kurt,

It would be interesting to see an example of a solution where you have implemented the "codes". How do you know that the individual "codes" are unique? (Because that's the #1 purpose of a primary key)

I cannot see how this would be done practically. Please prove me wrong!

How would you let a user enter find criteria in a layout, where the actual find (technically that is) would have to contain primary keys for some value (e.g. a company name) that the user has selected?

Now I suppose you answer that you would use specific find layouts and do not let the user enter find criteria while in find mode, so you can use portals and such for aiding the user in selecting the correct criteria? In that way you could assign primary key values with a script...

Daniel

[Kurt Knippel]

Here is a real example:

I did a "schedule" database for a client. Basically it is a calendar. A "schedule" is a collection of "items" which might be appointments, events, to dos, phone calls, ect. Any of these can be associated with a client, with a project and with a staff member (but only one of each). Each of these items can also have a status (open, cancelled, complete, etc) as well as a category (Warning, Notification, Staff - Vacation and a whole ton of others).

The general idea is that you can have many different schedules. One for each project, one for the office as a whole, pivate ones for individuals, etc.

The items are shown in a portal by date with a filter by Client, Staff, Status, Category and Project. Now obviously these alone could well exceede 60 characters.

The acutal calcs are too long (and confidential) to show but basically the key is made up of the ScheduleID, Date and the filters. Each client is referenced by a 4 digit code, each Staff, Status and Category by a 3 digit code, each Project by a 8 digit code.

xxxxxx-xxxxxx-xxxxxx xxxxxxx xxxx xxx xxx xxx xxxxxxxx

This makes a single line key of 55 characters. Using anything other than codes would make this impossible.

There is also a substution code "all" for allowing the display of all items of that type. The all code applies to Client, Staff, Status, Category and Project. Schedule and Date are static.

The user has a series of global fields showing the possible filter values for each filter. They can then select any combination of filters. A seperate calculation actually converts the user's selections into the codes and form the left side key, with is related to the compound multikey in the related file.

Here is a simplified version:

Parent File

code:

---

PrimaryID

Date

gFilter1

gFilter2

cFilterID = PrimaryID & " " & Date & " " & gFilter1 & " " & gFilter2

Child file

code:

---

cCompoundMultikey =

ParentID_fk & " " & Date & " " & Filter1 & " " & Filter2 & "