permanent search box to search all fields

[beschaving]

hello everyone,

i'd like to add a kind of itunes/apple mail etc. style search box to the top of my database. so that at any time, you can start typing in the box an it will display all records that include the search term in one of its fields.

like the find/replace command, but with the search box permanently present, and always set to search in both directions, all fields, all records.

could someone point me in the right direction, if this is possible?

thanks!

alexandra

[Ender]

The usual method for this is to simply concatonate all the fields you wish to search on into a big calc field, like:

Name First & ¶ &

Name Last & ¶ &

Name Middle & ¶ &

Address & ¶ &

Phone & ¶ &

...

Then this can be used for a regular find, or you can use it at the match field in a relationship.

For the relationship, the idea is to use a global for the search text (linked to the multi-line calc in the same or another table,) and have a portal show the related records that match the criteria. There are some limitations to this, however. First, you can only show related records from one other table in a portal, so you cannot use this as a search across all tables. Second, this alone would not work for partial matches, like iTunes has; the whole word or phrase would need to be entered in the search field. Third, this will be a performance drag if you have a large number of fields and a large number of records.

The partial match problem can be somewhat overcome by using an exploded key for each of the fields. An exploded key breaks the text down into each sub-string, like:

alexandra

alexandr

alexand

alexan

alexa

alex

ale

al

a

This is the more common type of exploded key, where the text is shortened for each line in a back to front direction. This will allow partial matches of "alex" but not "andra". There are several exploded key/string variations available in custom function format on briandunning.com and other sites, but they require FileMaker 8 Advanced to install them. Implementing them without custom functions is possible, but tedious for long fields.

Adding exploded keys for some of the fields, while improving the matching ability, results in a much larger multi-line calc field and could reduce overall performance. You may have to compromise on how much to include in such a multi-key.

[beschaving]

thank you.

your tip is a little advanced for me, i've never worked with relations before, and i don't know what "concatonate" means :-), but once i read about this stuff in the help files i'll try your advice. thanks again.

[Reed]

You can also Use a global field for the search criteria, and use a script to create a find request for each field that you want to search, the script would input the search criteria for each field, and all matching records would be returned.

[levi777]

Hey - I'm have to do the same thing, and concantonating is working well. Am I correct in assuming that if I set the calculation field to "do not store" it will load that field at runtime thus not making the db any larger?

Also, I kind of doubt this is a possibility when doing it this way, but is there anyway to highlight the found text? I wouldn't think so since my calculated field is not actually displayed, but you guys would know more than I....

Thanks

[Sanjai]

I had tried solving it in my one of my posts:

http://fmforums.com/forum/showtopic.php?tid/169250/

Here is the solution which I had offered:

You can use the go to next field script step which will help you to search on fields if in future you add fields in your file. See a generic script:

Go to layout [<>]

enter find mode []

Go to field [select the first field on the current layout]

Insert Text ["your search string"]

perform script[find others]

script find others:

new record/request

go to next field

if status[current{fieldname}]="first field on the layout" then

perform find[]

halt script

else

insert text["your search string"]

perform script[find others]

end if

//The script find others would insert the search string in every field in find mode. Since after going through all the fields, the go to next field comes back to the first field therefore I have the if condition where if the first field comes back then continue with a perform find and halt the script.