Multiple match fields in a relationship
| Author |
Message |
|
Aziz Kar
#1 / 7
|
 Multiple match fields in a relationship Hi,
Is it possible to specify two match fields for a relationship? I need to
display data on a portal - but based on two criteria - which I would like
to specify in a relationship.
Thanks,
Aziz 8)
----------------------------------------------------
Aziz Kara
|
| Wed, 20 Aug 2003 00:20:53 GMT |
|
 |
|
John Weinshe
#2 / 7
|
Multiple match fields in a relationship Concatenate them, in calculation fields on both sides of the relationship. Thus, using First Name and Last Name, create a concatenated field, in each file (if not a self-join): First Name & Last Name
and create a relationship using the two concatenations.
--
John Weinshel
Datagrace
Associate Member, Filemaker Solutions Alliance
Vashon Island, WA
(206) 463-1634
Quote: > Hi,
> Is it possible to specify two match fields for a relationship? I need to
> display data on a portal - but based on two criteria - which I would like
> to specify in a relationship.
> Thanks,
> Aziz 8)
> ----------------------------------------------------
> Aziz Kara
|
| Wed, 20 Aug 2003 02:09:15 GMT |
|
|
|
Aziz Kar
#3 / 7
|
Multiple match fields in a relationship That would work. But what if one of your fields are a multi-key match field with multiple entries separated by carriage returns. Does that mean that you would have to prefix the one field to every entery in the other field? Aziz 8)
Quote:
> Concatenate them, in calculation fields on both sides of the relationship. > Thus, using First Name and Last Name, create a concatenated field, in each > file (if not a self-join): > First Name & Last Name
> and create a relationship using the two concatenations.
> --
> John Weinshel
> Datagrace
> Associate Member, Filemaker Solutions Alliance
> Vashon Island, WA
> (206) 463-1634
> > Hi,
> > Is it possible to specify two match fields for a relationship? I need to
> > display data on a portal - but based on two criteria - which I would like
> > to specify in a relationship.
> > Thanks,
> > Aziz 8)
> > ----------------------------------------------------
> > Aziz Kara
---------------------------------------------------- Aziz Kara
|
| Wed, 20 Aug 2003 02:41:58 GMT |
|
|
|
John Weinshe
#4 / 7
|
Multiple match fields in a relationship Very interesting result. I am sending you a file that plays with that, and I find the results unexpected. Happy to send to anyone upon request. If you create CR delimited entries either of two text fields that get
concatenated together into a calc field used as the match field, the
calculated field
returns first the whole first field, with its list, and then the second,
with its list:
First field:
John
Bob
Jack
Mike
Jane
Fred
Second Field:
Harrah
Nance
Concatation (Field 1 & Field 2):
John
Bob
Jack
Mike
Jane
FredHarrah
Nance
The relationship works on the first field if the second field is empty, but
breaks if the second field is not empty. In other words, the records above
displays inits portal a record with "Bob" as the first field and an empty
second field. But if you enter "Harrah" or "Nance" into Bob's second field,
he is no longer related.
--
John Weinshel
Datagrace
Associate Member, Filemaker Solutions Alliance
Vashon Island, WA
(206) 463-1634
Quote: > That would work. But what if one of your fields are a multi-key match > field with multiple entries separated by carriage returns. Does that mean > that you would have to prefix the one field to every entery in the other > field? > Aziz 8)
> > Concatenate them, in calculation fields on both sides of the
relationship.
> > Thus, using First Name and Last Name, create a concatenated field, in
each
> > file (if not a self-join):
> > First Name & Last Name
> > and create a relationship using the two concatenations.
> > --
> > John Weinshel
> > Datagrace
> > Associate Member, Filemaker Solutions Alliance
> > Vashon Island, WA
> > (206) 463-1634
Quote: > > > Hi,
> > > Is it possible to specify two match fields for a relationship? I need
to
> > > display data on a portal - but based on two criteria - which I would
like
> > > to specify in a relationship.
> > > Thanks,
> > > Aziz 8)
> > > ----------------------------------------------------
> > > Aziz Kara
> ----------------------------------------------------
> Aziz Kara
|
| Wed, 20 Aug 2003 03:54:53 GMT |
|
|
|
Dave Barret
#5 / 7
|
Multiple match fields in a relationship I fail to see what was unexpected. However, I also don't quite understand why one would concatenate lists to create a list of composite keys. (I could understand concatenating 2 lists of keys to create a key "list-of-keys" which will match anything on either list, but that doesn't appear to be the case here.) I was under the impression that Aziz Kara was working on a file of the form
NameFirst NameLast _calc_matchkey
Bob Smith "Bob Smith"
Jim Jones "Jim Jones"
Nancy Drew "Nancy Drew"
Bob Jones "Bob Jones"
Nancy Jones "Nancy Jones"
And then he wants to take something like:
Field 1:
"Bob
Jim"
Field 2:
"Smith
Jones"
and use them in a relationship to return: "Bob Smith", "Bob Jones", and "Jim
Jones"
This of course requires that we have a list of composite-keys as the match
field on the left side of the relationship:
"Bob Smith
Bob Jones
Jim Smith
Jim Jones"
As you said, if the two fields are "singletons" you can simply concatenate
them to create a composite match key (f1="Bob", f2 = "Jones", matchkey="Bob"
& " " &"Jones" = "Bob Jones"). This of course is standard practice in
Filemaker circles. However, if the two fields are lists then you probably
wouldn't want to concatenate them, you want the cross-product of the two
lists. (Technically a composite match field is always a cross product of
its components but we can use concatenation as mathematically it has the
same result as a cross-product when the arguments are singletons). Filemaker
of course doesn't provide a cross-product function (or any other matrix/list
operation). It would be possible to write a calculation field to build
cross-products of lists of known fixed and/or very limited dimensions;
however in general this would require a script.
Another way of interpreting Aziz's question is that he wants to take:
"Jim
Bob"
and
"Smith
Jones"
to find only "Jim Smith" and "Bob Jones". This is a simpler problem but the
solution is derived the same way , as the list of composite keys is
essentially a matrix addition instead of a matrix cross-product of the two
lists.
-Dave Barreto
Quote: > Very interesting result. I am sending you a file that plays with that, and I > find the results unexpected. Happy to send to anyone upon request. > If you create CR delimited entries either of two text fields that get
> concatenated together into a calc field used as the match field, the
> calculated field
> returns first the whole first field, with its list, and then the second,
> with its list:
> First field:
> John
> Bob
> Jack
> Mike
> Jane
> Fred
> Second Field:
> Harrah
> Nance
> Concatation (Field 1 & Field 2):
> John
> Bob
> Jack
> Mike
> Jane
> FredHarrah
> Nance
> The relationship works on the first field if the second field is empty,
but
> breaks if the second field is not empty. In other words, the records above
> displays inits portal a record with "Bob" as the first field and an empty
> second field. But if you enter "Harrah" or "Nance" into Bob's second
field,
> he is no longer related.
> --
> John Weinshel
> Datagrace
> Associate Member, Filemaker Solutions Alliance
> Vashon Island, WA
> (206) 463-1634
> > That would work. But what if one of your fields are a multi-key match
> > field with multiple entries separated by carriage returns. Does that
mean
> > that you would have to prefix the one field to every entery in the other
> > field?
> > Aziz 8)
> > > Concatenate them, in calculation fields on both sides of the
> relationship.
> > > Thus, using First Name and Last Name, create a concatenated field, in
> each
> > > file (if not a self-join):
> > > First Name & Last Name
> > > and create a relationship using the two concatenations.
> > > --
> > > John Weinshel
> > > Datagrace
> > > Associate Member, Filemaker Solutions Alliance
> > > Vashon Island, WA
> > > (206) 463-1634
> > > > Hi,
> > > > Is it possible to specify two match fields for a relationship? I
need
> to
> > > > display data on a portal - but based on two criteria - which I would
> like
> > > > to specify in a relationship.
> > > > Thanks,
> > > > Aziz 8)
> > > > ----------------------------------------------------
> > > > Aziz Kara
> > ----------------------------------------------------
> > Aziz Kara
|
| Thu, 21 Aug 2003 22:55:02 GMT |
|
|
|
John Weinshe
#6 / 7
|
Multiple match fields in a relationship Dave, I am having trouble following your explanation. I guess I'm confused about
what you mean by "singletons" and "composite match key".
It finally occurred to me (this is an aside) that the double concatenation
was failing as a primary key because it exceeds (on the right side, where it
matters) the 20 character index limitation.
Thanks,
John
Quote: > I fail to see what was unexpected. However, I also don't quite understand > why one would concatenate lists to create a list of composite keys. (I could > understand concatenating 2 lists of keys to create a key "list-of-keys" > which will match anything on either list, but that doesn't appear to be the > case here.) > I was under the impression that Aziz Kara was working on a file of the
form
> NameFirst NameLast _calc_matchkey
> Bob Smith "Bob Smith"
> Jim Jones "Jim Jones"
> Nancy Drew "Nancy Drew"
> Bob Jones "Bob Jones"
> Nancy Jones "Nancy Jones"
> And then he wants to take something like:
> Field 1:
> "Bob
> Jim"
> Field 2:
> "Smith
> Jones"
> and use them in a relationship to return: "Bob Smith", "Bob Jones", and
"Jim
> Jones"
> This of course requires that we have a list of composite-keys as the match
> field on the left side of the relationship:
> "Bob Smith
> Bob Jones
> Jim Smith
> Jim Jones"
> As you said, if the two fields are "singletons" you can simply concatenate
> them to create a composite match key (f1="Bob", f2 = "Jones",
matchkey="Bob"
> & " " &"Jones" = "Bob Jones"). This of course is standard practice in
> Filemaker circles. However, if the two fields are lists then you probably
> wouldn't want to concatenate them, you want the cross-product of the two
> lists. (Technically a composite match field is always a cross product of
> its components but we can use concatenation as mathematically it has the
> same result as a cross-product when the arguments are singletons).
Filemaker
> of course doesn't provide a cross-product function (or any other
matrix/list
> operation). It would be possible to write a calculation field to build
> cross-products of lists of known fixed and/or very limited dimensions;
> however in general this would require a script.
> Another way of interpreting Aziz's question is that he wants to take:
> "Jim
> Bob"
> and
> "Smith
> Jones"
> to find only "Jim Smith" and "Bob Jones". This is a simpler problem but
the
> solution is derived the same way , as the list of composite keys is
> essentially a matrix addition instead of a matrix cross-product of the two
> lists.
> -Dave Barreto
> > Very interesting result. I am sending you a file that plays with that,
and
> I
> > find the results unexpected. Happy to send to anyone upon request.
> > If you create CR delimited entries either of two text fields that get
> > concatenated together into a calc field used as the match field, the
> > calculated field
> > returns first the whole first field, with its list, and then the second,
> > with its list:
> > First field:
> > John
> > Bob
> > Jack
> > Mike
> > Jane
> > Fred
> > Second Field:
> > Harrah
> > Nance
> > Concatation (Field 1 & Field 2):
> > John
> > Bob
> > Jack
> > Mike
> > Jane
> > FredHarrah
> > Nance
> > The relationship works on the first field if the second field is empty,
> but
> > breaks if the second field is not empty. In other words, the records
above
> > displays inits portal a record with "Bob" as the first field and an
empty
> > second field. But if you enter "Harrah" or "Nance" into Bob's second
> field,
> > he is no longer related.
> > --
> > John Weinshel
> > Datagrace
> > Associate Member, Filemaker Solutions Alliance
> > Vashon Island, WA
> > (206) 463-1634
> > > That would work. But what if one of your fields are a multi-key match
> > > field with multiple entries separated by carriage returns. Does that
> mean
> > > that you would have to prefix the one field to every entery in the
other
> > > field?
> > > Aziz 8)
> > > > Concatenate them, in calculation fields on both sides of the
> > relationship.
> > > > Thus, using First Name and Last Name, create a concatenated field,
in
> > each
> > > > file (if not a self-join):
> > > > First Name & Last Name
> > > > and create a relationship using the two concatenations.
> > > > --
> > > > John Weinshel
> > > > Datagrace
> > > > Associate Member, Filemaker Solutions Alliance
> > > > Vashon Island, WA
> > > > (206) 463-1634
Quote: > > > > > Hi,
> > > > > Is it possible to specify two match fields for a relationship? I
> need
> > to
> > > > > display data on a portal - but based on two criteria - which I
would
> > like
> > > > > to specify in a relationship.
> > > > > Thanks,
> > > > > Aziz 8)
> > > > > ----------------------------------------------------
> > > > > Aziz Kara
> > > ----------------------------------------------------
> > > Aziz Kara
|
| Fri, 22 Aug 2003 02:17:46 GMT |
|
|
|
Dave Barret
#7 / 7
|
Multiple match fields in a relationship Hmm... I just wanted to clarify that "what I failed to see" really was what result you encountered that was unexpected, and that it was not some sarcastic retort that everything you said "was expected". -Dave
Quote: > I fail to see what was unexpected. However, I also don't quite understand > why one would concatenate lists to create a list of composite keys. (I could > understand concatenating 2 lists of keys to create a key "list-of-keys" > which will match anything on either list, but that doesn't appear to be the > case here.) > I was under the impression that Aziz Kara was working on a file of the
form
> NameFirst NameLast _calc_matchkey
> Bob Smith "Bob Smith"
> Jim Jones "Jim Jones"
> Nancy Drew "Nancy Drew"
> Bob Jones "Bob Jones"
> Nancy Jones "Nancy Jones"
> And then he wants to take something like:
> Field 1:
> "Bob
> Jim"
> Field 2:
> "Smith
> Jones"
> and use them in a relationship to return: "Bob Smith", "Bob Jones", and
"Jim
> Jones"
> This of course requires that we have a list of composite-keys as the match
> field on the left side of the relationship:
> "Bob Smith
> Bob Jones
> Jim Smith
> Jim Jones"
> As you said, if the two fields are "singletons" you can simply concatenate
> them to create a composite match key (f1="Bob", f2 = "Jones",
matchkey="Bob"
> & " " &"Jones" = "Bob Jones"). This of course is standard practice in
> Filemaker circles. However, if the two fields are lists then you probably
> wouldn't want to concatenate them, you want the cross-product of the two
> lists. (Technically a composite match field is always a cross product of
> its components but we can use concatenation as mathematically it has the
> same result as a cross-product when the arguments are singletons).
Filemaker
> of course doesn't provide a cross-product function (or any other
matrix/list
> operation). It would be possible to write a calculation field to build
> cross-products of lists of known fixed and/or very limited dimensions;
> however in general this would require a script.
> Another way of interpreting Aziz's question is that he wants to take:
> "Jim
> Bob"
> and
> "Smith
> Jones"
> to find only "Jim Smith" and "Bob Jones". This is a simpler problem but
the
> solution is derived the same way , as the list of composite keys is
> essentially a matrix addition instead of a matrix cross-product of the two
> lists.
> -Dave Barreto
> > Very interesting result. I am sending you a file that plays with that,
and
> I
> > find the results unexpected. Happy to send to anyone upon request.
> > If you create CR delimited entries either of two text fields that get
> > concatenated together into a calc field used as the match field, the
> > calculated field
> > returns first the whole first field, with its list, and then the second,
> > with its list:
> > First field:
> > John
> > Bob
> > Jack
> > Mike
> > Jane
> > Fred
> > Second Field:
> > Harrah
> > Nance
> > Concatation (Field 1 & Field 2):
> > John
> > Bob
> > Jack
> > Mike
> > Jane
> > FredHarrah
> > Nance
> > The relationship works on the first field if the second field is empty,
> but
> > breaks if the second field is not empty. In other words, the records
above
> > displays inits portal a record with "Bob" as the first field and an
empty
> > second field. But if you enter "Harrah" or "Nance" into Bob's second
> field,
> > he is no longer related.
> > --
> > John Weinshel
> > Datagrace
> > Associate Member, Filemaker Solutions Alliance
> > Vashon Island, WA
> > (206) 463-1634
> > > That would work. But what if one of your fields are a multi-key match
> > > field with multiple entries separated by carriage returns. Does that
> mean
> > > that you would have to prefix the one field to every entery in the
other
> > > field?
> > > Aziz 8)
> > > > Concatenate them, in calculation fields on both sides of the
> > relationship.
> > > > Thus, using First Name and Last Name, create a concatenated field,
in
> > each
> > > > file (if not a self-join):
> > > > First Name & Last Name
> > > > and create a relationship using the two concatenations.
> > > > --
> > > > John Weinshel
> > > > Datagrace
> > > > Associate Member, Filemaker Solutions Alliance
> > > > Vashon Island, WA
> > > > (206) 463-1634
Quote: > > > > > Hi,
> > > > > Is it possible to specify two match fields for a relationship? I
> need
> > to
> > > > > display data on a portal - but based on two criteria - which I
would
> > like
> > > > > to specify in a relationship.
> > > > > Thanks,
> > > > > Aziz 8)
> > > > > ----------------------------------------------------
> > > > > Aziz Kara
> > > ----------------------------------------------------
> > > Aziz Kara
|
| Sat, 23 Aug 2003 16:35:48 GMT |
|
| |
|
