1 Reply Latest reply on Jun 12, 2012 1:24 PM by philmodjunk

    Going to related record in same table



      Going to related record in same table


      I have a contact list of 4000+ names.  Each record represents a camper who attended a camp, or their parent (s).  The camper and the parents have a uinque ID.  The camper may go on to become a counselor, then may become a parent of a camper themselves.  I need to go from the camper record to the parent record by using a script.  All records need to be in the same table.  Can this be done?



        • 1. Re: Going to related record in same table

          Yes, but it may depend on this requirement:

          All records need to be in the same table.

          All the personal info on each individual should be in the same table, but managing the links between records will likely require using related tables to serve as a "join" between individual contact records.

          When setting up contacts for a childrens ministry, I set  it up this way:


          Family::__pkFamilyID= Family_Member::_fkFamilyID

          Members::__pkMemberID = Family_Member::_fkMemberID

          __pk fields were defined as serial number fields and _fk fields were number fields.

          Name, birthdate, category (parent, camper, counselor) were fields in Members.

          Contact Info was stored in Families and Family_Member served as a "join" table between the two so that one individual could be listed as the member of more than one family. (Think how divorce, remarriage etc can make one individual a member of more than one family.)

          A layout based on Families could be used to list all families. To get lists of all families with all family members listed under a sub head for each family, I'd set up a report based on Family_Member (A member that's linked to more than one family will be listed more than once).

          To get a list of members without any duplication, I'd use a layout based on Members.