11 Replies Latest reply on Mar 8, 2011 9:27 AM by philmodjunk

    Setup Keyword Search

    KAmsinger

      Title

      Setup Keyword Search

      Post

      Here is what I want to setup in my already existing database. I am on FMP 10 Advance. 

      I have a database that has lots of tables which each table includes text fields, portals, value lists, pretty much everything you can imagine. What I want to do is create a new table within this database that a user can enter in a keywords separated by commas and hit find and all records that contains those keywords in any type of text field or portal will pull up either in a new window or in the records parent table and show all the records in a found set and the user can review each record. 

      Here is the thing, which I am not sure if this is an issue. But I already created the database and we have almost 15,000 records just in 1 table alone and have over 10 tables with tons of existing records in each. So I need this feature to work on existing records and text already in each field.

      Any help  with this would be appreciated.

      Thx You!

        • 1. Re: Setup Keyword Search
          philmodjunk

          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?

          • 2. Re: Setup Keyword Search
            KAmsinger

            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 :)

            • 3. Re: Setup Keyword Search
              philmodjunk

              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
              Loop
                 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 ) ]
                 New Record/Request
              End Loop
              Set Error Capture [on]
              Perform Find[]

              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.

              • 4. Re: Setup Keyword Search
                Sorbsbuster

                "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?

                • 5. Re: Setup Keyword Search
                  KAmsinger

                  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

                  • 6. Re: Setup Keyword Search
                    Sorbsbuster

                    Isn't this the same functionality as QuickFind?

                    • 7. Re: Setup Keyword Search
                      philmodjunk

                      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."

                      • 8. Re: Setup Keyword Search
                        philmodjunk

                        Here's a demo file you can play with and study:  http://www.4shared.com/file/lnhsUkkK/GlobalFieldSearchofSelectedTab.html

                        Notes about this demo file:

                        1. 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.
                        2. 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.
                        3. 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.
                        4. 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.
                        • 9. Re: Setup Keyword Search
                          KAmsinger

                          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!

                          Thx

                          Kara

                          • 10. Re: Setup Keyword Search
                            KAmsinger

                            HI PhilModJunk,

                            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

                            Kara

                            • 11. Re: Setup Keyword Search
                              philmodjunk

                              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.