Back on the subject of FileMaker..

I'm trying to do a keyword search. My first preference is to use a
relational search because the results will appear as a portal. Also,
the use of an index should be faster than repetitive 'PERFORM FIND's.
The basic principle is simple and fast for an 'OR' search, but I'm stuck
on the 'AND' search. An additional complication is that I want to be
'multi-user safe', so I don't want to be modifying the data with flags.

The idea is to split out the words in a 'keyword' field to a separate
table when the record is created. Then a search is just a relation
between a repeating variable containing the search terms and the keyword
table. That technique finds records that match ANY of the given
keywords. Those results also include duplicate references for records
that match multiple terms. I want to allow an 'AND' option that will
only find records that match ALL the search words (and without
duplication).

It seems like I need to somehow repeatedly constrain the 'OR' results by
additional searches for each individual search term. That would result
in a set that only those records that contained all the terms. Yet that
is crossing a relational lookup with a programmed 'find'. I'm out of my
depth in DB theory as well as FileMaker technology.

Let's say I somehow use 'PEFORM FIND' to get a 'found set' consisting of
only the desired records. How can I brings a 'found set' into a portal?
The obvious approach of tagging records would mean single-user only.

Can anyone help with a clue for doing a multi-term 'AND' search which
doesn't modify the data?

Thank you,
Andy

I use (FMP6) for that a 'token' field equal in both files to
'criterion#1 & "/" & criterion#2' (for 2 simultaneous criterions). Then
the relationship between the 2 files is based on token in FileA and
token in FileB.
Remi-Noel

"Andy Winslow" <bounce (AT) allspam (DOT) net> a écrit dans le message de news:
X7lme.1917$s64.572 (AT) newsread1 (DOT) news.pas.earthlink.net...

Andy Winslow <bounce (AT) allspam (DOT) net> wrote:

You have a couple of choices to show results in a portal. One uses Find
and one is entirely relational.

1. Uses Find in the child table and a relation from a global in the
parent to the Key field in the child. Steps:

A. Capture the search terms typed by the user into a global
B. Transfer to child table, run script that parses each value in the
global into a separate Find request (OR find). Perform the find. (hint,
use MiddleValues for the parsing)
C. If results are found, go to utility layout in the child file, which
has only one field, the Key field.
D. Use the Copy All Records script step. This places in the Clipboard a
set of all Key values, separated by returns.
E. Return to parent file to a utility layout that has only the global
used as the relating field, PASTE the results from the clipboard into
the global. (global should be text type) Commit record.
F. Return to the viewing layout. Results will appear in the portal.

This method is reliably multi-user, as each separate user can have their
own captured found set of values in the global.

A plus of this method is that the set of captured IDs can be preserved
for the user as a "canned search" by storing the data in a field in a
table.

2. Uses recursive calc in child file to create a field containing each
single word of the search file on a separate line. Steps:

A. In child file, use recursive calc to parse search field into

This
field
contains
search
values

form.

B. In parent file, create text field for entering search terms. Set
behavior to exit field on Tab or Enter, not Return.
C. Make relationship from entry field to parsed calc in child.
D. Display portal from relationship.
E. When user enters

search
george
smith

They will see all records that contain ANY of those values in the search
field, due to the way multi-line text key relationships work in FM. Any
line will match to any line in the child key.

For references to constructing recursive calcs, see the online FM help,
www.briandunning.com or www.clevelandconsulting.com, both of which have
extensive custom function resources, including building recursive
functions.

This second method is also multi-user, if the search field is a global.
If the search field is NOT a global, all users will see the same search
and results. Build as appropriate.

Warning, this method will increase the overhead in the child file
substantially, depending on how long the search field is, and may impact
performance.

Recursive functions will only recurse 10,000 times, so if your field is
more than 10,000 values long, I'd pursue the first method. It's
surprisingly quick in operation, and a judicious use of Freeze Window
will keep users from knowing what happens behind the screens.

Lynn Allen
--
Allen & Allen Semiotics www.semiotics.com
FSA Associate Filemaker Design & Consulting

In article <X7lme.1917$s64.572 (AT) newsread1 (DOT) news.pas.earthlink.net>,
Andy Winslow <bounce (AT) allspam (DOT) net> wrote:

First, know that you can perform a find for any text in any field. You
can do searches by multiple criteria, which constitute a Boolean "AND",
or by multiple requests, which constitutes a Boolean "OR", or by
combinations of these.

If you use a portal field in a Find criterion, that will return all the
parent records that meet that criterion in the portal field.

So I am not sure why you want to use a keyword field.

You can put specified values in a value list, then format a field in any
layout to produce a pop-up list of those values. You can then select the
appropriate value in either the Find or Browse mode.

You can script all of this.

FMP 7 has more powerful capabilities along these lines than does FMP 6
or earlier.

Bill Collins

--
For email, remove invalid.

My previous post on the subject was somewhat erroneous. So here is the
correct (I hope !) answer :
- if you want to show in a portal all the records where the field
'MyField' meets 'criterion1' and all the records where the same field
'MyField' meets 'criterion2', the relationship will be made on : on the
left a global = 'criterion1 & "¶" & criterion2' and on the right
'MyField'. For example you may show in a portal all the records which
meet in the 'City' field either 'Paris' or 'Marseille'.
- if you want to show in a portal only the records of which the couples
'MyField1' 'MyField2' are the same in the left and right files, the
relationship will be made on : on the left 'token' and on the right
'token', where token = 'Myfield1 & "/" & MyField2'. For example in the
record having 'Paris' in the 'City' field and 'Martin' in the 'Name'
field, the portal based on token = City & "/" & Name will show all the
related records having simultaneously 'Paris' and 'Martin', ie all the
'Martin' living in 'Paris'.
Remi-Noel

"Remi-Noel Menegaux" <rnmenegaux (AT) free (DOT) fr> a écrit ...

Bill wrote:
Thanks for the reply, Bill. I didn't see it until today, because as
soon as I saw Lynn's reply, I knew that was all I needed. Well, nobody
cares about month-old messages, but I wanted to tie up some loose ends...

I thought the use of the term "keyword search" would describe both the
problem and the need for a "natural" solution. Your simple "portal
find" can't be used because one field must be matched multiple times.
That field can't be broken into multiple search fields because the
number of terms and their sequence can't be frozen in a layout. And the
user couldn't possibly be asked to learn FMP for "multiple requests",
even if he wanted to do an "OR" search. Think "natural solution".

The answer was in doing a (scripted) conventional "Find" multiple times,
using "Contrain" after the first time, then using "Copy All" to bring
the results back into a portal. An "OR" search can easily be accomodated
by having the script choose "Constrain" or "Extend". Thank you Lynn.

Despite my suspicions, a sequence of "Find"s is very fast, even with
200K records. Filemaker is more powerful than I thought.

Andy