Modify the relationship (or create a new one) so that you relate the two tables based upon the Donor ID.
If you were then looking at S_ID 62 (PN164 as the Donor ID), you'd see E_ID 139, 140, 141, 142, 479.
You'd see the same for S_ID 63, and for S_ID 274.
A different relationship would be S_ID in Specimens to S_ID in Experiments. For S_ID 62, you'd see E_ID 139 and 140, for instance.
Both of those relationships might be important to you.
View all experimenets for a given donor.
View all experiements for a given donor's specimen.
Thanks for the feedback.
I don't think I can relate the two tables based on Donor ID, because the one donor can give multiple samples. In the original image for example, donor PN164 has provided three samples: S_ID numbers 62, 63 and 274, the first two of which have been split into two replicates. In my Experiment table, I need to differentiate between all these, and if I had just linked these two tables by Donor ID, then the Experiment table wouldn't know which instance/record of PN164 was which. Unless I'm missing something?
A simplified structure of my full database is as follows. As I need to de-identify patient/donor information, that Donor table is protected by privilege sets, and most users will only see the Sample and Experiment tables, which will contain experimentally pertinent data, but nothing that can identify the donor.
The donor table doesn't need an S_ID or E_ID. It just needs the primary key, PN164 in your example. The donor can give multiple samples. Each sample gets a primary key, and each sample gets the foreign key for that donor. At least one experiment is done on each sample, so each experiment gets a primary key, and each one is stamped with the foreign key of the sample. You can add the foreign key of the donor if you'd like. With that arrangement, you can see all of the samples for a donor, and if you select a given sample, you can see all of the experiments for that sample.
kp_donor from the donor table relates to kf_donor from the sample table and kp_sample from the sample table related to kf_sample from the experiment table.
Sent from my iPhone
Thanks again for your feedback. I haven't come across primary keys as yet in my FileMaker Pro education, so I guess I'll do a little Googling to see if I can work through it.
Most everyone on the forum really tries to help. It's an incredible resource, IMO.
When you create a table, you should ALWAYS create a "Primary Key" for the table. Some people name their "keys" with pk_ for primary, fk_ for foreign. Some use kp_ and kf_. Some use xP_ and xF_. Case doesn't matter. Consistency does, so pick a style and stick with it. I personally like xP_ and xF_ because alphabetically, the keys will be grouped together, and nobody much uses "x" to start their variable names so they'll be found easily. I also like kP_ and kF_ because the "k" designates "keys". Again...consistency matters most.
So in your Donor Table, you'll have a PK. DON'T use Social Security Number or something like that. Use an auto-entered value: the FileMaker generated UUID or Serial Number. Why? Because sometimes somebody makes an error entry when typing in a Social Security Number. If you've used it as a key and you change it...you break the relationship to the records that are associated with it.
In your Donor table, it looks like the PK for your donor is PN164. I don't know where it came from, but I can tell that it appears to be unique to whoever PN164 is. Whenever you're going to add something to another table..."Sample", for instance, you'll want to make sure you can relate the sample back to the donor. So...if you're working with a donor record, and are about to enter a sample for that donor, have your script "grab" the donor ID and store it in a variable, maybe $DonorID. Then go create your Sample record, and when you do, set the FOREIGN key for the record. Maybe more than one foreign key (like location, or whatever). Your new Sample record gets its own PK, Sample001. In that record you just created, you paste the value from $DonorID, which is the PK from the Donor table. That provides the basis for your relationship, which is PK_DonorID in the Donor table to FK_DonorID in the Sample table.
Hope that helps a bit.
For GREAT training, get the Basic FileMaker Training Series. It's free, too.