Are you wanting to search all 10 tables from one search or will the user select a table and then the script uses the criteria to search the specified table?
well, I guess the user could select a table and then enter in their keyword search. Each table is for a different purpose so that would be okay, if that makes the setup easier for me :)
It makes things much, much easier.
Next question: Why the extra table for entering key words? Do you want to save this criteria for later use?
If not, just define a global field for your list of keywords. Put a second global field on this layout for the user to use to select the desired table. You can format this field with a drop down of table or layout names.
Your script then follows this basic outline:
Use the global field of table names to select the correct layout (you'll need a different layout for each table on which the user might search.) Use either a series of If -Else If statments to select the correct layout or you can use Go To Layout with the "layout name by calculation" option if the global field's value list lists the actual names of the layouts.
The rest of your script then does this:
Set Variable [$Keywords ; Substitute ( YourTable::gKeywords ; "," ; ¶ ) ]
Enter Find Mode//clear the pause check box
Set Variable [ $I ; $I + 1 ]
SetFieldByName [//Expression that computes Name of Table::Field from user choice in global layout field ; Getvalue ( $Keywords ; $I )]
Exit Loop If [$I > ValueCount ( $Keywords ) ]
Set Error Capture [on]
This will find all records on the selected table if the record contains keyword 1, or keyword 2 or....
If you want the keyword search applied to multiple fields in the same record, define a calculation field in each table defined to combine all the searchable fields into a single concatenated block of text: List ( Field1 ; Field2 ; Field 3; ...) or Field 1 & " " & Field 2 & .... and do your search on this field.
Edit note: left out script step in Red originally.
"all records that contains those keywords in any type of text field" - do you mean that a found record must contain any, or contain all, of those key words?
Thx for this PhilModJunk. However, I think this is way over my experience level as I am not quite sure how to set this up. Is there an example file somewhere on FM site that has something similar set up that I could use as a template to build mine?
I guess I am confused as to how this would work. Would the user go into find mode and then type their keywords into the global field for the keywords and then hit perform find or hit a button I create with the script above attached to it? And how would that field, that I type my keywords into, know to search all the fields in that table for those keywords?
No need for an extra table or save for later feature (was just my first thought on how to begin), as I am so new to this that I need to make this as simple as possible for my self. This was just sprung on me to add to my list of other features I am trying to setup in this database that I've been working on for over a year!!! Uggghhhh
really appreciate your help with this.
Sorbsbuster: to answer your question - the found record must contain ANY of the keywords, NOT all of the keywords. Thx
Isn't this the same functionality as QuickFind?
This project isn't something a "newbie" can take on easily as it requires a pretty sophisticated approach.
Would the user go into find mode and then type their keywords into the global field for the keywords and then hit perform find or hit a button I create with the script above attached to it?
I left out a line now added in red to the above script: Enter Find Mode. The user specifies the criteria in browse mode. The script enters find mode and set's up the find requests needed to find the records from the table specified by the user.
And how would that field, that I type my keywords into, know to search all the fields in that table for those keywords?
With this script, you seach just one field, but that one field is a calculation field that combines the data from all fields within the record where the keyword might be found. That's the purpose to this statment from my last post:
"If you want the keyword search applied to multiple fields in the same record, define a calculation field in each table defined to combine [the data from] all the searchable fields into a single concatenated block of text: List ( Field1 ; Field2 ; Field 3; ...) or Field 1 & " " & Field 2 & .... and do your search on this field."
Here's a demo file you can play with and study: http://www.4shared.com/file/lnhsUkkK/GlobalFieldSearchofSelectedTab.html
Notes about this demo file:
- I used identical names for the tables, their search result layouts and the names in the drop down list. This is not strictly necessary, but it greatly simplifies the script and the calculation used in the first parameter of the SetFieldByName step.
- I gave the search field in both tables exactly the same field name. Again, this is not strictly necessary, but makes for a simpler SetFieldByName expression in the first parameter.
- I used two completely different calculations to combine the values of the individual fields in these two calculation fields just to show that either expression works for the purposes of this search method.
- The global fields can be defined in any table you want. I've used a separate global field table simply because I find this is a good practice for managing all global fields that aren't used in a relationship.
Thank You so much!!! This is perfect, now maybe I will learn some more in's and out's of the software for even future setups!!!!
Really appreciate this!
So now my boss wants me to add a new feature to this keyword search setup. Here is what she wants.
If she is in a table called "Person" (which is the only table I have setup in the Table List for the keyword search) and she hits the find button and enters in criteria to find, for example we have groups setup and one is called "NYW NJ Early 30s" and performs the find. But then wants to narrow down those results by using the keyword search that we setup and so in the keyword search she enters in "Brunette". Is there a way to setup a feature on the keyword search to select something so it knows to "Modify Last Find" for that table "Person" and you can enter into the keyword search field the criteria she wants to narrow down her search by and then hit find and it knows to search "NYW NJ Early 30s" & narrow it down by "Brunette" only?
The other question I have is in the keyword search that we already setup am I suppose to enter in keywords separated by a comma or by a plus sign "+"? Because there are times that a + gives better results and times when a comma gives better results.
Thx for your help
For the first question, you can use Constrain Found Set in place of Perform Find. This will apply the search criteria only to the records in your current found set. I'd create a second button and script for this option so that your user can do a key word search either separately or in combination with other searches.
With regards to + vs , it's important to understand how the script and Filemaker searches of a table work. The script uses commas as the separator between keywords. If you use + the words aren't separated into separate values and thus aren't entered into separate find requests.
If you enter Apple, Orange and run the script, FileMaker creates one find request with the keyword "Orange" entered and one request with the keyword Apple entered. Any records that have "Orange" OR "Apple" in the search field will be found.
If you enter Apple+Orange, the script will enter Apple+Orange into a single find request and this will find all records with "Orange" AND "Apple" in the search field. Instead of entering Apple+Orange, just enter Apple Orange if you want to find all records with Apple AND Orange instead of Apple OR Orange.