1 Reply Latest reply on Sep 22, 2016 11:50 AM by TSGal

    Fields in non-existing related records get calculated

    NBusch

      Product and version FileMaker Pro 11 to 15

      OS and version All

      Description A calculation in a related record "case(  isempty (fieldname) ; 1 ;'') is treated as having the value of "1" even if no record exists

      How to replicate

      table Parent, fields "parentID", "Nationality[Text]", "IsForeigner[calculation: case(Nationality <> "USA"; 1; 0)]"

      table Child, fields "chieldID", "fk_parentID", "parentIsForeigner[ Calculation: child.parent::IsForeigner]"

      relation child.parent, child::fk_parentID = parent::parentID

       

      Every child record that has no parentID will show the field "parentIsForeigner" as 1 instead of empty

       

      Workaround (if any) There are many other, probably better ways to write the formula for "IsForeigner". Still the result seems to hint at some basic flaw in the way FileMaker looks at non-existing records in a relation

        • 1. Re: Fields in non-existing related records get calculated
          TSGal

          NBusch:

           

          Thank you for your post.

           

          If a record doesn't exist, then any related fields will be empty.  Therefore, first evaluate if any records exist in the portal.  For example:   Count ( <Related Field> ) > 0

           

          Or, in your case, since the relationship is based on parent::parentID:

              If ( IsEmpty ( parent::parentID ) or parent::Nationality = "USA" ; 0 ; 1 )

           

          If you want an empty value if parent::parentID is empty, then use:

             Case ( IsEmpty ( parent::parentID ) ; "" ; parent::Nationality = "USA" ; 0 ; 1 )

           

          TSGal

          FileMaker, Inc.