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

    Fields in non-existing related records get calculated


      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



          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 )



          FileMaker, Inc.