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,
--
Janis

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.

"FilemakerPro_Developer" wrote:

> 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,
> --
> Janis

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" <(E-Mail Removed)> wrote in
message news:6D506705-7EF7-4D56-827F-(E-Mail Removed)...
>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,
> --
> Janis

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


"Al Campagna" wrote:

> 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" <(E-Mail Removed)> wrote in
> message news:6D506705-7EF7-4D56-827F-(E-Mail Removed)...
> >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,
> > --
> > Janis
>
>
>

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


"Al Campagna" wrote:

> 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" <(E-Mail Removed)> wrote in
> message news:6D506705-7EF7-4D56-827F-(E-Mail Removed)...
> >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,
> > --
> > Janis
>
>
>

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.
--
Janis


"KARL DEWEY" wrote:

> 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.
>
> "FilemakerPro_Developer" wrote:
>
> > 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,
> > --
> > Janis

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


"KARL DEWEY" wrote:

> 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.
>
> "FilemakerPro_Developer" wrote:
>
> > 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,
> > --
> > Janis

On Thu, 21 Dec 2006 16:50:00 -0800, FilemakerPro_Developer
<(E-Mail Removed)> wrote:

>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]

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...
>> If it's five fields, there'll be five lines. (individual realationships)

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."


"FilemakerPro_Developer" <(E-Mail Removed)> wrote in
message news:F1A0886F-8D30-47C6-8C16-(E-Mail Removed)...
> Wait a minute it doesn't make sense. If I can't composite the key in the
> tables how can I relate the tables.
> --
> Janis
>
>
> "Al Campagna" wrote:
>
>> 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" <(E-Mail Removed)> wrote in
>> message news:6D506705-7EF7-4D56-827F-(E-Mail Removed)...
>> >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,
>> > --
>> > Janis
>>
>>
>>