7 Replies Latest reply on Jan 26, 2009 6:44 AM by comment_1

    calculation on different rows in same column



      calculation on different rows in same column


      can anbody tell me how to make a calculation (a simple subtraction) between different rows in the same column. we have a database with patient IDs in one column, scores on tests in other columns, and visit numbers in another column. basically, i want to calculate the difference between scores on the tests between each visit for each patient (e.g., patient1, test1, visit 2 score - visit 1 score, etc., etc.). just to be clear - all the scores for a given test are in a single column but different rows correspond to scores on a given visit.


      patient    testAscore visit#

       0001      75              1

       0002      82              1

       0001      88              2

       0003      90              1

       0004      67              1

       0002      88              2

      ....etc., etc.


      and i want to calculate 0001 88-75; 0002  88-82 , etc., etc.

        • 1. Re: calculation on different rows in same column

          Hi Stephen


          Do you have a table with the patient record, where you get the Patient ID from? And do you want to see the total score for that patient in there Patient Record?


          Lets say you have tables for PATIENT and TEST, change accordingly for your system, you need to create a relationship between the two in the Relationships Graph, linking by the Patient ID, as below


          PATIENT              Patient_TEST
          PatientID  -- = --  PatientID


          Now in the Patient table create a calculation with the following


          Sum ( Patient_TEST::testAscore )


          This will add up all the tests for the patient record. 


          I hope this answers your question and let me know if anything is unclear.

          • 2. Re: calculation on different rows in same column



            Thank you for your post.


            A calculation field only calculates the current record.  This can be accomplished but you would need to create some additional fields and set up a relationship into the current table.  An easier way is to run a script that will put the difference into a Number field.  Here is the easy way...


            Create an additional number field titled "Difference".  This will capture the difference of the test scores into the field.


            Next, create a script with the following script steps:


            Go to Layout [ <Layout that contains the fields> ]

            Show All Records

            Sort Records [Restore (sort by patient and visit#) ; No dialog ]

            Go to Record/Request/Page [ First ]


            Set Variable [$pat; Value: "" ]

            Set Variable [$score; Value: "0" ] 



               If [patient ≠ $pat ]

                  Set Variable [$pat; Value: patient ]


                  Set Field [Difference; testAscore - $score ]

               End If

               Set Variable [$score; Value: testAscore ]

               Go to Record/Request/Page [Next; Exit after last ]

            End Loop




            Explanation: This script first goes to the layout that includes the patient, testAscore and visit# fields.  Next, show all records and sort by patient and visit# to get the records in the appropriate order.  We then go to the first record so we can start.


            We will be using some variables to keep track of data from previous records (patient and testAscore).  We first initialize $pat to "" (null) and $score to zero.


            The loop is entered and we check to see if the patient field has changed.  For the first record, the patient will not equal null, so we change the value of the variable $pat to the contents of the field patient.


            If the patient equals the variable $pat, then we know we are on a multiple visit for this patient, so we subtract the previous testAscore (stored in $score) and subtract it from the current testAscore and place that information in the field "Difference".


            Regardless if the patient equals or not equals $pat, we store testAscore into the variable $score and go to the next record, and evaluate again.  If the last record is reached, then the loop is exited and the script ends.


            I hope this helps.  If you need clarification for any of the above steps, please let me know.



            FileMaker, Inc. 

            • 3. Re: calculation on different rows in same column
                 If you define a self-join relationship of the Scores table as:

              Scores:: PatientID = Scores 2:: PatientID
              Scores::VisitID > Scores 2::VisitID

              and sort the related records from Scores 2 by VisitID, descending, you can then define a calculation field =

              Score - Scores 2::Score

              to return the difference between the current score and the previous one. Alternatively, using the same relationship, you could lookup the previous score into a local field - that would make it display faster in a list/table view.

              • 4. Re: calculation on different rows in same column

                Hi comment,


                I understand what you are doing, but I think you may have a t-i-n-y technical weakness in your calculation.  You are sorting the records in Scores 2 because you want the record in Score 1 to match the record in Score 2 that is immediately before it in date order.  But my understanding is that with LookUps, for example, Filemaker's concept of the 'first', next', or 'last' record is totally independent of any sort order.  (Unlike Excel LookUps, for example, which have to be carefully sorted into ascending values or the whole thing falls over.  Yet another reason not to use Excel as a database.)  My understanding is that the 'first, 'next', etc is always the order of record creation - no matter how the records are sorted for display in the database.


                In many cases these will be the same things, but I'm just wondering what would happen if there was a backlog of data entry, for instance, and they started entering test results 'from the top of the in-tray', in other words, in reverse chronological order.  Then the 'next lowest record' by date created would not be the one you want.


                The problem does not exist with TSGal's script, as it loops through each record in genuine date-sorted order.


                Also, I'm probably in 'wood-for-trees' mode here, but I'm at least momentarily stuck trying to figure out how to sort the scores in the Score 2 Table Occurrence. Is that just the same as sorting in the first Table Occurrence?


                I recognise that if you define VisitID to be something like PatientNameAndDateOfVisit then it almost certainly would work, but I'd just like to give a 'heads up' to  people who might take your idea as being more universally applicable.  I'd be interested to hear if TSGal can confirm that sorting records has no effect on how they are matched by relationship or lookup.



                (PS: one (ugly) way around it if no other exists is to sort the records, export them, delete the originals, then re-import them.  Works! - but, boy is that a last resort...)

                • 5. Re: calculation on different rows in same column

                  Sorbsbuster wrote:

                  My understanding is that the 'first, 'next', etc is always the order of record creation - no matter how the records are sorted for display in the database.

                  Not really. First, last etc. is determined by the sort order of the relationship. This has nothing to do with how records are sorted when viewed in their own table.


                  The default sort order of relationship is indeed record creation, but it can be changed in the relationship's definition - see #4 here.



                  Scripted loops that mark records are always problematic, since some records may be locked by other users.



                  Sorbsbuster wrote:

                  if you define VisitID to be something like PatientNameAndDateOfVisit

                  I would define VisitID as an auto-entered serial number.

                  • 6. Re: calculation on different rows in same column

                    Well, I learn something new every day, comment!


                    I am convinced that I was caught out by that way back in FMPro days (yes - no version number!) and worked around it.  I therefore never had to re-visit that discovery to see if it had changed.  I suppose it changed with version 7, whenever you could add 'Sort by...' to the relationship.  I'll have to go back over my files and see if I can exploit this!


                    But I would still worry about defining the VisitID as an auto-enter serial number, on the basis that they might create the records out of chronological sequence.


                    Thanks for the link to the help files - but to be honest if that was all I had read (without your explanation) I *still* would not have twigged that sorting the relationship over-rode the order of creation.  All that it seems to refer to implies (to me) how it is displayed in portals, not that it actually affects the relationship calculation.


                    Thanks again,


                    • 7. Re: calculation on different rows in same column
                         Sorting related records has been possible at least since version 4. Note that a portal can have its own sort order, which overrides the sort order defined for the relationship.

                      If they enter visits out of order, then VisitDate could be used instead of VisitID. But it doesn't seem very likely they would do that - see the original post.