Search the FAQ Archives

3 - A - B - C - D - E - F - G - H - I - J - K - L - M
N - O - P - Q - R - S - T - U - V - W - X - Y - Z - Internet FAQ Archives

[FAQ] FileMaker Pro - database for Macintosh and Windows
Section - 6 Find

( Single Page )
[ Usenet FAQs | Web FAQs | Documents | RFC Index | Airports ]

Top Document: [FAQ] FileMaker Pro - database for Macintosh and Windows
Previous Document: 5 Repeating Fields
Next Document: 7 Scripting
See reader questions & answers on this topic! - Help others by sharing your knowledge
6.1   Some facts
 - If no other option were choosen, a find command on "string" will
 search for all records where an indexed word begins with "string"
 - The last search criterion gets stored. It may be used for a Refind and
 as a search criterion for scripts while using the Restore Find Request
 option of Enter Find Mode or Perform Find. While defining a script, the
 last performed find gets used. Changes can be done by performing a new
 find, open & close the edit script dialog and accept the replacement of
 Find Requests.
 - As indicated above, not more than one restored find is possible per
 script. Additional finds may be used by defining additional scripts and
 calling them from within the first script.
 - The search criterion of a script may be displayed by using the Refind
 command after performing the script.
6.2   Perform scripted find
 Sounds to be obvious, but the find command will be performed by the
 script command 'Perform Find [...]'.
 Example script to find values matching the content of Field_A:
   Copy [Field_A, select entire content]
   Enter Find Mode [No Pause, Don't restore find request]
   Paste [Field_A, select entire content]
   Perform Find [Don't restore find request]
 The omit command itself is not a selectable script option for FMP2.1.
 However, as usual, there's a workaround:
 - Perform any omit find, e.g. enter find mode, paste "a" to field Name,
 click omit, perform find.
 - Define a script "find omit":
   Enter find mode[restore]
   Cut[select entire contents, "Name"]
 That's all. Whenever you call this subscript, the omit box is already
 checked in find mode.
 For FMP3.0 this was simplified. The Omit script step in Find mode does
 the same as marking the omit checkbox. The Omit script in browse mode
 does, as usual, exclude the current record.
6.3   No matching records found
 If no matching records were found, a dialog gets displayed: no records
 match this request:
 - "modify find" will return to the find dialog
 - "cancel" will omit the find and select ALL records
 An automated script without dialog in FMP2.1 showed all records while no
 matching record was found. Thus e.g. a delete on a found set is most
 For FMP3.0 this was modified. Now while you find no matching records,
 you'll get a less critical set of zero found records. Another option
 instead of "modify find" or "cancel" is "continue" with zero found
 records. Furthermore you may detect easily whether the find command was
 successful, e.g. by
  Set Error Capture [On]
  Perform Find [Restore]
  If ["Status(CurrentError) = 0"]
    Perform Script [Sub-scripts, "WhateverYouWantToDo"]
    Show Message ["There are no records found!"] ***
  End If
 You may check the ErrorCode itself or check the number of found records
 as well.
 For FMP2.1 a workaround by dummy records is possible:
 Performing an unsuccessful find in a script will not select zero
 matching records but all - that's really annoying for printouts,
 exports, deleting records etc.
 A workaround is to create a dummy record and to add an additional dummy
 search criterion, e.g.:
   Copy [Field_XYZ, select entire content]
   New Record/Request
   Go to Field [Field_1]
   Paste Literal ["dummy"]
   Enter Find Mode [No Pause, Don't restore find request]
   Paste [Field_1, select entire content]
   New Record/Request
   Go to Field [Field_1]
   Paste Literal ["dummy"]
   Perform Find [Don't restore find request]
 This script will find all matching records and the dummy record, or, if
 no matching records were found, the dummy record only.
 To exclude the dummy record (that is the last of all, and all<999999):
   Go to Record/Request [999999, Perform without dialog]
   Delete Record/Request [Perform without dialog]
 Another solution is to detect if only the dummy record was found:
   Go to Record/Request [999999, Perform without dialog]
   Go to Previous Record/Request [Exit script after last]
 This script jumps to the last record, tries to go back to the previous
 one and exits if the last (dummy) record is the one and only. Thus you
 may use a kind of an
   if(dummy is only record found,
     exit/return to main script,
     perform further part of script)
 Tip: A similar structure may be used as conditional script!
 See sections on conditional scripting and dummy entries.
6.4   Find on multiple fields
 Multiple finds may be performed. Logical AND search may be achieved by
 adding criterions to multiple fields. Logical OR search may be achieved
 by creating new requests. Find details in the next subsection.
 Sometimes a find shall be performed on the occurrence of a criterion IN
 ANY OF several fields.
 A scripted solution is to copy the criterion,
   Enter Find Mode [No pause, Don't restore find request]
   Paste [Field 1]
   New Record/Request
   Paste [Field 2]
   New Record/Request
   Paste [Field 3] ...
   Perform Find [Don't restore find request]
 Another solution is to concatenate the fields to a single search field
 SearchField = Field 1 & ", " & Field 2 & ", " & Field 3
 The "," are just for aesthetic purposes. This find is faster to perform
 but will increase the memory usage of the database and slow down
6.5   Find with AND / OR / NOT
 In order to use search by multiple criteria according to a logical AND,
 OR and NOT, you may use:
 - multiple words in a single field (equals Word1 AND Word2),
 - multiple fields (equals Field1 AND Field2),
 - multiple requests (equals Field1 OR Field2) and
 - the omit command (kind of NOT Field).
 It's not possible to find Field1 AND (NOT Field2). The logical
 equivalent, according to deMorgans law is NOT ((NOT Field1) OR Field2),
 or speaking in FMPro: enter find mode, paste literal to Field1, mark
 omit, create new request, paste literal to Field2, perform find, find
 But the omit works different than a real NOT! It will omit those records
 from a found set that match your omit request. Thus (Field1 OR (omit
 Field2) is different to ((omit Field2) OR Field1)
 OMIT B, FIND A will find all that are not B, plus all that are A
 FIND A, OMIT B will find all A, THEN omit from the found set all that
 are not B
 Example (X is match, (x) is after omitting):
   [data]   OMIT B + FIND A = found |  FIND A + OMIT B = found
    A  B      -        X        X   |    X        -
    A NB     (x)       X        X   |    X                 X
   NA  B      -                     |
   NA NB     (x)                X   |
 While AND and OR are independent on the sequence, OMIT is not the same
 as a logical NOT, but instead dependent on the sequence of requests.
6.6   Find on a subset
 Assuming that the subset was created by a previous find, the manual
 solution is not to select the Find command (cmd-F) but the Refind
 command (cmd-R). You may use AND / OR / NOT and multiple search
 criterions as described above.
 The scripted solution works similar. As a refind you may use the find
 criterions from the last find before creating/editing the script. It is
 accessable by the restore request option. For multiple searches see the
 sections above.
 Sometimes the subset was created manually, or it is not possible to
 perform the desired find by multiple searches in a single step. The
 solution is to use a mark field: (unmark all, perform find,) mark found
 set, perform next find on mark and new criterion.

User Contributions:

Comment about this article, ask questions, or add new information about this topic:

Top Document: [FAQ] FileMaker Pro - database for Macintosh and Windows
Previous Document: 5 Repeating Fields
Next Document: 7 Scripting

Single Page

[ Usenet FAQs | Web FAQs | Documents | RFC Index ]

Send corrections/additions to the FAQ Maintainer: (Martin Trautmann)

Last Update March 27 2014 @ 02:11 PM