Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Primary Key and Composite Key function


andrewlu7

Recommended Posts

I need the Primary Key and Composite Key functions as in ACCESS.

 

What function/setting should I do in FileMaker?

 

Thanks,

Andrew

Link to comment
Share on other sites

The simple explanation...

 

A primary key is any field that contains data UNIQUE to a record. Like a serial number. You can do this in any number of ways. For example, when you create a new field you can go to 'options' in the define fields dialog box and under the 'auto-enter' tab set a value in serial number.... this can be any old thing like "FileA00001". You can set the increment value to whatever... the default is 1 of course.

 

Now, each record will have a unique value in this "primary key" field. Record one will get FileA00001 and record two will get FileA00002, etc.

 

Often, primary key fields are calculations which include information of value as well as a serial number. I use a calculation that includes the year, month, day, hour, minutes, and seconds of creation, logged user code, and a four digit serial number.

 

Remember that filemaker only indexes the first 20 characters of a field.

 

A primary key is simply unique. You may never see it. Access makes one so you don't forget to do so. Make your own in Filemaker.

 

N.B.

The term "Primary Key" and "Foriegn Key" are sometimes used to refer to the fields used in a relationship.

Link to comment
Share on other sites

Loved your explanation Riley. But I didn't want anyone to misunderstand something you said, so I'd like to clarify what I think you meant -

 

Remember that filemaker only indexes the first 20 characters of a field.
FileMaker indexes the first 20 characters per WORD and a total for the field of 60 characters including spaces. smile.gif

 

Kind regards,

LaRetta

Link to comment
Share on other sites

Thanks for lovely details.

 

I'm building a parts list data base and console for our factory. It contains MODEL# and REF#. Both values are unique in the whole database. If a duplicate record is entered, the console will prompt the end user.

 

There're many REF# in each MODEL#. Those REF# in those MODEL# are same value but different content. i.e. the REF# is dependent of MODEL#, it's unique.

 

e.g. "R101", "R102" and "R103" are the records of REF# field in Toy, of course, "Toy" is one of the records in MODLE# field. While another "R101", "R102" and "R103" exist in Game. The "Game" is the record in MODEL# field.

 

We expect the filemaker will prompt error, if the 2nd R102 are entered in Toy. B'cos R102 in Toy is already exist there.

 

In ACCESS, we can use primary key and composite key for that function.

 

What shall we do with filemaker?

 

Thanks,

Andrew

Link to comment
Share on other sites

If the Ref. # R101 is used in the same field on any more than ONE record, it is no longer unique, rendering it useless as a single ID key for look ups and/or portals. To be unique, an ID MUST be the only such designation in that field in the ENTIRE file (db).

 

If the Model number is unique (by above designation) than you can create a concatenation of the model number and the Ref number to isolate only those models which have that reference such as:

 

Calc (Text)

ModelNoField & RefNoField

 

which would return "ToyR101" which may or may not be unique based upon whether one or more of the data in the fields is truly unique itself.

 

This is why auto generated serial numbers are so often employed, to ensure uniqueness.

 

Be well . . .

 

laugh.gif Tom

Link to comment
Share on other sites

Originally posted by LaRetta:

[qb]Loved your explanation Riley. But I didn't want anyone to misunderstand something you said, so I'd like to clarify what I think you meant -

 

Remember that filemaker only indexes the first 20 characters of a field.
FileMaker indexes the first 20 characters per WORD and a total for the field of 60 characters including spaces. smile.gif

 

Kind regards,

LaRetta[/qb]

Well actually, that's not quite correct either, LaRetta.

 

For the purposes of relationship matching, current versions of FileMaker index the up to 20 characters per word and up to 60 characters per line.

 

Therefore, providing all the lines are 60 characters or less in length, FileMaker will index and match on up to 64,000 characters per field. wink.gif

Link to comment
Share on other sites

I think you meant the first 20 characters of each word (not field) per line, up to a max of 60 characters per line.

 

However, it is important to note that characters that normally act as word delimiters (such as ~, |, and _ ) do NOT act as word delimiters for relationship matching indexing purposes.

 

So,

000001-000001-000001-000001

 

will (incorrectly) match:

000001-000001-000001-000001 and

000001-000001-000001-000002 and

000001-000001-000001-000003

 

Where as

000001 000001 000001 000001

 

will (correctly) only match

000001 000001 000001 000001

 

and not

000001 000001 000001 000002 or

000001 000001 000001 000003

 

The moral of the story is:

When creating multikeys, use spaces to delimit the keys, not dashes or underscores or other characters.

Link to comment
Share on other sites

I'm afraid that's not correct Mariano.

 

Hyphens are not treated as word separators for relationship matching or most other things, but other characters such as pipes, tildes and underscores are. The only occasion when this is not true is when the ASCII indexing option has been selected for the field in question.

 

So whereas you are correct in saying that:

 

000001-000001-000001-000001

 

will (incorrectly) match:

000001-000001-000001-000001 and

000001-000001-000001-000002 and

000001-000001-000001-000003

 

with FileMaker's default indexing,

000001_000001_000001_000001

 

will correctly match to

000001_000001_000001_000001

 

but will not match to

000001_000001_000001_000002 or

000001_000001_000001_000003

 

and similarly,

000001~000001~000001~000001

will correctly match to

000001~000001~000001~000001

 

but will not match to

000001~000001~000001~000002 or

000001~000001~000001~000003

 

and

 

000001|000001|000001|000001

 

will correctly match to

000001|000001|000001|000001

 

but will not match to

000001|000001|000001|000002 or

000001|000001|000001|000003

 

So if there is a 'moral' it is that characters that work as word separators in other situations also work as word separators for determining the key length for relationship matching - and only if you have explicitly turned on the non-standard ASCII indexing option for the field in question, is it necessary to beware of various ASCII symbols - which will then be counted as part of the 'words'.

 

The point about hyphens not being treated as word separators is valid, however. wink.gif

Link to comment
Share on other sites

Whoops! You're right about the pipe and tilde seperating the words, Ray. I thought I'd checked those in addition to the dash, but was obviously mistaken.

 

There are a few situations where the dash is considered a word seperator. The wordcount function increments the word count for each dash. Also, when using the keyboard to move through text, holding the control key and typing the right or left arrow causes the cursor to jump to the next word, which it does with the dash as well as the others.

 

Thanks for the clarification!

Link to comment
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use