1st time database user - relationship/field guidance
I'm a grad student in a research lab tasked with setting up an FMP12 database to convert paper records to electronic. I have never used a database before and need help with what I assume is a basic question.
I set up an Intake table, with an Identifying Info layout and a Medical History layout. One of the fields is ParticipantID (auto serial unique). It was easy enough to show that ParticipantID field on both layouts and insert a button to switch between layouts. The ParticipantID is the only common field between the layouts. As of now, this table contains 457 records.
Now I'm trying to set up a 2nd table called Studies. This Studies layout will have a list of our 27 studies and if a person was in the study, an undergrad will check a box indicating the participant has completed it, and also fill in a text box with a unique StudyID. So, this layout will have ParticipantID from the 1st table, connected to up to 27 StudyID (Study1ID, Study2ID, Study3ID...), but most commonly only 3-4 StudyID. These StudyID numbers won't be auto serial, but will be unique.
After that is all set up, I plan to set up Data tables for each of the studies' data, using Study1ID, Study2ID, Study3ID... as the link, but they won't show ParticipantID.
If I understand correctly, my Intake table should have ParticipantID, my Studies table should have ParticipantID and StudyIDs, and my Data tables should have StudyID.
1) Am I headed in the right direction?
2) How do I get ParticipantID to automatically populate on the Studies layout when a new record is created from the Identifying Info layout (this is where it all starts). I created a field in the Studies table named ParticipantID and dragged ParticipantID from Intake to Studies in the relationship manager, but when I create a new record on the Identifying Info layout, nothing happens in the Participant ID box on my Studies layout.
3) The same thing should occur when a StudyID is entered in the Studies layout. We would like it to automatically populate in the corresponding Data layout.