6 Replies Latest reply on May 22, 2009 12:57 PM by ralvy

    What is the best way to produce running differences?

    synergy46

      Title

      What is the best way to produce running differences?

      Post

      I have a table:  Tests

         TestsID.pk

         PatientID.fk

         DoctorID.fk

         TestName

         Value

          Notes

       

       The input layout is on a portal and it works great.  (See picture below)

       

      I have produced a sub-summary report below that also works great.

      But, I want to add a field that shows the difference in Values between

      succeeding dates.   For example, the June 15, 2002 'diff' field would be to the right of

      Value and show 22-5.2=16.8 .  

       

      What is the best way to do this?

       

        • 1. Re: What is the best way to produce running differences?
          philmodjunk
            

          Is it always the difference between the current value and the same value of the previous record? (same patient, same test of course)

           

          I've been playing with several ways to auto-enter the value from the previous record. It might be possible to do that with the right self-join relationship, but I'm not pulling all the pieces together in a way that looks like it will work. (I'm assuming that due to the unpredictible aspects of real life, you won't always date each test in perfect one month intervals like your example.)

           

          Alternatively, you could set up a script that uses the current record's date, test ID and patient id data to find all matching records, sort by date in descending order, then it could go to the 2nd record in the found set and use set variable to copy that data into a "previous value" field. Your difference would then be a simple calculation field.

           

          Last step: set the above script so that Commit Record (a layout script trigger) or exit object (on the value field) triggers this script.

          • 2. Re: What is the best way to produce running differences?
            synergy46
              

            Is it always the difference between the current value and the same value of the previous record? (same patient, same test of course)

             

            Yes, I need the difference between the two adjacent records.    It sounds like you are thinking the same way I am.  But, I don't have a handle on how to setup the 'self-join' relationship.  (new territory for me).

             

            Thanks

             

             

            • 3. Re: What is the best way to produce running differences?
              philmodjunk
                 I couldn't either, that's why I suggested a scripted alternative.
              • 4. Re: What is the best way to produce running differences?
                KIDO
                   <!--            @page { margin: 2cm }           P { margin-bottom: 0.21cm }        -->

                You can pull the value from adjacent record to use in a calculation:

                 

                GetNthRecord(fieldName; Get(RecordNumber)-1)

                Regards

                • 5. Re: What is the best way to produce running differences?
                  philmodjunk
                    

                  KIDO,

                   

                  I thought of that too, but am not convinced it would always be an adjacent record in this example.

                  • 6. Re: What is the best way to produce running differences?
                    ralvy
                      

                    How about creating two Table Occurrences (TOs) related like this, where TO 1 fields are on the left and TO 2 fields are on the right in following relationship:

                     

                    PatientID = PatientID

                    Date > Date

                     

                    Have the relationship sort by Descending Date in TO 2.

                     

                    Now, assuming the layout is based on TO 1, just create a Calculation Field that looks through the above mentioned relationship. When it does that, it will grab values from a record that is dated just before the current record, if my reasoning is okay here.