5 Replies Latest reply on Nov 12, 2009 11:40 AM by philmodjunk

    ? self relationship

    mark_d2x

      Title

      ? self relationship

      Post

      Hi 

       

      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. 

       

      Thanks

       

      Mark 

        • 1. Re: ? self relationship
          philmodjunk
            

          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

          New Record/Request

          Set FIeld [Treatment::TumourID; TumourID$]

          Set Field [Treatment::Field1; $Fld1]

          Set Field [Treatment::FIeld2; $Fld2]

          and so forth

           

          • 2. Re: ? self relationship
            mark_d2x
              

            HI Phil,

             

            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

             

            Mark 

            • 3. Re: ? self relationship
              philmodjunk
                

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

              • 4. Re: ? self relationship
                mark_d2x
                  

                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! 

                • 5. Re: ? self relationship
                  philmodjunk
                    

                  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.