AnsweredAssumed Answered

Ideal Table relationship structure for graphing data from multiple tables

Question asked by huang-j on Feb 15, 2017
Latest reply on Feb 16, 2017 by IT_User

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!

Outcomes