AnsweredAssumed Answered

? self relationship

Question asked by mark_d2x on Nov 12, 2009
Latest reply on Nov 12, 2009 by philmodjunk


? 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.