build a drill-down search and display what was searched for on the results page?

[iwasnevy]

In the search pages of my database layouts, I have a button button set up to perform the find and take me to the search results on a different layout (in list form).

What I'd like to do is, on the results layout, is to show what was searched for. example: Using merge fields, I currently display "found xx of xx records." I'd like it to say something like "searched for _______ , found xx of xx records" (fill in the blank for what the user searched for)

I haven't a clue how to go about this other than maybe to somehow store the search criteria in an unstored field and then display that as a merge field on the layout. The 'find' button is already calling up a script to "perform find", "go to layout: search results", so adding an additional step in there to store the search criteria wouldn't be a big deal, and I assume it would be a calculation, but I lack the knowledge of the syntax to get this done.

Also, on a similar note, I'd like to be able to perform a 'global' search, whereas instead of displaying all the available search fields from a given table on the layout, have one or more fields to build a search string that will search across all the fields in all the tables in the database.

What I have in mind is a series of fields in a row that go something like - a field that the user can enter anything, followed by a drop own list containing "and", "or", another drop down for operators like "excluding", containing, is, is not, greater than, etc, followed by another field, and another "and/or" drop-down, another operator, another field, etc etc, so the user can drill down in a search. Much like a search in the Mac OS, where the user can add additional criteria to get a moe accurate drill-down search. I think this would involve creating a seperate 'search' table and creating fields for each search field and dop down displayed and and then using those fields and drop downs to build a search string into a dedicated calculated field and then searching by the data that's in the resulting calculated field. I can envision how to build the calculation: (Get(SearchField1) Get(AndOrField1) Get(OperatorField1) Get(SearchField2) Get(AndOrField2) Get(OperatorField2) Get(SearchField2)) etc... And then somehow using the results of that field to perform the search, but how to get it to search across all fields in all tables?

Interesting stuff. Again, I lack the syntax to build the calcuations.

Sorry for the long double-question post : Any suggestions?

Thanks in avance, and merry christmas!

[IdealData]

As you are scripting then you can use a $variable.

Use a $variable to capture the entry in find mode, then set a global field (in the results layout) to the $variable.

You will need to PAUSE your script rather than wait for the find to be executed otherwise you won't be able to capture the $variable.

Enter Find Mode

Pause

Set $variable = Entry field

Perform Find

Not sure about the rest of your posting.

[comment]

If users enter their search criteria into a global field (or fields), then these criteria remain available after performing the find and can be displayed, e.g.:

"The search for " & gCriteria & " found " & Get ( FoundCount ) & " records."

Searching across multiple fields is possible by adding requests. You need a new request for every 'or' in your criteria, so the number of requests can climb up pretty quickly: if you want to search for 'a or b or c' in 4 fields, that alone would be 3 * 4 = 12 requests.

The requests must be created by a script, so a calculation field woud be quite useless here.

Searching across multiple tables is problematic, because there is practically no way to display the aggregated results. You could, with some effort, display a portal for each searched table.

Finally, with a few exceptions, the need to search for the same thing in multiple fields and/or tables is a symptom of inadequate data structure: why would I want to search for "Smith" in both LastName and in Address - not to mention in Products::Price?

[iwasnevy]

Good points all.

IdealData - a little clarification plz - Set $variable = Entry field:

I get the set variable part, but when you say 'Entry field", do you mean replace that with the fieldName that they seached in? 'cause there's umpteen fields on the layout and they could search from either one, or do I create a new field to capture the search criteria into?

Comment: Thanks - that sounds like about what I'm looking for, so I'll play with it. I agree with you about a global search being unecessary if the data is structured properly, (and it is) but it's a specific request from the client to be able to do this, and their request is somewhat justified by at least two specific fields - an "activities" field (which denotes the activity in the video that's being cataloged) which gets entered from a value list and a "notes" field where any text can be entered. Let's just say that the consistency of their data entry skills are not always up to par.

Actually, the portal idea was just about exactly what I had in mind already - rather than jumble all the global search results together into one big list, I was thinking of using portals to display the global search results: matches from TableA; matches from TableB etc.

I'll play with these ideas and see what I can come up with. Anyone else have suggestions, I'm open to them, 'cause I have a feeling this'll be a small mountain for me, but it's the LAST thing to do before this databsae is finished and off to the client! woohoo!

:yay: