1 Reply Latest reply on Feb 16, 2017 2:40 PM by IT_User

    Ideal Table relationship structure for graphing data from multiple tables

    huang-j

      So I'm very new with filemaker pro and I'm stuck on creating more optimal relationships in the my database. I currently have a database containing records of patients. Each patient can have any amount of blood drawings, treatments etc. These are then related to the patient record through an ID. So a primary table for each patient, which is then linked to a treatment table (a new occurrence with each treatment).

       

      Now I need to pull the data from all of that specific patients blood drawing/treatments and filter it by a specific analyte/treatment. I do this by using an SQL command to pull out the fields.

      e.g.

       

      Executesql ( "

      SELECT dates, results

      FROM blooddraw

      WHERE analyte = ?

      AND patient_ID = ?

      ";

      "";

      "";

      analyte, patient_ID)

       

      This gives me all the data I need. But now my problem is how I should go about organizing this data in tables. What I'm currently doing is definitely not a good way of organizing the data. I currently have a table linked to the patient called "Graph". And branching off graph I have table (e.g. WBCCount, RBCCount)  that has the field of 'Data' which is populated by the Executesql query, and the patient_ID. I then move this data to a html doc where I graph the data in javascript after delimiting (I'm using highcharts).

       

      This gives me a redundancy in data, and I haven't exactly figured out the optimal way to organize and move this data. I'm unsure how I should proceed with this. I'm unsure if I should continue to use a field on a table which contains this data, if I should use a script to populate fields each time the layout is accessed, or something entirely different.

       

      Thanks!