keeping a field constant after it is auto-enterd

[Dave C from CPWG]

I'm new at Filemaker and can't find a way to do something that I would think is a common practice. Any help is welcome.

I'd like to generate a key field value 'automatically' for an Instant Web Publishing application. The key field, once set must not change because records are referenced from other tables using the key's value.

On the Auto-Enter dialog, the key field's calculated value is defined as 'Left(Last Name;3) & Serial'. (e.g. for Ted Smith, the key value is set to 'SMInnn' where nnn is the current serial value)

This works fine when adding a new record to the database. However, the key is also changed when a record is modified and 1 or more of the first 3 letters of the last name change. (I realize it isn't often that the last name changes, but Users have over-typed the last name by mistake, or the last name spelling needed correction).

Selecting the the Auto-Enter 'do not replace existing value for field (if any)' doesn't work either because the generated key will then only consists of the Serial value. (Note, the form layout order is first-name, last-name, key, ...)

Thanks in advance for suggestions.

Edited August 3, 2008 by Guest

[comment]

Use ONLY the serial number for keys.

[Fenton]

One has to wonder, why are you using the last name in the key field? It is obviously the entire cause of your problems, and is known to be a poor practice. Why not just use the Serial ID (assuming this is a real FileMaker auto-enter serial number)? Or, if you want a key that is unique, but does not need to be updated after imports, you could look at Ray Cologon's uID example; which uses a Custom Function. It's the best method I've ever seen.

http://www.nightwing.com.au/FileMaker/demos9/demo910.html

[Dave C from CPWG]

It is expedient to have a portion of the last name as a key prefix, e.g.

1. A list by key order is pretty much kept in last name order regardless of the serial value.

2. Manually entering a key value for search purposes will fail when the serial # is incorrect whereas using the serial # only never fails, even when mis-entered.

[comment]

It is expedient to have a portion of the last name as a key prefix

I don't think so - on the contrary, it can only cause problems, such as the one you have described. Your primary key in any table should be completely meaningless and independent of any record attribute. If you need to sort or find by last name, then sort or find by last name.

[Dave C from CPWG]

We apparently differ on whether a key value should be easy to humanly read / manipulate.

In my current application, key values are displayed as part of the various outputs. When accessing a person's record, it is usually easier to enter the key instead of the whole name, e.g. 'WAU917' as opposed to 'Kyrstyna Waupotitich'. It is also safer than entering a serial number by itself, since if any one of the characters is incorrect, an error message is (typically) generated. It also seems to me that providing access to someone's data based on a UserId (and password) is pretty common and similar to what I'm doing.

Even though I probably haven't convinced you my approach has any merit, I'm stubborn and would still appreciate a solution to my question.

[comment]

No, we differ on whether a key should contain information. I don't mean to sound condescending, but this is not a point that I am inclined to debate. Here's a thread you might find interesting:

http://www.fmforums.com/forum/showtopic.php?tid/194785/

When accessing a person's record, it is usually easier to enter the key instead of the whole name, e.g. 'WAU917' as opposed to 'Kyrstyna Waupotitich'.

IMHO. it would be even easier to enter "WAU" into the last name field. But if you find it convenient to access records that way, I don't see why you couldn't continue to do so. That has nothing to do with keys. You can have your code field and use it to access records, or for any other purpose - just don't use it for relationships, because, as you said in your first post:

The key field, once set must not change because records are referenced from other tables using the key's value.

[Vaughan]

"When accessing a person's record, it is usually easier to enter the key instead of the whole name, e.g. 'WAU917' as opposed to 'Kyrstyna Waupotitich'."

Ok, fine. So create a "key" field to store a customer id that people can search on. But this should NOT be the key field used internally for the main relationships; an auto-entered serial number should be used for these.

[bcooney]

You're not going to convince any of us that a meaningful key is preferable.

I'm assuming from your rank as Novice that you do not realize that you can find records by typing in a person's last name, phone number, etc. You do not need to know keys to find.

[comment]

Just a technical footnote:

An auto-entered calculation (replacing) =

 Case ( IsEmpty ( Self ) and not IsEmpty ( LastName ) ; Left ( LastName ; 3 )  & SerialID ; Self )

will enter the specified code once, and not modify it any further. Of course, it also means that the code "WAU917" is going to remain permanent, even if Kyrstyna's name is really 'Saupotitich', and she has changed it to 'Sawyer'. But if she marries Henryk Wauslawski it will be a winner. :

[Dave C from CPWG]

Thank you very much for the code sample. It does just what I wanted.

[comment]

Even though I probably haven't convinced you my approach has any merit, I'm stubborn

:idunno: