1 2 Previous Next 15 Replies Latest reply on Oct 2, 2015 7:59 AM by Gerrly

    Relationships gone awry

    Gerrly

      I'm not sure what I'm doing wrong.  I'm going to try my best to explain the situation, but I'll be happy to elaborate or add more screenshots.

       

      Okay, so I have a genetics database.  This particular table, Phenotype Correlation, should be matched on Patient ID and HGNC ID (HGNC is gene symbol).  Each record in the table should display only one Patient ID and the portal window should list all HGNC IDs (genes) matching that Patient ID.  Instead, what is happening, is that the data gets loaded and however many genes are linked to that patient ID is how many records are created and nothing populates in the portal.  For example, patient 0097 data was loaded and 454 records were created.  What I wanted was 1 record to be created with 454 genes displayed in the portal.  Make sense?

       

      So take a look at the relationships.  I have it so that new records can be created and deleted in the phenotype correlation table if new records are created/deleted in the Patients or Genes 2 tables.  The portal on the phen corr table is displaying records from Genes 2.

       

      Now, it does work if I change the phenotype correlation table to display records from Patients table, but this is not what we want.  Only a small cohort from patients will be needed in the phen corr table, so separate data will be loaded.

       

      Thanks if you made it this far.  Any suggestions are welcomed.Screen Shot 2015-10-01 at 3.26.05 PM.pngScreen Shot 2015-10-01 at 3.26.39 PM.png

        • 1. Re: Relationships gone awry
          erolst

          Gerrly wrote:

          Okay, so I have a genetics database.  This particular table, Phenotype Correlation, should be matched on Patient ID and HGNC ID (HGNC is gene symbol).  Each record in the table should display only one Patient ID and the portal window should list all HGNC IDs (genes) matching that Patient ID.

           

          Then you should base your layout on the Patient table. If you want to do this from the context of PhenotypeCorrelation, you need a chain like

           

          PhenotypeCorrelation --< PhenotypeCorrelation__self --< Genes

           

          where the self-join relationship is defined as

          PhenotypeCorrelation::PatientID --< PhenotypeCorrelation__selfByPatient::PatientID

           

          meaning leave your layout as is, and insert the additional TO.

           

          (btw, Genes 2 isn't a good name; if you have several TOs of the same base table, name them to indicate their purpose and or location in the (literally) larger picture.)

           

          Every record in the join table PhenotypeCorrelation couples one (1) patient with one (1) gene, so all you'll ever see here with your existing relationships is exactly that (for which you wouldn't need a portal). So you need to run this once through the self-join (to get all the other records for that patient)

           

          Having said that: you should see that one Gene record in the portal; if you don't, I'd start by checking the underlying data: is there really a record in Genes with a primary key value that matches the foreign key in PhenotypeCorrelation?

          • 2. Re: Relationships gone awry
            BruceRobertson

            What is the purpose of Patient Variants 2?

            AS currently set up, it appears to allow you to see every other patient with the same gene variant.

            • 3. Re: Relationships gone awry
              Gerrly

              I know, the one gene should at least show up, and it DID before, the way I have it set up.  I then deleted all the records (they were test records) and then my programmer uploaded some data and it doesn't work.  Could it have been a matching error?  I'm going to try your suggestion, also.  And the names... yeah, I don't rename them when I duplicate them.  Genes 2 was generated automatically.  It actually makes more sense for me to leave it like that because I like to know what TO it's coming from.  Don't really care about its function.

              • 4. Re: Relationships gone awry
                Gerrly

                Patient Variants 2 was created to display portal records from its own TO (patient variants).  It works.

                • 5. Re: Relationships gone awry
                  BruceRobertson

                  No it doesn't. At least; not as illustrated. You would need to add a match on the patientID field but you show only a match on the CHR_POS field.

                  • 6. Re: Relationships gone awry
                    Gerrly

                    Yeah it does.  This isn't even relevant to my original question.  I matched it on that because I wanted to display all patients who have that particular variant in a portal.  Don't care about what patient.Screen Shot 2015-10-01 at 4.53.49 PM.png

                    • 7. Re: Relationships gone awry
                      Gerrly

                      Can you explain this more?  What is a self join table?  What does "ByPatient" mean in the end of the relationship example you gave?  I'm really an intermediate user.  Maybe not even.

                      • 8. Re: Relationships gone awry
                        BruceRobertson

                        Gerrly wrote:

                         

                        Patient Variants 2 was created to display portal records from its own TO (patient variants).  It works.

                        Yes; I see that it does work (and does do what I described)

                        In my earlier reply, I think I misread your comment. The portal screen shot shows what I mentioned - a relation to all records for the same variant. And also what you intended the relationship to do.

                         

                        When you said "patient varients" I at first thought you meant same-patient variants. My mistake; sorry.

                        • 9. Re: Relationships gone awry
                          Gerrly

                          Oh, gotcha.  That's okay!

                          • 10. Re: Relationships gone awry
                            erolst

                            Gerrly wrote:

                            What is a self join table?

                            Relating two table occurrences (TOs, instances) of the same base table to each other.

                             

                            Gerrly wrote:

                            What does "ByPatient" mean in the end of the relationship example you gave?

                             

                            It gives you a clue as to what this TO does and how it is related. (Contrary to what you seem to think, this sort of information is important, and you should care.)

                             

                            Gerrly wrote:

                            Can you explain this more?

                             

                            Actually, I think I spelled out what you need to do, but here it is again:

                             

                            • create a new TO of PhenotypeCorrelation and give it a meaningful name

                            • delete the existing relationship between PhenotypeCorrelation and “Genes 2” (and give that one a proper name, while you're at it – Genes_for PhenotypeCorrelation comes to mind …)

                            • relate PhenotypeCorrelation and the new TO of PhenotypeCorrelation by matching the respective PatientID field

                            • relate the new TO of PhenotypeCorrelation to “Genes 2” by matching the HGNC_ID

                             

                            So you went from

                             

                            Patient --< PC >-- Genes 2

                            to

                            Patient --< PC --< PC-self >-- Genes 2

                             

                            Now your portal on the PhenotypeCorrelation layout still looks into “Genes 2”, and that is OK; the relationship chain has changed because you inserted the new TO, but that doesn't concern FileMaker, because the reference (i.e. the path description from your layout TO to the portal TO) hasn't changed.


                            Meaning now you should see all the Gene records associated with the current PatientID of your PhenotypeCorrelation record; if not, something's off with your data.

                            • 11. Re: Relationships gone awry
                              BeatriceBeaubien

                              Hello Gerrly,

                               

                              This may not appear to come to bear on your immediate issue (I think it possibly does), but the phenotype is an entity, or at least it is in two very similar solutions I am working on (paediatric ophthalmology genetics, clinical and research). Instead of thinking of Phenotype Correlations as a join table without a distinct primary key, I would encourage you to think in terms of establishing the Phenotype as a distinct table, with its own primary key and at least one field of description. These Phenotypes can be related to more than one mutation. I believe this will help put into sharper focus problems you may be encountering in naming table occurrences and establishing the display of related information.

                               

                              In addition, I agree with earlier posts; getting a more accurate table occurrence name than "Genes 2" will serve you better in the short and long term as you develop this solution. Naming is critical to understanding the assumptions involved in building the relationships and they help in displaying related data.

                               

                              I hope this is useful.

                               

                              Best wishes,

                               

                              Beatrice Beaubien, PhD

                              i2eye, Toronto, Canada

                               

                              FileMaker Business Alliance

                              FileMaker 14 Certified Developer

                              Knowledge Translation Certified Professional

                              • 12. Re: Relationships gone awry
                                Gerrly

                                • create a new TO of PhenotypeCorrelation and give it a meaningful name

                                • delete the existing relationship between PhenotypeCorrelation and “Genes 2” (and give that one a proper name, while you're at it – Genes_for PhenotypeCorrelation comes to mind …)

                                • relate PhenotypeCorrelation and the new TO of PhenotypeCorrelation by matching the respective PatientID field

                                • relate the new TO of PhenotypeCorrelation to “Genes 2” by matching the HGNC_ID

                                 

                                So you went from

                                 

                                Patient --< PC >-- Genes 2

                                to

                                Patient --< PC --< PC-self >-- Genes 2

                                 

                                Now your portal on the PhenotypeCorrelation layout still looks into “Genes 2”, and that is OK; the relationship chain has changed because you inserted the new TO, but that doesn't concern FileMaker, because the reference (i.e. the path description from your layout TO to the portal TO) hasn't changed.

                                Okay, I did this and the gene info appears in the portal properly, but it still creates a new record for every gene related to that patient.  See in the screenshot, patient 37 has 426 genes associated with it.  So it created 426 records and also put 426 records in each of the 426 records' portals.Screen Shot 2015-10-02 at 10.26.58 AM.png

                                • 13. Re: Relationships gone awry
                                  Gerrly

                                  Beatrice Beaubien wrote:

                                   

                                  Hello Gerrly,

                                   

                                  This may not appear to come to bear on your immediate issue (I think it possibly does), but the phenotype is an entity, or at least it is in two very similar solutions I am working on (paediatric ophthalmology genetics, clinical and research). Instead of thinking of Phenotype Correlations as a join table without a distinct primary key, I would encourage you to think in terms of establishing the Phenotype as a distinct table, with its own primary key and at least one field of description. These Phenotypes can be related to more than one mutation. I believe this will help put into sharper focus problems you may be encountering in naming table occurrences and establishing the display of related information.

                                  Thank you, but I already have a phenotype table that is related to multiple variants.  This phenotype correlation is for a different, specific purpose.

                                  • 14. Re: Relationships gone awry
                                    erolst

                                    Gerrly wrote:

                                    Okay, I did this and the gene info appears in the portal properly, but it still creates a new record for every gene related to that patient.  See in the screenshot, patient 37 has 426 genes associated with it.  So it created 426 records and also put 426 records in each of the 426 records' portals.

                                     

                                    You got me confused here; what is “it”, and why does “it” “still” create 426 records?

                                     

                                    Also, did you post the wrong screenshot? Where can I see here that this patient has 426 genes associated with them? I can see a found set of 426 records, but the portal is empty. (Assuming that the blank area on the left side is the portal. Is it?)

                                     

                                    Anyway, the entire discussion was based on your issue of not being able to display – in the PhenotypeCorrelation context –all associated Genes of the patient of the current PhenotypeCorrelation record. How you create(d) these records wasn't pertinent here.

                                     

                                    And of course, while you traverse the PhenotypeCorrelation records of a patient, a portal into Genes will always show the same set of related genes.

                                     

                                    Out of curiosity: how did you create the PhenotypeCorrelation records?

                                    1 2 Previous Next