creating a composite key

[FilemakerPro_Developer]

I have a auto-enter number foreign key field and a type field. I have a
question:

1. field definition is SerialNo & " " & type as text in both tables and I
get an error message when I try to relate them saying I must have the same
number of fields and same data type. Do I need to constrain the number field
as text or does naming the composite key text already change the number to
text?

I don't quite understand the queries but I assume I don't have to create the
composite key in a query? AM I supposed to create this key as a function
instead of a concatenation using the expressions?
Thanks,

 

[KARL DEWEY]

You seem to be off on the wrong foot. Primary keys are in the table
properties, not in a query.
To create a multiple field key, open the table in design view and click on
the left most part of the field row. Highlight the two or more fields for
the multiple key. They do not have to be the same data type.

Foreign keys must match the primary key. If the primay is a text field and
datetime combination then the composition of the foreign key must also
consist of the same datatypes.

You would then create the relationship between the tables.

 

[Al Campagna]

Filemaker,
Don't use the composite field for linking purposes. Just let the autonumber field take
care of itself (you can hide it on the form), and just display a composite key as a
calculated field.
Since you capture the SerialNo, and the Type, anunbound text control with a
ControlSource of...
= SerialNo & " " & Type
can just be displayed to the user on the form.

Key fields are not for users, they are for you to relate tables and provide unique
record identifiers. Use SerialNo (autonum) in your ONE tables and SerialNo (Long) in your
MANY tables for realtionships.

And yes... SerialNo & " " & Type is a Text value.
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[FilemakerPro_Developer]

This makes sense to me although I don't know the difference to bound and
unbound yet.

 

[FilemakerPro_Developer]

Wait a minute it doesn't make sense. If I can't composite the key in the
tables how can I relate the tables.

 

[FilemakerPro_Developer]

Thanks so much this was the right answer. I noticed that you can't enforce
referential integrity with this composite key? I can't get the relationship
to appear in the relationship window.

 

[FilemakerPro_Developer]

It looks like two little relationships instead of one, is that how it is
supposed to look for the composite relationship?

 

[John Vinson]

It looks like two little relationships instead of one, is that how it is
supposed to look for the composite relationship?

Yep. If it's five fields, there'll be five lines.

John W. Vinson[MVP]

 

[Al Campagna]

As yet, we haven't seen your table design/s and key fields... so were really dealing
with general concepts... not specific solutions.

If you need two fields in order to properly relate two tables, then link each field
individually from table to table. You don't combine those fields, and then use them for a
relationship.
As John Vinson indicated in his post...
Table1 Table2
SerNo (auto) -----> SerNo(Long)
Type (text ) -------> Type (text)

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."