5 Replies Latest reply on Sep 23, 2015 4:28 PM by dtcgnet

    Portal Problem

    DamianWhite

      All,

       

      I have a related database consisting of two tables, Specimen and Experiment, as detailed in the image below.

       

       

      In the Specimen table, S_ID is a field of automatically entered serial numbers.  I then use these values to link records through to the Experiment table, and use E_ID as a field of automatically entered serial numbers in that table.

       

      So, in the Specimen table, Donor 164 has given three specimens, with S_ID values of 62, 63 and 274.

       

      In the Experiment table, this first two specimens has been split into two replicates each, giving 5 records in total for the one donor: two pairs sharing the same S_ID, but all five with a unique E_ID.

       

      What I'd like to accomplish is set up a portal in the Specimen table to show all records from the current donor, irrespective of which S_ID record I'm viewing.  Thus, if I'm looking at S_ID 62 within my Specimen table, I'd like a portal that shows all 5 records for PN164 in my Experiment Table.  However when I try to do this, I end up with a portal that only shows the related records for that S_ID number ... so when I'm viewing S_ID 62, I only see E_ID 139-140 ... and when I'm viewing S_ID63, I only see E_ID 141-142.

       

      Is there any way to set up or configure a portal such that when I'm viewing either S_ID 62, 63 or 274 in my Specimen table, I see all five related records (i.e. 139-142 and 479) via Donor ID PN164?

        • 1. Re: Portal Problem
          dtcgnet

          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.

           

          Post pictures.

          • 2. Re: Portal Problem
            DamianWhite

            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.

             

             

            Capture4.PNG

            • 3. Re: Portal Problem
              dtcgnet

              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

              • 4. Re: Portal Problem
                DamianWhite

                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.

                 

                Thanks again.

                • 5. Re: Portal Problem
                  dtcgnet

                  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.

                  Database Skills, FileMaker Pro Training | FileMaker