AnsweredAssumed Answered

Calculation fields misbehaving!?

Question asked by eriso3 on Sep 26, 2018
Latest reply on Oct 1, 2018 by gregstock



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.


Table A:

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.


Table B:

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!