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.
Executesql ( "
SELECT dates, results
WHERE analyte = ?
AND patient_ID = ?
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.