I have designed a db for storing medical research data. I want to be able to export sets of records to carry out statistical analysis in SPSS. I want to export each patient as a single row. My problem is that I have measures taken from each patient at mulitple times eg Blood Pressure (BP). In my db I have linked my Recruitment Table which contains patient information to my Blood_Pressure Table (via a field called recuit id). This all works well in FM - but to export it in this format the BP measurements are exported as separate rows.
So - in my export layout I need fields called BP1, BP2, BP3 etc (up to 7 possible blood pressure readings) and I will then export them with all the patient info in the Recruitment Table as a single record. I've tried creating an export table (linked to my Recruitment table via recuit id) with a calculation field called BP1 - the calculation is:
If ( Blood_Pressure::recruit_id = recruit_id and Blood_Pressure::time_period = 1; Blood_Pressure::systolic;"" )
In other words if the recruit id in my Blood_Pressure table = the recuit id in my Export table and the time it was taken during the study is in the first time period then put this reading (from the field called systolic in my Blood Pressure table), into this new field (called BP1) in the Export table, otherwise leave it blank.
The calculation works, my problem is when I want to populate my field BP2 using the same formula but with time_period = 2, it doesn't work. It appears that it is only ever getting data from the first record where "Blood_Pressure::recruit_id = recruit_id" when in fact there will be multiple records where this is the case.
Any suggestions would be greatly appreciated.
You can use the GetNthRecord() function to get ... well, the value from the n-th related record, obviously.
Alernatively (and preferably, IMHO) export your records as XML and use a custom XSLT stylesheet to tranform them to a format acceptable to the target application.