1 Reply Latest reply on Sep 2, 2011 8:47 AM by philmodjunk

    Help Creating Search Filter



      Help Creating Search Filter


      I am a novice at layouts and filemaker language, So this is what I am trying to do and I thank anyone who helps me figure out how to get this completed.


      I am trying to create a database search filter that will be able to return any and all records that fit the filter but can have an unlimited number of search criteria that are based on "and"/ "or" strings. I'm not sure if using a portal can solve this but this is what I'm talking about.


      example, search:


      first name="" , and, age<"" , and, height<>"",  +


      last name="" , and, team="" , and, record>"", +



      The plus signs are buttons I would like to have to automatically add to the search criteria to tag as an "and" string or an "or" string. So when you go to click one it adds a box to enter the "field" from the database then the command "<=>,<>" then the "value/comparing field" 


      I'm not sure if I'm asking/trying to do something complicated here or this is a walk in the park, but any help will be greatly appreciated. Thank you.

        • 1. Re: Help Creating Search Filter

          It's not simple, but it can be done. A clickable portal or these search clauses could build a list of and, Or options in a global Field or variable, then a script can parse the list of selected items into a series of find requests in order to produce the desired found set.

          What's not clear, is how you intend to add criteria inside the quotation marks here. (First name = "Fred", Age < 30, or....)

          This will be a very challenging thing for someone new to FileMaker and scripting to accomplish. We'll explain as best we can as we go, but if you have access to training materials and/or a book on filemaker, please keep reading/training on those so what we do makes more and more sense to you.

          First, you need to understand how FileMaker finds work: (Apologies if you know this already, but we have to start somewheres...)

          To find all records where the first name = "Fred" and the age field stores a value smaller than 30, you enter find mode and enter "Fred" in the first name field and < 30 in the age field, then perform the find. How you do this manually is very similar to how you do it in a script. You can get subtly different results for your name search depending on whether you  use " =Fred", "Fred" or "==Fred". A single = sign means that you will find all records where there is at least one word in this field. It will find both "Fred" and "Fred J". Entering "Fred" with no operators will find all records wehre there is a word that starts with "Fred". It'll find "Freddy" as well as "Fred".  == on the other hand is the exact match operator that will find only records that have exactly "Fred" in them. The operators are something you can look up in FileMaker help to learn more.

          To find all records where the first name is Fred OR the age is less than 30, you also enter find mode, but make one small change. Enter Fred in the first name field, select New Request from the Requests menu and then enter < 30 in the age field. Now you'll have an OR based find.

          As I've already stated, scripts work in very similar fashion and a script can loop through such a list of clauses, creating new requests as needed and then performs the find.

          What will really complicate this script will be combining OR and AND clauses in the same list of criteria.

          Given this complexity, you might describe what your end goal is here just in case we can think of a simpler alternative to what can be a fairly complex script.