Boolean Searches

[Horatio77]

I'm trying to better understand complex searching in Filemaker 10, specifically if and how it maps to Boolean sets.

Let's say I have a database with 20,000 records each with one field containing a random paragraph of text. How would you suggest I search that field using the normal search interface for arbitrary combinations of values.

For instance:

("*a*" [AND] "*b*") [OR] ("*c*" [AND] "*d*")

Or:

("*Super duper*" [OR] [NOT] "*b*") [OR] !

Or perhaps:

("*Young*" [AND] "*Tom*") [XOR] [NOT] "*Fred*"

Producing Boolean operations is oddly wrapped up in 'New Record / Request', 'Constrain / Extend Found Set' and 'Omit' in ways that have non obvious implications. I'm stumped on how to handle parenthetical [AND] and [OR] queries like the first example. Perhaps there are better ways for me to think about searching?

My ultimate goal is to build a custom search interface for my company's job tracking database, but I don't want to lose the power of the default interface. It will be hosted on a FM9 server and accessed via FM10 clients.

Are there other resources you guys like that go into detail about Filemaker searching?

[bcooney]

You really do not need to build a custom search interface. Typically, a layout is dedicated for Find mode (one for each table).

Also, although you can search a random paragraph of text, it's more typical to search fields.

Logical AND searches are when all search criteria is entered on the same find request.

Logical OR searches require multiple find requests.

OMIT is your NOT.

Constain and Extend are really new versions of Find with New Request or Omits.

As far as resources, have you read FM Help on Find? It's pretty detailed.

[Lee Smith]

Are there other resources you guys like that go into detail about Filemaker searching?

How about the User Guide and Online Help.

bcooney has explained how the AND and OR Operators, and the Omit Request work.

I would be careful using the [color:red]* operator until you try it with an actural database and fields.

Read up on the Operators, they will help you figure out what to use and when.

Lee

[Horatio77]

I understand that there are many avenues for failure in trying to recreate the search tool, but there a few features I'd like to have (though I may not need), that compel me to try.

1) I don't want the search process or its results to take the user away from their current record or 'location'. A bit like Apple's Spotlight, not the 'as you type' aspect, but that it's always available and never takes you away from your data.

2) I'd like the user to be able to search most fields without showing them most fields. e.g. some kind of dwindling lists.

3) I'd like value lists during searches that are different than during data entry.

Before I can even try to implement my own version of search, I really need to get my head around FM's native version.

To your comment on searching paragraphs, I don't actually expect to do that much. As an example, however, it demonstrates that a logical [AND] operation on a single field seems to require iterative queries with 'Constrain Found Set'. This has performance implications over a network, and gets very complicated if you throw an [OR] in like I did in the first example.

Logical [OR] is pretty straight forward, using 'New Record / Request', except when used with Omit, where they get fused to become [AND][NOT], rather than being strictly one or the other. 'Exclusive or' [XOR], has me stumped.

I looked at the help files quite a while ago, but perhaps I should give myself a refresher.

[bcooney]

1. Not really clear. You can create a new window and find in that. Close window when done. User's right back where they started. However, you'll have to manage multiple windows.

2. Search without showing fields? An example would help. You can concatentate fields and show them that field in Find mode. Be careful of unstored calcs, they aren't indexed and are slow to Find on.

3. Very easy. Just assign different value list to the field. How valid they'll be is up to you...

[comment]

it demonstrates that a logical [AND] operation on a single field seems to require iterative queries with 'Constrain Found Set'

That's certainly not true. To take your first example, to find:

("*a*" [AND] "*b*") [OR] ("*c*" [AND] "*d*")

First request:

*a* *b*

Second request:

*c* *d*

[Fitch]

If this isn't exactly what you need, you might at least get some inspiration from it:

www.seedcode.com/fmsearchresults/

[Horatio77]

That's certainly not true. To take your first example, to find:

("*a*" [AND] "*b*") [OR] ("*c*" [AND] "*d*")

First request:

*a* *b*

Second request:

*c* *d*

Hmm, indeed, for some reason I thought request "*a* *b*" wouldn't find "ba". Thanks for pointing that out.

Perhaps you can help me with this too. It seems that FM has [OR] via 'New Request' and [AND][NOT] via 'Omit', but no unadorned [NOT]. 'Omit' always constrains, so [OR][NOT] is not an available search. Let's say I have three fields, 'Name', 'Day' and 'Done'. I want to search for:

[NOT]Tom [OR] [NOT]Friday [OR] [NOT]Done

In FM I might expect:

First Request - Omit

Table::Name Tom

Second Request - Omit

Table::Day Friday

Third Request - Omit

Table::Done Done

If 'new requests' are a boolean [OR] I would expect a Venn diagram of the found set to be like the shaded part of OR NOT, but it instead appears to be an AND NOT.

To get FM to do that search as an OR NOT I think I'd have to add:

Fourth Request - Find

Table::Name Tom

Table::Day Friday

Fifth Request - Find

Table::Name Tom

Table::Done Done

Sixth Request - Find

Table::Day Friday

Table::Done Done

Do I have this right?

[Horatio77]

If this isn't exactly what you need, you might at least get some inspiration from it:

www.seedcode.com/fmsearchresults/

Thanks. That looks pretty awesome, I'll check out their demo.

[comment]

Well, the thing is that an omit request is always ANDed. It may not be the most logical arrangement, but it works quite well in practice.

To do an actual OR NOT, you would have to use Extend Found Set, or simply:

[NOT]Tom [OR] [NOT]Friday [OR] [NOT]Done

=

NOT (Tom AND Friday AND Done)

and this can handled easily by a single request.

[Horatio77]

Well, the thing is that an omit request is always ANDed. It may not be the most logical arrangement, but it works quite well in practice.

To do an actual OR NOT, you would have to use Extend Found Set, or simply:

[NOT]Tom [OR] [NOT]Friday [OR] [NOT]Done

=

NOT (Tom AND Friday AND Done)

and this can handled easily by a single request.

Thank you. Yes, if Omits are always ANDed there is no reason to split them into multiple requests looking to get an OR. Simply using FM's natural AND in a single request makes sense.

[Wine]

I would like to extend the question a little bit. I would like to create a Boolean search feature on a resume field. What would be the best way to create an/a interface/script and not to let user use the menu bar to perform a Boolean search for data such as the following and make sure more extensive search is covered?

administrator AND support AND (Weblogic or “web logic”) and (MQseries or “MQ Series”)

Edited August 10, 2009 by Guest