AnsweredAssumed Answered

4 Tables from 1?

Question asked by jhed on Apr 12, 2010
Latest reply on Apr 19, 2010 by jhed


4 Tables from 1?


I hope I can express this clearly, as this is an issue which has begun to hurt my head.


I am using FileMaker Pro 10.0v3 on a MacBook Pro, Mac OS X 10.4.11


I am creating a database of alumni records, and I need to set it up so that I can import records from our current student database.


In the current student database, student records can be linked to up to 4 different families, stored in the tables FAM1, FAM2, FAM3, FAM4.


It seems as though these tables are populated from one table: Families.


Each Family record contains a FamilyID.  Each student record contains a StudentID, FAM1ID, FAM2ID, FAM3ID, and FAM4ID.


I've managed to set up a neat little import script that will import all family records into my database's Family1 table and link them to the imported student records in the Alumni table.


The script looks like this:


Freeze Window

Set Error Capture [On]

Go to Layout ["Alumni" (Alumni)]

Import Records [No dialog; "CurrentDatabase"; Add; Mac Roman]

Go to Layout ["Family1" (Family1)]

Import Records [No dialog; "CurrentDatabase"; Add; Mac Roman]

Go to Layout ["Alumni" (Alumni)]

Perform Script ["LinkFam1Imports"]


The script triggered by this script looks like this:



     Set Field [join.Families::fk.AlumID; Alumni::AlumniID]

     Set Field [join.Families::fk.FamilyID; Alumni::Family1ID]

     Go to Record/Request/Page [Next; Exit after last]

End Loop


Now, it seems to me that I would want to expand the latter script to include similar steps to place in the join table the IDs for Fam2, 3 & 4.


This is where my internal logic gets loopy (no pun intended).


Ideally, I would be able to grab the records from the existing database, and populate the 4 separate family tables, so that when I'm in the Alumni layout, I can view the related fields in Tab Controls labeled Fam1, Fam2, etc.


However, the imported Family records have no indication of which family they belong to; that information seems to be stored only in the student record.


I've managed to resolve most of my issues with this databse setup so far (with a lot of help from the forum... thanks again, all!), but once again, I'm stymied.