To pick the earliest of three dates in date fields: Min (Date_OF_SURGERY; START_DATE_RADIOTHERAPY; START_DATE_CHEMOTHERAPY ) You can put this in a set field step to script it or simply define a calculation field with this expression.
Are the "number of fields that are the same when a second record for that tumour is created" always the same and never differ for a given tumour? If so, then put those fields in the tumour table rather than treatment.
If not, you can create a script to create a new treatment record that works like this:
Set Variable [TumourID$ Treatment::TumourID]
Set variable [$Fld1; Treatment::field1]
Set Variable [$Fld2; Treatment::Field2]
and so forth
Set FIeld [Treatment::TumourID; TumourID$]
Set Field [Treatment::Field1; $Fld1]
Set Field [Treatment::FIeld2; $Fld2]
and so forth
Thanks I will give that a try tomorrow. That is definitely a more eloquent way of duplicating a record!
These variables change for a second tumour so need to stay in the treatment table
"These variables change for a second tumour so need to stay in the treatment table"
If I understand your system design correctly, a second tumour results in a new record in the tumour table right? Thus, placing these fields in the tumour table will not be an issue and will reduce the number fields you need to manage in the treatment table.
Yeah I guess so!
Problem now is if I create those fields in the tumour table, it means copying the data from treatment into them
The dataset supplied to me hasn't helped as they are in the treatment report for uploading.
Deadline for this year's data is 2nd Dec and I stlll have 35 records (x600 fields) to collect!
Perhaps after the 2nd... brain is trying to think ahead hence questions now before I forget!
There are several approaches you can use to automate moving the data from one table to the other.
A simple looping script run during "down time" that copies from the Treatment table to the related tumour record could do the job nicely.