4 Replies Latest reply on Mar 8, 2012 1:43 PM by BruceHerbach

    Test if field is empty for all related records

    morgkl6

      I want to test if a field is empty for all related records. Is this possible? And, if it is, can you please explain how to do it?

       

       

      For example, I have a table with customer information, which is linked to a "purchases" table by a number that is unique to each customer. I want to see if a specific field in the "purchases" table is empty for all of the records for each specific customer. Basically, I want a field on the customer information table that says either "No" (all records are empty for that person and specified field) or "Yes" (all records are not empty for that person and specified field).

       

       

       

      Thanks.

       

      - morgkl6

        • 1. Re: Test if field is empty for all related records
          BruceHerbach

          Hi

           

          One way would be to set up a calc field with a Le statement.

           

          Let(

           

          cur (List( Table::Field2check));

           

          if (ValueCount(cur) >0;"yes";"no"))

           

          This will create a list of items so it checks all fields in the relationship,  and returns a yes for 1 or more values. 

           

          The catch,  as an unstored calculation this may be a bit slow since it has ti check all records in the related table.  You can do it as a stored calc,  but then you have to come up with a way to trigger an update.

           

          HTH

          Bruce

          • 2. Re: Test if field is empty for all related records
            DaveRawcliffe

            Hi morgkl6,

             

            I assume you do have a relationship between Customer & Purchase by CustomerID

             

            Then:

             

            Case (

            not IsEmpty ( Cust_Purch::CustID ) ; "Yes" ; "No" )

             

            HTH

            Dave

            • 3. Re: Test if field is empty for all related records
              morgkl6

              Bruce,

               

              Your suggestion worked wonderfully. If you could let me know if you think of a way to calculate it as a stored calculation, I would really appreciate it. But thanks so much. Works great.

               

               

              - morgkl6

              • 4. Re: Test if field is empty for all related records
                BruceHerbach

                Hi,

                 

                The trick is to add a dependany in the local record and change the

                Value. 

                 

                So if you add a field say upd and the field to the let section of formula.  The have a script replace the value of upd for all records in the found set.  FileMaker will update all of the fields. 

                The last part is to change the Field to an  autoenter calk.  Make sure you uncheck "Do Not evaluate if all referenced fields are empty" on the formula screen and uncheck "Do Not replace existing value if any"  This will ensure that the field updates when you change the valueof upd.

                 

                You could trigger the update script as part of the open layout script so that it refreshes once when the layout is opened.

                 

                HTH

                Bruce