Re: Finding ANY Related Value

In article
<4dcff3a1-d13c-4c38-a740-7199a1264a3c@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
d-42 <db.porsche@xxxxxxxxx> wrote:

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

where {ret} 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...

Peter
.

Quantcast