Multiple match fields in a relationship 
Author Message
 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
 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
 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
 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




- Show quoted text -

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




- Show quoted text -

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




- Show quoted text -

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

 Relevant Pages 

1. Is there a limit to match field length in relationships

2. relationship/match field problem

3. Relationship ignores some of match field

4. Matching Fields for Relationships

5. Relationship match fields - unique 20 or 60 characters?

6. Count with multiple matching fields

7. Multiple Field Relationships

8. Remote view field defs WILL NOT match table field defs

9. How to get the match count on multiple word with an OR

10. sql2k join question - only want one match per left regardless of multiples on right

11. multiple table matching

12. match duplicate records to multiple values in same table


 
Powered by phpBB® Forum Software