solutions wanted Changing a Primary Key

[gessie]

Hi

Just upgraded to FMP 9 - and took it as time to make some structural changes in a file I use frequently. This tracks clips of films that originally came from DV tapes of Himalayan dances - there are about 300 tapes and 800 plus records. Originally I used a date/camera/clip number to make a Unique Tape ID for each dance I wanted to pull up as an individual record - so Tape ID could be 050621C1_3 which (reading backwards) gives me the 3rd dance on the first tape of the C camera on 21/6/05. This has worked fine to date. However, I'm starting now to combine several records from clips that spanned two or more tapes into a single record (possible in digital but not with tapes) which will reduce the number of entries by about 150 records. I was wondering if it would be a good time to introduce a new primary key that would be a simple numeral identifier - liberating me from strict attachment to the TapeID field (the tapes are all in storage!) - but don't know if this is a quite simple process, how best to go about the switch - or if it might have some hidden consequence for the rest of the file's data integrity etc. I would still keep the TapeID on one layout to be able to return to the tapes if at some future time it's necessary, but it is no longer of real significance in this new digital world of clips stored on a big RAID. Any hints as to how to go about this process of safely and surely creating and inserting a new Unique ID field would be much appreciated.

Big thanks

gessie

[David McQueen]

Set yourself up a new field that is a text field, auto enter serial number, indexed. Then do a replace to put serial number data in each record. This will give you a prime key for each record that is unrelated to the record data content. This is always a good thing to have. Data in a record changes.

By using text instead of number, you are then free to use this key in forming calculated keys:

a.) of a concatenated type eg: Prime1&" "&Prime165

b.) as a coumpound type eg:

Prime1

Prime165

Prime473

It will not affect your existing database, but it will be there for you to use when incorporating new features.

HTH

Dave McQueen

[gessie]

Dear David

Thanks for that reply - that will help me to get started on this little project. I designed most of my FMP solution off the cuff - but the more I read into the subject the more I realise that this sort of primary key seems better than a "meaningful" one in the real world. And then the original necessity for the TapeID field began to change as I moved from tapes to stored digital files.

I'll implement over the weekend - and post back if other issues come up. Thanks for the helping hand.

gessie

[Twd70]

I agree a primary key should always be:

a) uneffected by record data

: unique

but Can you tell me why a TEXT primary is better in this situation?

Using text means that when i search for '1' i get these results:

1, 11, 3112, etc

you can ofcourse use the exact match == but why should you have to? it adds another script line.

[David McQueen]

I am sure that I replied to this yesterday, but the post does not show.

There is debate on whether to use text or numbers as prime fields. My preference is to text so that the prime fields can be combined easily with other field data to form both concatenated and compound keys for various relationships.

Concatenated keys are the formation of a long text string. eg:

Prime & " "& LateIndicator& " " &DateAsText

Compound keys are the formation of return delimited lists. These can be used in relational finds and in filter keys eg:

LeftLastName;1 & "return"&

LeftlastName;2 & "return"&

.

.

.

Using Auto Entered Serial Numbers of form text, if one then creates a calculated field that is TextToNumber of the PrimeKey, this calcualted field will behave as you wish for sorts etc.

Dave McQueen

[Vaughan]

"My preference is to text so that the prime fields can be combined easily with other field data to form both concatenated and compound keys for various relationships."

This can equally done with numbers, since the type of the result is set by the calculation.

I'm moving towards primary keys being numbers. I got bitten by text primary keys last year when used them to sort a huge data migration, expecting them to sort numerically. Instead they sorted alphabetically. It was a *very* late night.

[David McQueen]

"This can equally done with numbers, since the type of the result is set by the calculation.

A warning there. If you are depending on FileMaker to make the conversion in context automatically, I have not gone through all of the manuals to confirm this, but I think you are doing a variant of depending on "undocumented behaviour" ... Which can change at any time.

The exception to this is if you explicitly doing all of the number to text transferences as you build the key.

This too can bite you.

[LaRetta]

There are pros and cons to both. I use a numeric serial for every table - no exceptions - ever. It most cases this is enough and I don't need a text key. Numeric keys began to impress me when vs. 7 came out; handing us the ability to join on comparative operators. And numbers ARE leaner than text.

The exception to this is if you explicitly doing all of the number to text transferences as you build the key.

This too can bite you.

If I need a multiline or to concatenate keys, I create them - simple. I know enough to turn that numeric serial into padded text as needed, using [color:green]Right ( "0000" & serial ; 4 ) and it has nothing to do with type casting, or counting on the calculation context, or whether part of the calculation is text and the rest is number.

To each their own.

LaRetta

Edited August 29, 2007 by Guest

[comment]

I don't get this warning either. Filemaker has always done implicit type conversions. Most of this documented. For each function there is a 'Data type returned' parameter clearly stated in the help. Similarly, each calculation field can be set to the desired result type. These two could be even considered explicit. The only gray area, I think, is operators, e.g.:

a & b + c

Here, you need to know the (undocumented) order of evaluation, in order to predict the result type.

In any case, I don't see much difference between "depending on undocumented behaviour" and implementing documented features - both can change at any time.

[David McQueen]

In any case, I don't see much difference between "depending on undocumented behaviour" and implementing documented features - both can change at any time.

For documented behaviour, as versions change there is usually ( but not always) a bridge. For undocumented behaviour, there is a surprise.

I agree on the comment above that since FMP7 there is less need for key concatenation with multiple relation key parameters.

I am not sure which works quicker - large concatenated key or a relationship based on multiple parameters. It is probably a difference that makes no difference until you get onto a WAN.