Finding records except those matching criteria

You can omit records while performing a find by finding information in your database that does not equal your specified criteria.

FileMaker Pro works through the requests in the order you create them. For example, in a Clients database with clients in the US and France:

  • If the first request finds all clients in Paris and the second request omits all clients in the US, the found set contains all clients in the city of Paris, France, but none in the city of Paris, Texas, or anywhere else in the US.
  • If the order of the requests is reversed (the first request omits all clients in the US and the second request finds all clients in Paris), the found set includes all customers in the city of Paris, France, and the city of Paris, Texas, but no records for clients elsewhere in the US.

To find records that don’t match criteria:

  1. In Find mode, type criteria for the records to omit.

    For example, to find all sales records except those for the city of London, type London in the City field.

  2. Click Omit in the layout bar.
  3. Click Perform Find in the status toolbar.

To find some records while omitting others:

  1. In Find mode, type the criteria for the records to find.

    For example, to find vendors in the state of New York, except those in the city of Albany, start by typing New York in the State field.

  2. Click New Request in the status toolbar.
  3. Type criteria for the records to exclude, and click Omit.

    To exclude Albany, you would type Albany in the City field and click Omit.

  4. Click Perform Find.

Notes 

  • You can have omit criteria in more than one request, including for the same field. For example, in an Item field, finding Red but omitting Barn and omitting Chair (in a total of three requests) will find Red Bird and Red Shoe, but not Red Barn or Red Chair.
  • To omit a related record from a portal row (or set of related records), you must change the relationship or the value in at least one of the match fields so that the record is no longer related. See Working with related tables.
  • You can also omit records from a found set after performing a find. See Omitting records from a found set.