Displaying Linked Possible Values
Hi, I'm relatively new to this forum and Filemaker as a whole. Thanks in advance for your sympathy.
I'll explain my setup first. My database manages research data on MRI scans. Patient's have unique medical record numbers (MRN), of which they have only one. Patient's are also given Subject Codes, which is the main method of identifying subjects in our study. When a patient undergoes a scan, they are given a subject code. This particular study involves the patient undergoing more than one scan, so each particular scan is also given a timepoint (i.e. the number of the scan - 1, 2, 3...). For instance, a patient will have one MRN 1111, and will also be referenced in our system by their subject code n001 with this patient's scans being referred to by 1 and 2. So each of these values (MRN, subject code, and timepoint) are 3 different fields. The top table is the "patient" table, which includes basic patient data, which is linked via MRN to the "scan" table where a new record is given for each timepoint (i.e. one record for subject code n001 timepoint 1, one record for subject code n001 timepoint 2).
Here is where my question comes in:
Under the "scan" table is a generic data collection table called "spectroscopy". In spectroscopy I want to include numbers that relate to one scan, so this table is linked to the scan table via subject code and timepoint. I don't have any issues with this, but I'd like to simplify things a bit. Ideally I'd like to enter at the top of the layout for spectroscopy the subject code, then tab to the timepoint field and have the previously entered values display. I can get the timepoint field to display previously entered values for ALL of the scans (i.e. throughout all records in the scan table via a value list), but I want it to show JUST what values have been entered for that particular subject code. The reason is because these timepoints are randomized, so the second scan may have a timepoint of 7 and the first a timepoint of 9. Because it can sometimes get confusing about which timepoints actually exist for a patient (i.e. they aren't always 1 or 2), having only the options to enter values that have already existed for this patient is a sort of quality control check that would make data entry easier. Any ideas on how to do this?
Also, maybe this might make things easier. It would help me out also if a record for a particular subject code and timepoint (i.e. one MRI scan) in the spectroscopy table was created automatically when I created a record in the scan table. I have no idea how to do this right now haha. Doing this automatically woudln't suffice though, as not every MRI scan has spectroscopy portions. I would ideally like a button or a check box that when selected while creating a record in the scan table, a record with the same subject code and timepoint was created in the spectroscopy table. If someone could help me with that I'd be very appreciative. Basically I'd like a button on the scan layout that I could use to say "this scan had spectroscopy, create a corresponding record in the spectroscopy table".
Thanks in advance!