4 Replies Latest reply on Mar 4, 2011 2:53 PM by KarenSpieler

    Hierarchy Calculations in 1 table



      Hierarchy Calculations in 1 table


      I am having trouble figuring out the calculations For Spon1 & Spon2 ...

      Sample Table1:

      Con#    Spon#    nickname    Name_ L                 Con1             Spon1      Spon2                         CommCode*
      100        200          Jim           Jones               100_J.Jones          ???           ???          100_J.Jones / 200_B.Davis / 300_J.Doe
      200        300          Bob          Davis                200_B.Davis         ???           ???          200_B.Davis / 300_J.Doe / 400_x.xxx
      300        400          Jane         Doe                  300_J.Doe            ???           ???          300_J.Doe / 400_x.xxx / 500_y.yyy
      400        500          x             xxx                   400_x.xxx
                                                                                                                           *the calc for this field is currently working just fine
                                                                                                                       in the table when I hand enter the values for Spon1 & 2

      Con1 =  GetField("Con#")&"_"&Left(nickname;1)&"."&GetField("Name_ L")
      Spon1 =  ???
      Spon2 =  ???
      CommCode =  GetField("Con1")&" / "&GetField("Spon1")&" / "&GetField("Spon2")

      I  tried self-joined TOs where the relationship was: Spon#=Con# 
      and the calculation was:   Spon1 = GetField("Spon#")&"_"&Left(TO2::nickname;1)&"."&GetField("TO2::Name_ L")

      ...but that didn't work ... I kept getting Spon1:  200_J.Jones   instead of:  200_B.Davis

      Can anyone see what I am doing wrong?

      Also - is there a book or website that is really good at explaining all the different parts-is-parts that go into creating these calculations? One that someone like me (with little or no programming knowledge) can understand? The Filemaker "Help" has gotten me this far.

        • 1. Re: Hierarchy Calculations in 1 table

          Con1 can be simplifed to read:

          Con#&"_"&Left(nickname;1)&"."& Name_ L

          If your self join is set up to read like this:
          TO::Spon# = TO2::Con#

          Then this expression should return the value for Spon1:

          Spon# & "_" & Left(TO2::nickname;1) &"." & TO2::Name_ L

          However, your original expression should also have worked, I'm just simplifying your expressions to eliminate the uneeded Get Field functions.

          Thus, something is not right with the self join relationship.

          Make sure that Con# and Spon# are both the same data type. (I recommend number.)

          You might want to add a portal to TO2 temporarily to your layout to check and see if the relationship is matching up to the records you expect here. You'll need yet another self join chained to TO2 to get Spon2, but that's a waste of time until you get Spon1 working for you.

          Oh yes, and this calculation should be defined in a field of type calculation, not a text field with an auto-entered calculation as that won't update correctly when you edit a referenced field from a different record.

          • 2. Re: Hierarchy Calculations in 1 table


            I checked all my field types and data types, got everything set like you said. I have Spon1 working now (the data type was mixed in the base #fields), so I duplicated everything I did to set up for Spon2, but It's pulling the same as Spon1!

            I added new TO ... "TO3"
            Duplicated the calc for Spon1, then moved everything a step back...

            TO2::Spon# & "_" & Left(TO3::nickname;1) &"." & TO3::Name_ L

            I am guessing that wasn't the way to go, but I don't know why?

            • 3. Re: Hierarchy Calculations in 1 table

              Given the way these chain together, what you need is this:


              TO::Spon# = TO2::Con#
              TO2::Spon# = TO3::Con#

              Then your expression for Spon2:

              TO2::Spon# & "_" & Left(TO3::nickname;1) &"." & TO3::Name_ L

              Should work.

              • 4. Re: Hierarchy Calculations in 1 table

                That's what I had, no go

                Oooo, I figured it out! ... I had the "current table" selected as TO2 for Spon1 and TO3 for Spon2 for some reason.

                I changed them TO and everything's hunky-dory now