2 Replies Latest reply on Aug 29, 2011 9:18 AM by DonaldBennett

    Wierd Calculation Problem



      Wierd Calculation Problem


      I'm working on a family database with tables for families, persons, and a join. On the person table I have a checklist with the data values being Head, Spouse, and Child. On the family table I have the following fields:

      Head     Calculation   =If ( Person::Family Position = "Head" ; Person::First Name )
      Spouse   Calculation   =If ( Person::Family Position = "Spouse" ; Person::First Name )

      The calculations work but in a strange way. The calculation only works on the first person entered. If the head gets entered first, their name shows up but the spouse's name is left empty. Vice-versa with the spousegetting entered first.

      Any clues as to how to fix it or attack it differently?


        • 1. Re: Wierd Calculation Problem

          Person::Family Position only refers to the first related record and this is why your calculations won't work as written.

          Why not put a text field in the join table and use it to designate specific members of the family as "head" and "spouse"? That way, a person can be the head of one family and a child in another, but perhaps you don't need that type of flexibility here.

          What you need is either a filtered portal (FileMaker 11) or a filtered relationship (older versions) to display the "Head", "Spouse" names.

          With filtered portals, you add one row portals to your layout and use filter expression such as:

          Person::Family Position = "Head"

          and then put Person::First Name in the portal's row. (The portal's boundaries can be made invisible so that this looks like any other field on your layout.)

          If you are not using FileMaker 11, let me know and I'll describe a way to use a relationship that only links to the "Head" of the family.

          • 2. Re: Wierd Calculation Problem

            Bingo! That'll work well.