1 2 Previous Next 16 Replies Latest reply on May 23, 2014 1:14 PM by Rob_4

    1st time database user - relationship/field guidance

    Rob_4

      Title

      1st time database user - relationship/field guidance

      Post

           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.

        • 1. Re: 1st time database user - relationship/field guidance
          philmodjunk

               1) Not quite. instead of "up to 27 fields", you need up to 27 related records.

               Start with these relationships and tables:

               Participants-----<Participant_Study>-----Studies

               Participants::__pkParticipantID = Participant_Study::_fkParticipantID
               Studies::__pkStudyID = Participant_Study::_fkStudyID

               You can place a portal to Participant_Study on the Participants layout to list and select  Studies records for each given Participants record. Fields from Studies can be included in the Portal to show additional info about each selected Studies record and the _fkStudyID field can be set up with a value list for selecting Studies records by their ID field.

               This assumes that you need a table of studies where you have one record for each study with fields that document information about that study (such as a name, description, etc.)

          • 2. Re: 1st time database user - relationship/field guidance
            Rob_4

                 Thanks for the reply.

                 I'm sorry if I'm misunderstanding, I really am absolutely new to this. We can't actually have participants linked directly to their study data because almost all of our studies are blind studies. We have a group of people in the lab that have access to data and a group of people that have access to intake info, but only myself and the professor have access to both sets of info (I'm not a researcher in this lab, just the lab coordinator).

                 The process goes like this.... when a participant comes in to participate in a study, research assistant 1 gathers bio and contact info and hands the paperwork off to me or the prof. We assign a StudyID to that person and hand them off to research assistant 2 who does the actual testing. RA1 never gets the StudyID and RA2 never gets the ParticipantID. We want to try to keep that same kind of "blindness" in our database, so we planned to use account privileges to control which groups can access specific tables and layouts, if that is even possible.

                 RA1 would have access to the Intake table (Identifying Info and Medical History layouts), which includes the ParticipantID field (auto serial unique). RA2 would have access to Data tables, which includes the StudyID fields. Professor and I would have access to both of those, plus another table that shows both the ParticipantID and StudyID fields, in addition to other info, like testing date, age at time of testing, population group, etc..

                  

                 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.

            • 3. Re: 1st time database user - relationship/field guidance
              philmodjunk
                   

                        We can't actually have participants linked directly to their study data because almost all of our studies are blind studies.

                   You will still need to link participants to the studies in which they participate. Your researchers that use the database may need to have limited access to the participant data--I'm assuming that they would want to see medical history info but not name or other fields that identify the participant. And it is also possible that the "intake data" that takes their medical history may be different for different studies if the same person participates in more than one study at different times. It may make sense to just start a new participant record should that occur, or you may want to link a Medical history table to both the participant and the Study for which it is applicable. And using access privileges is exactly how you should control access to the participant data.

                   2) with the database link that I described, you can add any needed fields from participants to your Studies layout to show that information. (And this is why you have to link participants to studies, you just don't link the records by name--which would be poor database design anyway.) Establishing the link can be done in multiple ways--all involve one method or another of copying the value of Participants::__pkParticipantID into Participant_Study::_fkParticipantID. You can "enroll" a participant from the participant layout using a portal to Participant_Study at the time of intake or you can do this from another layout based on the participant_Study layout or even the Study layout (using a portal to participant_study.

                   3) If by "data layout" you mean the data recorded about that participant, this would be another table you'd link to Participant study--assuming that you would be logging the same data at periodic intervals so that each record represents one set of data collected on one participant at a specific point in time. (and whether you can use the same tables for multiple studies or will need separate tables for different studies will depend on the type of data and protocols needed for a given study.

                   Here is a thread on getting data from a related table to appear on a given layout that you may find useful: Auto Fill

                   

                        I'm a grad student in a research lab...I have never used a database before...I'm not a researcher in this lab, just the lab coordinator

                   I gather that your expertise is in the subject area of this research lab, not computers. If this is a lab that operates in a university setting, you might want to make friends with a comp sci student--maybe one looking for a special project that they can do for credit to help you set up your database. You may also want to invest some time in learning more about database design in general and FileMaker Pro in particular. We'll be glad to offer advice here, but that additional training can help you "learn the language" and have a better understanding on which to work from when trying to apply advice received in a forum such as this one. There are many training and tutorial resources available--including this free one from Filemaker Inc.: https://itunes.apple.com/us/book/filemaker-training-series/id787527886?mt=11

              • 4. Re: 1st time database user - relationship/field guidance
                Rob_4

                     Thanks again.

                     Is this...

                Participants-----<Participant_Study>-----Studies

                Participants::__pkParticipantID = Participant_Study::_fkParticipantID
                Studies::__pkStudyID = Participant_Study::_fkStudyID

                     the same as this...

                Participants-----<Study>-----Study Data

                Participants::__pkParticipantID = Study::_fkParticipantID
                Study_Data::__pkStudyID = Study::_fkStudyID

                     ?

                     Thanks for the training links. I've been using ones from YouTube and Lynda to get me to where I'm at, but more resources are always helpful. My expertise is in neither this subject matter (Psychology) nor databases. I work as a Coordinator, which is just a fancy name for Office Administrator, for extra weekend spending money. My expertise is in my own field.... Finance :)

                • 5. Re: 1st time database user - relationship/field guidance
                  philmodjunk

                       They are not the same.

                       Study would have one record for each study you plan to support with your database. Study Data, would have multiple records, one record for each time you record data from a single participant. You could easily need multiple tables to support the different requirements of each study. And each record in Study Data will link to only one participant--the source of the data recorded and to only one record in study--the study for which the data is being recorded.

                  • 6. Re: 1st time database user - relationship/field guidance
                    Rob_4

                         Each participant only participates in any given study 1 time.

                         This is a crude representation of what I was thinking.

                    • 7. Re: 1st time database user - relationship/field guidance
                      philmodjunk

                           I don't see how that can work.

                           In Studies, you need one record for each study.

                           In Intake, you need one record for each participant

                           Your data tables then need to link to the participant, not the study as I assume that you will need to gather data from each participant. And each such data set will result in one new record in at least one data table.

                           If you choose to do so, you can create a new Intake record each time a participant participates in a different study if that's a sufficiently unlikely event that you won't see a significant work reduction in not linking more than one study to the same Intake record.

                           Studies----<Intake----<Prospective Memory...

                           Studies::StudyID = Intake::StudyID

                           Intake::participantID = Prospective Memory...::ParticipantID

                           The links to the other 3 study data tables would follow the pattern shown for Prospective Memory...

                           Please note that ParticipantID should be an auto-entered serial number, not only is this good database design for a primary key, it does not reveal any information about that participant beyond serving as a unique identifier number.

                      • 8. Re: 1st time database user - relationship/field guidance
                        Rob_4

                             Thank you so much for sticking with me here. I realize its probably frustrating trying to explain this to someone with no knowledge of the basic concepts.

                             What would I use the 27 individual Study records for?

                        • 9. Re: 1st time database user - relationship/field guidance
                          philmodjunk

                               To link each intake record to the study for which that data has been recorded and to keep data from one study separate from data collected for another study.

                               You may also find that you need to record data documenting that specific study. Whether you need to do that I can't say from here, but you might find that you need to record the name of the researcher in charge of the study, the date the study was performed or....

                          • 10. Re: 1st time database user - relationship/field guidance
                            Rob_4

                                 What if an intake record is related to multiple studies?

                            • 11. Re: 1st time database user - relationship/field guidance
                              philmodjunk
                                   

                                        What if an intake record is related to multiple studies?

                                   I am wondering if you read all of my previous posts.

                                   On May 16, I recommended this:

                                   Participants-----<Participant_Study>-----Studies

                                   Participant_Study serves as a join table linking a single participant (Intake) to multiple studies and yet a study can be linked to multiple participants.

                                   When you then did not adopt that suggestion in the designs you've shown in your responses, I suggested an alternative:

                                   Start a new intake for the same individual each time they participate in a different study.

                                   The first option is what I recommend if doing so saves creating a lot of duplicate intake records. But if you don't expect to see this very often or usually after some time has gone by--which might possibly change some of the intake details recorded, then the second option might also work.

                              • 12. Re: 1st time database user - relationship/field guidance
                                Rob_4

                                     Ahh, ok, I think I've had a light bulb moment. The straight forward, basic language of your last post makes much more sense to me than the technical jargon of the May 16 post. I'll most definitely use your initial suggestion if it means we can have 1 intake record per participant, rather than 1 for each study they participate in.

                                     So, using your initial design, I'll have 1 intake record for each participant, 1 study record for each study, and 1 participant_study record for each time a participant participates in any study (multiple records per participant)? I can use a portal on my Participant layout to see all the studies that participant has been in and also to create a new participant_study record each time they participate in a study?

                                     Thanks again for walking me through this. I, and the members of our research lab, really do appreciate you taking the time to get me on track.

                                • 13. Re: 1st time database user - relationship/field guidance
                                  Rob_4

                                       I'm not sure how this forum works on your end, so I apologize if you get an email every single time I edit my post :)

                                  • 14. Re: 1st time database user - relationship/field guidance
                                    Rob_4

                                         I got it all set up and it is working perfect except 1 thing.

                                         When I create a new Participant record, the corresponding Participant_Study record is created. Is there a way to get a record created in a study's appropriate data table when I type in a new ID number in the portal you had me set up for Participant_Study?

                                    1 2 Previous Next