1 Reply Latest reply on Jul 19, 2010 10:55 AM by philmodjunk

    Conditional matching between tables, 2 or three variables

    dsmith99

      Title

      Conditional matching between tables, 2 or three variables

      Post

      I am Working on database with Light Bulbs

      I have a database of commercial light bulbs. There are about 20 fields of information per bulb.

      One is: if it is Energy Star qualified.. “yes”, “no”, & “n/a”.

      Another field is useful life in years… example could be “10” years.

      If I am trying to match light bulbs to a specific need, that has been manually input from a job order on another table, and that need might be that a bulb can either have a useful life of over 8 years or be Energy Star Qualified. 

      Example bulbs in database:

      Bulb A: useful life 12 years & Energy Star Qualified (this bulb would get a Yes / successful match)

      Bulb B: useful life 5 years & not Energy Star Qualified (this bulb would get a No)

      Bulb C: useful life 11 years & Not Energy Star Qualified (this bulb gets a Yes)

      Bulb D: useful life 4 years & Energy Star Qualified (this bulb gets a Yes) 

      You can see the problem, if I match from field to field (twice: using both fields), only Bulb A would show up. But the correct answer is A, C, & D. 

      How do I have 2 fields: Energy Star Qualified & Useful life, and then in another table/portal (for job requirements), have Bulbs A, C, & D show up as meeting requirements?

      And of course I could, will have three, or more, fields to contend with later.

      Help! I am sure the answer is easy... if you know it.

        • 1. Re: Conditional matching between tables, 2 or three variables
          philmodjunk

          The easiest approach is to construct a script that performs a find on the light bulbs table.

          Set Variable [$StarQ ; value:  JobOrder::Energy Star Qualified ]
          Set Variable [$Life ; value: JobOrder::Life ]
          Go To Layout [Light Bulbs]
          Enter Find Mode[]
          Set Field [ LightBulbs::StarQ ; $StarQ ]
          new Record/Request
          Set FIeld [ LightBulbs::Life ; "> " & $Life ]
          Set Error capture [on]
          Perform Find[]

          The above example is simpler than you'll need to process multiple criteria, but should illustrate the concept. When you enter criteria on separate requests--such as the second request created by the new record/request step--filemaker finds all records matching the criteria on the first request OR matching the criteria on the second request.