4 Replies Latest reply on Apr 20, 2009 2:29 PM by philmodjunk

    FIND ALL RECORDS WHERE TWO FIELDS ARE EQUAL

    macguy0918

      Title

      FIND ALL RECORDS WHERE TWO FIELDS ARE EQUAL

      Post

      This seems simple, but I can't figure it out.  I have a database table with 40 or so fields.  I need to find all the records where Field20 = Field27.

       

      When I try to set up a Perform Find in a script, it won't let me enter a field name on the right hand side of the equation.  So how do I do this?

       

      thanks

       

        • 1. Re: FIND ALL RECORDS WHERE TWO FIELDS ARE EQUAL
          philmodjunk
            

          In a script:

           

          Set variable [$value, table::field27]

          Enter Find Mode [] (no pause, no restore)

          Set field(table::field20,$value)

          Set Error capture [on]

          Perform Find

          Set Error capture [off]

           

          This will find all records where field20 has the same value as the value in field 27 of the current record.

          The "set error capture" steps, eliminate the "no records match..." standard message that will appear if no records are found.

           

          You can script more details by enhancing this basic example.

           

          Is that what you need?

           

           

          • 2. Re: FIND ALL RECORDS WHERE TWO FIELDS ARE EQUAL
            macguy0918
              

            I am not sure if this will get what I need unless I put this all in a loop so that for each record, it repeats this process for that record only.  I need to check each individual row to see if Field20 in that row equals Field27 in the same row.  If they match, I need that record in the Found Set.  Either or both fields can be different on every other row, so I need to check each one.  Does that make sense?

             

            thanks 

            • 3. Re: FIND ALL RECORDS WHERE TWO FIELDS ARE EQUAL
              comment_1
                

              Filemaker doesn't perform calculations during find. You can either add a calculation field (result is Number) =

               

              Field20 = Field27

               

              and search for 1 in this field, or do a loop as you said.

               

              ---

              BTW, if you have fields named Field20, Field27 etc., you might want to re-examine your structure.

              • 4. Re: FIND ALL RECORDS WHERE TWO FIELDS ARE EQUAL
                philmodjunk
                  

                macguy0918 wrote:

                I need to check each individual row to see if Field20 in that row equals Field27 in the same row.  If they match, I need that record in the Found Set.  Either or both fields can be different on every other row, so I need to check each one.  Does that make sense?

                 


                That makes perfect sense. I wasn't sure which type of find you needed from first post and the coin flip came up wrong.

                 

                Define a calculation field that returns text: If(field20 = field27, "Match", "" ) and call it "match test". Now enter find mode and search for records that contain "match" in the match test field.