February 5, 2020
, , , , ,

FileMaker Operators for Advanced Finds

Products

  • FileMaker Pro
  •   14.x
  •   13.x
  •   12.x
  •   11.x
  •   10.x
  •   9.x
  •   8.x
  •   7.x
  • FileMaker Pro Advanced
  •   14.x
  •   13.x
  •   12.x
  •   11.x
  •   10.x
  •   9.x
  •   8.x
  •   7.x

 

 

Typically, when you perform a traditional find in FileMaker Pro you type your search criteria directly into a field or fields while in Find mode.  FileMaker Pro supports many different symbols that allow you to perform advanced finds on your FileMaker data.  The list of symbols / find operator supported by FileMaker Pro include:

 Symbol / Operator Description Examples
 =Matches a whole word or matches empty
  • =Market find Market, Market Services and Ongoing Market Research but not Marketing or Supermarket
  • = by itself finds all records that have a blank value in that field
  • =Chris =Smith finds Chris Smith or Smith Chris but not Chris or Christopher Smithson
 = =Matches exactly your entered search criteria
  • ==John finds John but not John Smith
  • ==John Smith finds John Smith but not Smith, John or John Smithers
 !Matches records that have same data within the search field
  • If you only have one person in the database from the state of California and you perform a find using “!” in the state field, the person from California will not show up since there are no duplicate records based on California.
  • If you only have 5 people from the city of Santa Clara and 4 people from the city of San Jose, then all of them will be found because they all have duplicates.
 <Finds all the records that are less than your entered search criteria
  •  < Matt finds every person who comes before Matt alphabetically
  • <40 finds any record whose value is less than 40
  • <4/4/2014 finds any record whose date is less than April 4, 2014
 ≤
<=		Finds all the records that are less or equal to your entered search criteria
  • ≤ Matt finds every person who comes before Matt alphabetically and includes all Matts as well
  • <=95129 finds any record whose value is 95129 or less
  • <=05:00:00 finds any record whose value in a time field is 5:00:00 or less
 >Finds all the records that are greater than your entered search criteria
  • > 9 finds any record whose value is greater than 9
  • > 7/30/2014 finds any record whose date is greater than 7/30/2014
 ≥
>=		Finds all the records that are greater or equal to your entered search criteria
  •  ≥ 9 finds any record whose value is greater than 9 including 9
  • >= 4/13/2014 finds any record whose date is 4/13/2014 or greater
 …Finds all the records that are between two values
  • 12:30…17:30 finds all records where the time value is between 12:30 and 17:30
  • 1/1/2014…6/6/2014 finds all the records where the date value is between 1/1/2014 and 6/6/2014
  • A…M finds all the records where the text value is between A and M
 //Finds all the records that match based on the date in memory
  •  // finds March 21, 2014 when current date is 3/21/2014
 ?Finds all the records that have an invalid date or time entered into the selected field
  • ? finds twelve but not 12 or twelve30
  • ? finds Next Tuesday or 2/33/2014 in a date field
  • ? finds midnight in a time field
  • To find the ? character search for “?”
 @Used as a wildcard character that is part of your search criteria
  •  Gr@y finds Gray and Grey
 #Used as a wildcard digit that is part of your search criteria
  •  # finds 3 but not 30
  • ## finds 30 but not 3 or 300
  • #3 finds 53 and 43 but not 3
 *Used as a wildcard character to find all records based on search criteria plus wildcard
  •  Jo*n finds Jon and John
  • J*r finds Jr. and Junior
  • *phan* finds Phan and Stephanie
  • S* finds Sophie, Steve, and Sven
  • *”son & Phillips” finds Johnson & Phillips and Paulson & Phillips
  • 5/12/* finds the 12th day of May in any year in a date field
  • *:15 finds times 15 minutes after any hour in a time field
 \Used to search for characters that might otherwise be used as operators or other reserved items
  •  \”Joey\” finds “Joey”
  • joey\@abc.net finds the email address joey@abc.net
 “”Used to search for more than one word beginning from the start of the field
  • “Marten and Jones Interiors” finds Marten and Jones Interiors but not James and Marten Interiors”
  • “, Ltd” finds all companies with “, Ltd” in the name, but not those without a comma
  • “Spring” finds Springville” but not ColdSpring Harbor or HotSpring
  •  “@” finds @ (or an email address, for example)
  • “,” finds records containing a comma
  • ”  ” finds three spaces in a row
 ~Used to search for data / characters that makes the same sound in Japanese
  • Image
Share on social media:
Facebook
Twitter
LinkedIn

Related articles