Get field data from a specific related record

[andypullen]

I have a contact info database based on family records, with the main Families table having a relationship to a second table called Individuals. In my layout, I've added two portals to the Individuals... one for adults, the other for children. There is a script trigger on each portal's "First Name" field that assigns that Individual's record as being either an adult or a child.

What I would like to do is calculate the Family name from the adults' last names. For example, if I enter one person, John Doe in the adult's Individuals portal, and his wife Jane Doe, I want the Family Name field to automatically return as "Doe". However, if John Doe is married to Jane Smith, I want the Family Name to return as Doe/Smith. I've figured out how to calculate so that the first two related records entered populate fields in the Families table called Last Name 1 and Last Name 2 respectively, and the Family Name field calculates correctly from those. The problem is that if a child is entered before both adults, one of the Last Name fields are populated from the child.

I hope my issue makes sense. It feels like I'm close, but I must be over thinking or just unaware of a simple formula to enter. Thoughts?

[comment]

Try it this way:

In the Individuals table, define a calculation field cAdultlastName =

Case ( IsAdult ; LastName )

In the Families table, calculate =

Substitute ( List ( Individuals::cAdultlastName ) ; ¶ ; "/" )