Finding in multiple fields

[eoinmurphy]

Hi

I'm new here, hope to be able to contribute and help others in time.

But first of all I have had a query from a user that I can't answer and was hoping you could help.

The solution that I have has many fp5 files and many records. A user wants to search for strings of text that could be in any field (excluding summary, etc that cannot be searched). The user may not know the field that holds the correct search result and will not know where to search. Ideally we could have one place to enter the query and this would search each database and every possible field in each.

Is there a way to do this. I can imaging that if there is it is very slow.

If it can be done the next issue is that of presenting the answers (ie record 15 from A1.fp5, record 28 from A3.fp5, etc.) What is sensible?

I dont suppose that this makes any sense to anyone out there .

Cheers

Eoin

[QuinTech]

Hi, Eoin, and welcome to FM Forums. (And thanks for your generous offer--many will take you up on that!)

Two ways i can think of to do this:

1. Create a concatenated field that includes all fields where text could possibly be, and have the user search there. That is, have a calc field called "AllText" which is equal to: A1 & " " & A2 & " " & A3 & " " & ... & " " & An, where A1 thru An are the field you want to include. Make it clear to users that AllText is the preferred search field.

2. Create a global text field into which users can enter criteria, then use a script to enter find mode and set each A1 thru An to the contents of that. Your script would look like:

Enter Find Mode

Set Field ( A1, "AllText" )

New Record/Request

Set Field ( A2, "AllText" )

New Record/Request

...

New Record/Request

Set Field ( An, "AllText" )

Perform Find

This method is possibly quicker (can anyone vouch for that?), but has the drawback that if the user hits 'Enter' rather than clicking the button that executes the script, he may get unexpected results.

As for the second question:

You'd want to trap for unfound criteria, but assuming records were found, results could be displayed in a calc field called FoundFields:

Case ( PatternCount ( A1 , AllText ) and PatternCount ( A2 , AllText ) and ... PatternCount ( An , AllText ) , "A1A2...An" ,

...

)

If you get the logic behind my Case statement, you'll see that it could be tough to write, and someone may have a better idea of how to do this. But basically, it would check all fields for the criteria, and if all fields contain it, returns "A1A2...An". If not, it checks all fields but one, with every permutation of that, and returns the appropriate result.

The significance of the character is that if you display the field as radio buttons or checkboxes, then all the items listed will be displayed. Thus, a field whose (text) contents are "A1A3A5" when displayed as checkboxes will show A1, A3, and A5 as checked off, but not A2 and A4.

HTH.

Jerry

[bruceR]

For Mac users, about 8 years ago I made a utility that does this called Find Anywhere, available here: http://concisedevelopment.com/findany.htm