9 Replies Latest reply on Nov 3, 2009 9:52 AM by philmodjunk

    Complex Search



      Complex Search




      I'm creating a sales prospecting database to help narrow down possible clients on file for location specific events.


      I need to be able to perform a search based on a particular location and for it return all the clients that would be appropriate for that location.  There is a preference for local or national interest on the clients profiles in the database.


      Does anyone have an idea? 


      Background - I'm using Filemaker Pro Avanced 10 on a Mac, the database will be shared across a network with around 10 users.  I have an intermediate level of knowledge with filemaker. 

        • 1. Re: Complex Search

          Can you give a more detailed example? This sounds like a very simple find request, but I suspect there are complications that are making it not so simple.


          When requesting assistance on this forum...

          • 2. Re: Complex Search

            OK.  The company runs exhibitions all around the country and has clients who exhibit at these exhibitions located all around the country.  What I'm trying to do is, for any given exhibit taking it's location and finding all the companies in the area, who have a local interest, who would be interested in exhibiting and also the companies located elsewhere in the country that have a national interest.


            I hope that makes sense! 

            • 3. Re: Complex Search

              The devil is in the details and many of them are still lacking from your description of the issue.


              How do you record a "local" or "national" "interest" for a given customer record in your database?


              Are there multiple "interests" recorded for a given customer? If so, how did you structure your database to manage such multiple entries?


              Examples of what this data looks like would be extremely helpful.

              • 4. Re: Complex Search

                I haven't yet decided how to store their interest as I didn't know how I am going to need to search.


                Each company has basic details, Company name, address etc.  The exhibitions have a location detail being a city.


                I want to be able to run a script for any given exhibition and to be returned a list of clients that might be interested in that exhibit based on their geographic interest and location in regards to the exhibit.


                Any ideas would be helpful... 

                • 5. Re: Complex Search

                  I strongly suggest working out how you would gather and store this information first--that will in all probability be much harder than designing a method to search the data. Once you can describe how the data is stored in the data base, the search and report functions will probably fall into place.


                  One method for documenting potential "interest":

                  Define a list of keywords that match to exhibition categories or topics.


                  For each customer, define a related "interest" table where one record records one topic of interest.

                  For each exhibit, define a related table of interests using the same list of terms.



                  Customer:  John Smith

                    Space Travel

                    Science Fiction



                  Exhibit:  History of NASA

                    Space Travel





                  Once you have the data in the tables, performing a find to match customers with a given list of exhibit "interest" categories becomes fairly straight forward.

                  • 6. Re: Complex Search

                    I have created a field in each table which gives a checkbox selection for each of the interest categories.  Both the sets of checkboxes come from the same list of values.


                    Obviously the checked interests are then stored as plain text, how would i go about splitting up the text in each field, customer and exhibit, to compare them in a search?


                    Any help appreciated! 

                    • 7. Re: Complex Search

                      Hmmm, that's not the exact method I suggested, but it should work OK for you. ( I pictured a portal of "interest" records where you selected a different value from a value list on each row of the scrolling portal. )


                      Using your method, establish a relationship that uses the two check box fields to link your exhibit and customer tables:


                      Exhibit::InterestsCheckBoxes = Customer::InterestsCheckBoxes


                      (Existing relationships may require you to create new table occurrences for one or both tables and use them in the above relationship.)


                      Multiple check box selections in the same field are stored as text separated by carriage returns. When you use this field in a relationship, filemaker matches the records using "OR" logic.


                      Example:  Referring back to my two examples in my previous post, the NASA exhibit record would match to any customer whose interest check box field has Space Travel OR Rockets OR Astronomy OR  Astronauts selected.


                      In a script, you can thus pull up all customers with at least one matching interest selection using the Go To Related Record (GTRR) step. If you want to then restrict the matching records to a specific geographical area or list of geographical areas that you've entered in other fields, you can use Constrain Found Set to pare down the list of matching customer records.


                      Here's a possible script that would be launched from a layout based on your exhibits table:

                      We'll assume a field in Exhibit, Location records a geographical area we want to use to restrict our customer list.

                      Set Variable [$Loc; Exhibit::Location]

                      If [Count (Customer::CustomerID) > 0 /* There are related records that match */]

                        Go To Related Records [Show Only Related Records; From table: "Customer"; Using Layout: "Customer" (Customer)]

                        Enter Find Mode[]

                        Set Error Capture [on]  // keep filemaker's records not found dialog from interrupting the script

                        Set Field [Customer::Location; $Loc]

                        Set Error Capture [off]

                        Constrain Found Set []

                        If [Get (foundcount ) > 0 /* Some records were found with this location */]

                           Sort [No dialog; Restore]

                           #Do whatever else you need to display/process your group of customers with matching interest/location data


                           Go To Layout [Original Layout]

                           Show Custom Dialog ["No customer records with this location were found."]

                        End If


                        Show Custom Dialog ["No customer records matching this list of interests exists."]

                      End If


                      GTRR Note: This is a powerful but woefully undocumented (by Filemaker Inc.) tool with many quirks and a few dangerous pit falls. If you're interested in learning more, see the following link:


                      The Complete Go To Related Record


                      • 8. Re: Complex Search

                        Thanks for your help it's done just the trick!


                        One last query -


                        Is there any way to be able to search using the location field of the exhibition and find customers within a certain distance of that exhibition?  I have been looking and I can't find anything on it except Postcode PA.


                        Thanks Again! 

                        • 9. Re: Complex Search

                          I'm not familiar with any method you could use here. I'd assume a method does exist, but haven't seen one you could "plug in" to your database.


                          You might want to open a new thread and post this question. Another forum participant may know of a method.