AnsweredAssumed Answered

Scripted Find Examples

Question asked by philmodjunk on Dec 20, 2012
Latest reply on Jun 9, 2017 by BruceRobertson


Scripted Find Examples


     Folks frequently ask how to perform finds in scripts where data specified by the user is used to create the needed find criteria. Such scripted finds can be much more user friendly than a manual find. This post presents the basic script I recommend for this purpose. At the end of this post I have also listed multiple examples of how the set field step can be used to create various find criteria.

     This script assumes that global fields have been placed on a layout where a user entered or selected data to be used as find criteria. This layout can be displayed via the New Window script step to be a small modal dialog where the user enters or selects criteria.

     The script will fail if you do not use fields with global storage specified in Field Options. Since global fields can be accessed from any layout and script in your file, such fields are often defined in a Globals table to better keep track of global fields used in the file.

     Go To Layout [Select a layout based on table being searched]
     Enter Find Mode [] ---> clear the pause check box
     Set Field [YourTable::YourField ; Globals::gField]
     Set Error Capture [on] ----> Keeps error dialog from interrupting script when no records are found
     Perform Find []
     If [Not Get ( FoundCount ) // no records were found ]
         Show Custom Dialog ["No records were found by this search."]
     End IF

     Other Set field steps you can use:

     Records with a date within a range of dates:
     Set Field [YourTable::YourDateField ; Globals::gDate1 & "..." & Globals::gDate2]

     Records with a certain date or older:
     Set Field [YourTable::YourDateField ; "<" & Globals::gDate ]

     Today's Date:
     Set FIeld [YourTable::YourDateField ; Get ( CurrentDate ) ]

     5 days from today:
     Set Field [YourTable::YourDateField ; Get ( CurrentDate ) + 5 ]

     Where a field is empty (Field should not be from a related table):
     Set Field [YourTable::YourField ; "="]

     Where a field is NOT empty:
     Set Field [YourTable::YourField ; "*"]

     Field equals exact text specified:
     Set Field [YourTable::YourField ; "==" & Globals::gField ]

     Word in field exactly matches specified text
     Set FIeld [YourTable::YourField ; "=" & Globals::gField ]

     Word in field ends with text specified:
     Set FIeld [YourTable::yourField ; Globals::gField & "*" ]

     Word in field starts with text specified:
     Set Field [YourTable::YourField ; Globals::gField ] ---> no wild card needed this is the default behavior for find criteria

     Records that do NOT have text specified:
     Set FIeld [YourTable::YourField ; Globals::gField]
     Omit Record

     Omit Record when in Find Mode is the same as clicking the Omit record button in a manual find. In multi-request finds, omit request should be created last as they modify the found set produced by the other requests.

     That barely scratches the surface, but should give you some ideas on some of the myriad ways you can specify find criteria in a script.

     Note for newbies creating a set field step for the first time:
     When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

NOTE: This thread is now more than 3 months old. New comments posted to it no longer pop it up in Recent Items and thus are unlikely to be seen by others. If you were referred here from another thread, post any questions/comments there. Or you can start a new thread and include a link to this thread in your posted comment.