3 Replies Latest reply on May 13, 2015 3:09 PM by philmodjunk

    Search with multiple "And/Or" conditions



      Search with multiple "And/Or" conditions


      I'm from the MS Access world and I'm porting an Access 2010 database over to FileMaker Pro (FM).

      Need help with constructing a script that can search a table occurrence(A) based on selections from 3 different fields within table occurrence(A).  Table occurrence(A) contains information for Shirts and the three dropdown fields are "Type", "Color", and "Size".  In my original Access 2010 database I used VBA to construct a SQL script that ran each time any field select was made and also updated the current layout data.  I'd like to create something similar in FileMake Pro if possible.

      Any suggestions?

        • 1. Re: Search with multiple "And/Or" conditions

          You haven't explained how the "and" and "or" factors apply to a search for type, color and size.

          And "and" find would specify  the type, color and size all in one find request. A search for all records with a specified type, color or size would use 3 find requests, one for each criterion.

          And you can mix and match, putting some criteria on all requests (and) and some criterion on just one request (or).

          There are also options of Constrain Found set (and) and Extend Found set (or) that can be used in place of Perform Find.

          For examples of scripted finds, you might find this thread helpful: Scripted Find Examples

          • 2. Re: Search with multiple "And/Or" conditions

            When selecting either of the 3 dropdown fields for Type, Color, Size in my original VBA script all 3 fields were checked for a selection. The script was run each time a selection was made and the data results from the query (Find) were displayed in the Layout.


            - Select Type:TShirt, no Color selected and no Size selected will return records for TShirt, all colors and all sizes

            - Select Type:Polo, Select Color:Blue and no Size selected will return records for Blue Polo shirts of all sizes

            The script I'm trying to create is for a viewing Layout and needs to be dynamic. I'm aware that multiple finds can be constructed with the Find function in the Status Tool Bar of FM but what I need is to identify the related scripting steps and functions for my scripting objective.

            • 3. Re: Search with multiple "And/Or" conditions

              Please refer to the scripted find examples for which I have already posted a link. These should be exactly what you need for this as they show example scripts that set up and perform finds.

              Your examples, BTW, are all "And" finds and need only a single find request--whether you manually create that find request or use a script to create it.