I´m a PhD-student using Filemaker pro to sort and organise medical data. I currently have problems creating a report on historic treatments and need you input.
I use two related tables.
Contains fields with patient ID, name of the drug, starting date and end date.
Every unique patientID has at least 10 records and there is about 7000 patientIDs.
Contains fields with patientID, a date of a blood sample, the value of the blood sample, followed with about 30 fields with different drugnames. This table is also rather big with about 50-100 records for each unique patientID.
I want Filemaker to complete the fields with drugnames in Table B with information from Table A. PatientIDs are related between the tables and the date of the bloodsample in Table B is ≥ to Table A´s starting date and ≤ to the enddate.
The fields with different drugnames in Table B are all calculated-fields set up with the Case-function: Case (TableB::drugA = “drugnameA”;”1”)
The end result should be that Table B, for each record, has ≥ 3 different drugfields with “1” for each bloodsample but what I get is seemingly random results with max 1 (but not consistent) drug/bloodsample. I´ve checked that all date fields are categorized as dates, that there is no space before or after the drugname, that the calculationresults are numbers but that hasn´t helped.
Am I using the wrong method? What have I missed? I would very much appreciate your thoughts!