I upgraded an old solution and it has record #'s in serial format.
I'd like to remove this to that type that no matter how you sort, the
first record is #1, as it were, even though each time it'll be a
different record that is #1. I just unticked the serial feature in
the properties, but that didn't work.

Such a simple question, sorry for that.. I started some type back to
compile a tips folder for FMP now that I have a freeware notepad
replacement that handles URLs (it has richedit) and I just index that
folder for easy reference to the tips, but I never saved how to do
this as it was before this new system. Tx.

Cerulean wrote on (9/14/2005):

That serialized field is an "artificial" value created by the developer.
FileMaker has its own built in values.

What you want is FM's Record Number, changes with every find and every
sort, representing the record's place in the found set at that time. See
the Get (RecordNumber ) function in Help.

Change that serialized field to a calc = Get ( RecordNumber ).

Conversely, there's the Record ID, which FM assigns when a record is
created and never changes. Record ID is unique to each record in each
table in your file. See the Get (Record ID ) function in Help.

Matt

In article <%bWVe.14801$FT6.7477@trndny02>, I'm (AT) Witz (DOT) end says...
Record IDs are lost when you import. They are suitable as ids within the
scope of a session, but cannot be relied upon not to change.

42 wrote on (9/14/2005):

As described in FileMaker Help, the Record ID "is a decimal value (an
integer) generated by FileMaker Pro when the record is created. It does
not change."

I wouldn't expect the record ID to be the same in a destination file as it
was in the source. Importing created new records in the destination, thus
new record IDs, within that file.

But in a given table in a given file, as a record is created, FileMaker
assigns a new Record ID, and it never changes, nor is it ever duplicated.

Matt

In article <cqbWe.21963$Zv6.10837@trndny03>, I'm (AT) Witz (DOT) end says...
Many people *have* made that very mistake. Particularly when the
destination file is an empty clone of the source file.

Exactly. And any filemaker database being used for real work will
eventually need to be discared and the data imported to a new file
either to recover after a corruption, or to allow development to proceed
on a separate clone, or when you update to a newer file format of
filemaker and it gets converted from FP3 to FP5 to FP7 etc.

If you use those record ids as keys and reference them in other files,
the entire thing sooner or later will blow itself up destroying all the
relationships between records in the process. Moral of the story: do not
use those id's as record keys outside the scope of a single "session".

And that would be great if we could all use the exact same file
indefinately. We can't. So its a very bad idea to base the referential
integrity of your data upon the internal recordids.

The only time it makes sense to use the recordids is within the scope of
a session, where you want to maintain a link to a record for a breif
duration, e.g. while updating its primary key via a script.

Or possibly, if you want to maintain a temporary link to a specific
record in a table that doesn't have a primary key to perform some
operation on it. (Although not having a primary key is generally a bad
idea, and not having a primary key in a table where you actually need
one and are thus resorting to using record ids is even worse.)

-regards,
Dave

42 wrote on (9/15/2005):



The original question had nothing to do with relationships.

No part of my answer mentioned relationships, nor was there any suggestion
that either function was useful in a relationship.

I described the changeable RecordNumber as an answer to the original
question, and offered the unchanging RecordID as a comparison. Nothing else.

Matt

In article <AhdWe.11177$c27.2244@trndny01>, I'm (AT) Witz (DOT) end says...
Ok. Right.

Right. I gave readers credit for being able to deduce that they might be
useful in relationships on their own.

Right.

I think you are being needlessly 'defensive'; I never said anything you
said was wrong.

You did, however, say this: "Conversely, there's the Record ID, which FM
assigns when a record is created and never changes."

Its correct as far as it goes, but its dangerously incomplete. A great
many people have made the mistake of using RecordIDs instead of creating
their own primary keys, because they read about these recordIDs.

Think about it: using recordids as primary keys *seems* a "reasonable"
thing to do.

1) They are unique
2) They "never" change
3) Filemaker creates them automatically for each record in the database

That's pretty much the *textbook definition* of primary keys. If this
were the first time you'd heard of Filemaker's recordID, you might
immediately think, "Hey, cool feature, filemaker already has a primary
key automatically in every table! I'll just use those; no point in
duplicating the effort.". And it will work perfectly too, until they
need to import (which may be years worth of data entry from now).

I just wanted to head off *anyone* thinking about them in terms of
usable pkeys.

42 wrote on (9/15/2005):

Sorry for the defensiveness. It has been a normal state of late.

I'll accept that it would not be good for someone to assume that the
RecordID could be used as a primary key, and that some advice should be
given against making that mistake. Perhaps FMI should expand its Help
entry to include the fact that the RecordID sequence is unique to each
table, cannot be controlled, and should not be used for relationship keys.

I can't say I agree that it would be exceptionally dangerous: I have to
believe that any reasonably observant person would see quite quickly that
basing the primary key on the RecordID simply doesn't work.

First, they would have to create a field in each table to take the
RecordID as an autoentry, something I would not expect a newbie to do.
Later, perhaps, but not right away.

For the relationship to work (i.e., to display the desired related data),
the parent and child records would have to have the same RecordID, which
is exceptionally unlikely. I don't believe there a one of us who doesn't
create a few test records in every table as they are set up, most of which
would be deleted before real data is entered. Given even a minor number of
test records in the related tables, I don't think it would take long for
that newbie to see that the related data don't relate.

All that, of course, far ahead of any concern for losing anything because
of what happens in an import.

Matt

In article <YjmWe.19453$e96.3224@trndny09>, I'm (AT) Witz (DOT) end says...

I'd agree.

In my experience they usually go with unstored calculations. The
rationale being that unstored fields don't take any extra space, and
work just fine on the parent side of a portal. How clever!

That would be an exceedingly odd way to set it up indeed. Nobody tries
to relate independant tables primary keys to each other, at least not
for more than a few minutes. <grin>

They treat them just like we'd treat a normal serialized key. The parent
table will have an unstored calc number called "my primary key" which is
equal to Get(RecordId), and the child table will have a regular indexed
number field called "my foreign key" . The relationship is built on
that, new records are created, for example, through a portal, pushing
the parents recordid to the childs foreign key field, linking the
records. Everything works flawlessly for years...

Then one day in the distant future you import and the parent records are
all given new serial numbers and the children aren't related to the
right parents anymore.

-regards,
Dave

On Wed, 14 Sep 2005 14:11:07 GMT, "Matt Wills" <I'm (AT) Witz (DOT) end> wrote:

Matt, thanks! Can you tell me where in help? I've done numerous
searches and nothing! <sigh> Very frustrating. Tx.