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.
What is the redundancy?
Is it the "Data" field in the different tables?
What is in there that can be broken into different fields & tables?