? self relationship
Hard to explain this but here goes;
database structure: PATIENT table / TUMOUR table / TREATMENT table (1 2 many relationship so each patient can have multiple tumours and multiple treatments per tumour). There are 3 main layouts patient / tumour / treatment; there is a portal on the patient layout to get you to the tumour layout and similarly tumour to treatments using a button on the portal.
In treatment, there are a number of fields that are the same when a second record for that tumour is created, what i want to happen is when a subsequent record is created those constants for that tumour are automatically entered. My example is DATE_OF_FIRST_TREATMENT - on record 1 that is entered (this is the earliest of 3 date fields) at the moment I enter that manually (I haven't managed to figure how to get FMP to choose between DATE_OF_SURGERY, START_DATE_RADIOTHERAPY and START_DATE_CHEMOTHERAPY).
I have created a script to duplicate the record then delete out the fields that are not the same, however this means clearing aout 200 fields ;-( when i click on new it creates a new blank record, not one that is related to the tumour?
Any suggestions how to improve this situation - its naive and clunky at the moment.