Finding ANY Related Value

When I use  a Calc field to evaluate the expression

Matchfield=Related::Matchfield

it only compares the first related record (depended by the sort). If I 
do a multi record portal I can see the other "hits" but when they are

not at the top of the portal, I don't get a match.

I don't want to resort every time the matchfield changes in the parent 
file (its' a global). How can get a Calc field in the related file to 
equal "1" whenever there is a match anywhere in the related file, not

just the first record, WITHOUT SORTING?

Thank you for your assistance in advance,

Peter

>     NumMatches     Calculation, Number Result, Unstored
>        = PatternCount(List(Related::MatchField) & "",
>                       MatchField & "
>                      )
>
> where  is the Return character - the "backwards P" on one of
the
> buttons in the Define Calculation window.

> The List function returns all the values separated by a Return, so
> comparing MacthField plus a Return should get around any problems of
> one data item being the sub-set of another (eg. "Fred" and
"Fred
> Flintstone").

That will fail by matching (data:  "Fred Flintstone" with matchfield:
"Flintstone" though. So you really want to put ret before and after
the list and match field... and even that will break if there are
leading or trailing whitespace in any of the data or the matchfield.

But really its a terrible approach because its painfully slow if there
are a lot of related records, especially if its sorted. Think about
what it does -- first it has to concatenate all the values from ALL
the related records and then scan it one character at a time -- yuck.
(And if its on a sorted relationship, it has to perform a sort of all
that data too, before concatenating it...)

I'd recommend that the OP define a relationship on the matchfield
directly so that only matching records will ever be returned in that
relationship in the first place; and don't sort it. Only use sorting
when you need it.

Then if you want how many there are you can count them on their
primary key using the Count function...

countRecords = Count(matchrelationship::primarykey)

or if you just want 'yes/no' there are matching records then define a
calc

hasMatching = not IsEmpty(matchrelationship::primarykey)

Both of these are MUCH faster. The first only has to scan the matching
index entry to see how many rows will be returned, the second can stop
at the first row entry in the matching index entry. (And indexes are
designed to searched quickly, unlike lists of values returned as plain
text.)

cheers,

In article 
<4dcff3a1-d13c-4c38-a740-7199a1264a3c@i29g2000prf.googlegroups.com>,
 d-42 <db.porsche@gmail.com> wrote:

> >     NumMatches     Calculation, Number Result, Unstored
> >        = PatternCount(List(Related::MatchField) &
"",
> >                       MatchField & "
> >                      )
> >
> > where  is the Return character - the "backwards P" on
one of the
> > buttons in the Define Calculation window.
> 
> > The List function returns all the values separated by a Return, so
> > comparing MacthField plus a Return should get around any problems of
> > one data item being the sub-set of another (eg. "Fred" and
"Fred
> > Flintstone").
> 
> That will fail by matching (data:  "Fred Flintstone" with
matchfield:
> "Flintstone" though. So you really want to put ret before and
after
> the list and match field... and even that will break if there are
> leading or trailing whitespace in any of the data or the matchfield.
> 
> But really its a terrible approach because its painfully slow if there
> are a lot of related records, especially if its sorted. Think about
> what it does -- first it has to concatenate all the values from ALL
> the related records and then scan it one character at a time -- yuck.
> (And if its on a sorted relationship, it has to perform a sort of all
> that data too, before concatenating it...)
> 
> I'd recommend that the OP define a relationship on the matchfield
> directly so that only matching records will ever be returned in that
> relationship in the first place; and don't sort it. Only use sorting
> when you need it.
> 
> Then if you want how many there are you can count them on their
> primary key using the Count function...
> 
> countRecords = Count(matchrelationship::primarykey)
> 
> or if you just want 'yes/no' there are matching records then define a
> calc
> 
> hasMatching = not IsEmpty(matchrelationship::primarykey)
> 
> Both of these are MUCH faster. The first only has to scan the matching
> index entry to see how many rows will be returned, the second can stop
> at the first row entry in the matching index entry. (And indexes are
> designed to searched quickly, unlike lists of values returned as plain
> text.)
> 
> cheers,
> Dave

Thank you very much for your reply. I'll give it a try this weekend - I 
have a convention going this week - all those lists and badges...

In article
<ogusa-A1680A.19073624032008@sn-radius.vsrv-sjc.supernews.net>, Peter 
Sturges <ogusa@lava.net> wrote:

> When I use  a Calc field to evaluate the expression
> 
> Matchfield=Related::Matchfield
> 
> it only compares the first related record (depended by the sort). If I 
> do a multi record portal I can see the other "hits" but when they
are 
> not at the top of the portal, I don't get a match.
> 
> I don't want to resort every time the matchfield changes in the parent 
> file (its' a global). How can get a Calc field in the related file to 
> equal "1" whenever there is a match anywhere in the related file,
not 
> just the first record, WITHOUT SORTING?
> 
> Thank you for your assistance in advance,
> 
> Peter
> Honolulu, Hawaii

Any singular function / calculation performed on a related field will
always use data from the first related record in the sort order.
Summary style functions / calculations (eg. Sum or Average) will use
data from all the related records.

If I understand correctly, then there are two main ways you could
achieve this, but the simplest way would be to create a Calculation
field which collates all the related MatchField values into one text
block, which you can then test for the MatchField.
eg.

    NumMatches     Calculation, Number Result, Unstored
       = PatternCount(List(Related::MatchField) & "", 
                      MatchField & "
                     )

where  is the Return character - the "backwards P" on one of the
buttons in the Define Calculation window.

The List function returns all the values separated by a Return, so
comparing MacthField plus a Return should get around any problems of
one data item being the sub-set of another (eg. "Fred" and "Fred
Flintstone").


Helpful Harry                   

In article 
<4dcff3a1-d13c-4c38-a740-7199a1264a3c@i29g2000prf.googlegroups.com>,
 d-42 <db.porsche@gmail.com> wrote:

> countRecords = Count(matchrelationship::primarykey)

Count Records works perfectly. Thank you all for your help.