11 Replies Latest reply on May 11, 2010 11:27 AM by comment_1

    Table Relationships

    halfour

      Title

      Table Relationships

      Post

      I am using Filemaker Pro 11 Advanced and have used FM since version 4.  I have built relationships before, but this one I am having trouble with.  My priamry  table has, in addition to other fields,  24 diagnosis fields with names diag01, diag02, diag03...to diag24.  These fields have a text format.  There is also a  field called Diag Type all the records have a D entered.

       

      I have another table called Diag Desc.  This table has 3 text fields.  Type, Diag Code, Diag Description.   In Diag Desc the field Type is D which matches Diag Type in my primary table.

       

      In my relationship I matched Type from Diag Desc table to  Diag Type in the primary table, then I connected Diag Code to diag01.  On my form next to diag01 I put the Diag Description field and the description is correct when only one of the diag fields (diag01, diag02...) is selected.

       

      Here is the problem - When I try to link Diag Code to subsequent diag02, diag03...and so on the Diag Description does not fill in and I lose the description for diag01 also.  Any suggestions appreciated.  Hal

        • 1. Re: Table Relationships
          comment_1

          It would help knowing what are the real things being modeled here. In most cases, instead of numbered fields such as diag01, diag02, diag03... one should use individual records in a related table.

          • 2. Re: Table Relationships
            halfour

            I know what you are saying.  The primary table that has diag01...diag24 is part of one patient record.  The records are imported from a csv file from another program.  I have contacted the company that provides the data to include a field for the diag description in the csv.   They are working on that.  However, for reporting puposes we need the description now, so I created the table Diag_Desc with the three fields I indicated thinking I could create the relationship based on matching diags and by having each record in the primary table have D in the type field. 

            hal

            • 3. Re: Table Relationships
              comment_1

              The way you have it now, you would need to set up 24 relationships between the Patients table and the Diag Desc table, to get the correct description for each diagnosis.

               

               


              halfour wrote:

              I have contacted the company that provides the data to include a field for the diag description in the csv.


              There's no need for them to do so - but they probably could make the format easier for you to import (you need to import the patient - if new - into the Patients table, and their diagnoses into a Diagnoses table). Failing that, you could still import the data into a temp table (similar to your existing table) and then break it up into the 'real' tables.

               

               

               

              I didn't quite get the part with the "D" in the type field. If all diagnoses are of type "D", then this field is redundant in both tables.


              • 4. Re: Table Relationships
                halfour

                You are correct - I do not need the D in the patient record.   I am still confused about what you are saying in regards to breaking down the patient record.  The patient record is one record in the Patient Table, which is my primary table in the database, which happens to have 24 diagnosis fields in it. 

                 

                The Diag Description table has three fields Type, Diag Code and Description. 

                 

                Here is how the relationship is set:

                 

                Table:  Patient                      Table:   Diag Description

                 

                Field:  diag01            =               Field:  Diag Code

                 

                Result on my form:        diag01:    V89         Description (from Diag Description table):   Admin Proc  

                 

                Problem is when you add diag02 so you have the following

                 

                Field:  diag01            =               Field:  Diag Code

                AND

                Field:  diag02            =               Field:  Diag Code

                 

                The result is a blank description for both diag01 and diag02.  It is basically a one to one relationship for each field.  diag01 or 02 is looking for a match from the Diag Code field.  Thanks.

                 

                 

                 

                • 5. Re: Table Relationships
                  comment_1

                   


                  halfour wrote:

                  It is basically a one to one relationship for each field.  


                   

                  That's true - and that's why you need a separate relationship for each field. Adding a matchfield to the existing relationship is not going to work, because there is no record in the Diag Description table that meets the condition:

                   

                  Patients::diag01 = Diag Description::Diag Code

                  AND

                  Patients::diag02 = Diag Description::Diag Code

                   

                  unless diag01 and diag02 happen to contain the same code.

                   

                   

                   


                  halfour wrote:

                  I am still confused about what you are saying in regards to breaking down the patient record.  The patient record is one record in the Patient Table, which is my primary table in the database, which happens to have 24 diagnosis fields in it.


                   

                  I am suggesting a structure of three tables: Patients, Diagnoses and DiagnoseDescriptions, related as follows:

                   

                  Patients::PatientID = Diagnoses::PatientID


                  Diagnoses::Diag Code = DiagnoseDescriptions::Diag Code

                   

                  Instead of 24 fields in the patients table, you will have 24 (or any other number) of related records in the Diagnoses table - and each one of these will fetch its own matching description from the DiagnoseDescriptions table.



                  • 6. Re: Table Relationships
                    halfour

                    So are you saying create 24 separate tables to import the diagnosis into?  Otherwise, in the diagnosis table I will have patient id and 24 diag fields which is what I have now.

                    • 7. Re: Table Relationships
                      comment_1

                       


                      halfour wrote:

                      So are you saying create 24 separate tables to import the diagnosis into?


                      Certainly not. You only need one Diagnoses table. In this table, you will have ONE diag field and 24 RECORDS per patient.

                       


                      • 8. Re: Table Relationships
                        halfour

                        Thanks - I will give it a try today.

                        • 9. Re: Table Relationships
                          halfour

                          Well, I have had success.  In the relationship window I created 24 copies of the Diag Table and linked each copy to a different diag in the patient table.   The Diag Table fields are Diag Code and Description.

                           

                          Structure is:

                           

                          Patient Table                       Diag Table 01     Diag Table 02              Diag Table 03

                           

                          Diag01                                   Diag

                           

                          Diag02                                                                   Diag

                           

                          Diag03                                                                                                         Diag

                           

                          and so on to Diag24

                           

                          The description is then linke to Diag01,  Diag02....Diag24.

                           

                          Thank you for your help on this all the comments helped me get this done.  Hal

                          • 10. Re: Table Relationships
                            RickWhitelaw

                            If you read Comment's posts correctly you'll see he suggests ONE Diagnoses table. There's no need for all the copies.

                             

                            RW.

                            • 11. Re: Table Relationships
                              comment_1

                              As I mentioned earlier, that is the only way to do it with your current structure. However, it is not a good solution, and you are likely to encounter limitations. A structure of three tables is both simpler and more flexible.