3 Replies Latest reply on Dec 18, 2013 12:12 AM by UrsSchuermann

    find empty records in a related table

    UrsSchuermann

      In a relation (related thru a like this. a::field_1 = b::field_2) two table are connecd. Table "a" has more recored then table "b". Therefore in table "b" appear some records as empty, because no relation can be established (like in a full outer join on any SQL DB). When I perform an interactive find and put an * in b::field_2 then I receive all related records (inner join). Then I select omit for the current found set and I recieve all records which have no relation, since they do not exist in the other table.

       

      This is what I would like to achive through a script. Unfortunatelly may attemp to script this behavior repeatently fails.

       

      Enter Find Mode[]

      Set Field [b::field_2;"*"]

      Perform Find []

      Show Omitted Only

       

      I receive always all recored instead the subset which I receive when I do it interactive as described before.

       

      Does anybody has an idea what I do wrong? Thanks for your help.

        • 1. Re: find empty records in a related table
          jmrusch

          Try this:

           

          Enter Find Mode[]

          Set Field [b::field_2; "*"]

          Omit Record

          Perform Find[]

          • 2. Re: find empty records in a related table
            erolst

            UrsSchuermann wrote:

             

            In a relation (related thru a like this.  a::field_1 = b::field_2) two table are connecd. Table "a" has more recored then table "b". Therefore in table "b" appear some records as empty

             

            Not that this is an inherent consequence, because in a 1-to1-relationship, if A has more records, some of them cannot have a counterpart in B, while each B could be represented in A …

             

            Anyways … if you want to find records in B without a related record in A, you need to be in the B context, and set A::matchfield (or any non-empty related field from A) to "*", not B::matchfield.

             

            Go to layout [ B ]

            Enter Find Mode [ ]

            Set Field [ A::field_2 ; "*" ]

            Perform Find [ ]

            Show Omitted Only

            • 3. Re: find empty records in a related table
              UrsSchuermann

              Thanks guys

               

              I found the "bug". It was my fault. I did a mistake in the programe flow and fired an "show all recoreds" at the wrong position... It works now indeed. Her the explanation may is of some interest:

               

              What I tried to achieve was  something you may would formulate with  the 'SET THEORIE' as 

               

              B \ A

               

              The equivalent in SQL looks like this 

               

              LEFT OUTER JOIN b ON a.key = b.key WHERE b..key IS null

               

              FILEMAKER:

               

              Since I did not come up with a more elegant solution I had to script it this

               

                 

              Enter Find Mode[]

              Set Field [b::field_2;"*"]

              Perform Find []

              Show Omitted Only

               

              whereas a table occurance is used with a relation   1:n between Table B and Table A and  Key relation b.key = a.key and of course a layout that is based on this table occurance.