Hierarchy Calculations in 1 table
I am having trouble figuring out the calculations For Spon1 & Spon2 ...
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.