automated searches

[Rob 7 Collins]

I'd like to offer my users a search page, lookin kinda like this:

Any of:

All of

• Search

I want the search to look in all fields in a large block of tables that are in one relationship tree. "Any of" is AND'ed with "All of" in the search, so if the user ran this search:

Any of:

All of :

• Search

The script should find records with that name and either phrase, regardless of what fields are involved. Assuming I can find an easy way to parse the quotes (no escape characters need be allowed, so there's no worry about quoted text in phrases), I could do this by running a search on each field of each table per every permutation of the "All of" phrases, but that's killer-slow.

I need suggestions: I'm not really getting the FileMaker way.

[David Kachel]

If I understand your dilemma correctly...

In each table add a calc field (stored and indexed) that concatentates the text from all the fields you would like searched, adding a return character between each data piece; for example, if you have fields: Fred, Ethel, Ricky, Lucy; your calc field would be:

Fred & "return character" & Ethel & "return character", etc. Then search on this calc field.

I would reconsider the design of the data entry fields for the search. Requiring users to put multiple phrases in quotes in one field is asking too much. Whatever an end-user can screw up, they WILL screw up.

[David Kachel]

I should add a point here...

The need to search all fields in a "large block of tables" is unusual. I can't say that it shouldn't happen; all sorts of weird stuff has been necessary in DB design at one time or another, but:

It has been my experience that if you find yourself having to design something that seems overcomplicated and unwieldy, there is almost certainly a basic flaw in the DB design.

It is quite common to get down the road of a project and discover that a particular feature/approach dictates that a DB should have been designed differently. This happens a lot when the client keeps moving the goal posts on you, and is why we always get clients to agree on a detailed Design Specifications document before starting a project.

We develop projects in "builds". When we reach a particular feature completion point, we lock that stage away, duplicate it, and start with the duplicate as the next build. This way it is much easier to go back to a previous build and start over when we have hit a dead end with a particular feature/group of features. There is an old saying in software development, (paraphrased), "every software project should be done twice: the second time is to do it correctly, based on what you learned the first time."

Anyway, the above rant is basically to bring me to this point: it might be a good time to take a step back and look at your overall design to be certain that you do indeed need this type of search, and not perhaps some more fundamental redesign.

[Rob 7 Collins]

About the design ...

I have several different "main" tables; providers, programs, catalog entries, etc ... most of which have a need for many different contact records (addresses, etc). So I built the system with all the main tables, and tables for addresses, etc.

Addresses are in sub tables of main tables, in this view ... and the main tables are in a heirarchy as well, with providers running programs each of which has catalog entries from different years, etc.

For the day to day, this search functionality is not necessary... the user could go to the appropriate layout, drop to find mode, and enter data into a field specifically. But what if they are looking for a main table, and need to match partial info from the main table and from some supporting table.

example,

Perhaps she knows the provider's title contains "Kennedy", and a contact person, maybe for one of the programs but perhaps for one of the scholarship reports, matches "Doe". The thing is, there's just as many Kennedy providers as there are Doe contacts... she must use an overall searhc feature like this, or else run several searches and at each returned record scrounge for the contact's name.

One of the main functions for this project is to build a catalog of various entries, which the user should be able to search for in order to select.

...

I started out with the calculated fields like you suggested but ... I need clusters like that for each main record combined with all that record's child/related records to support the "All" phrase.

??