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
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.
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 ]
Set Variable [$score; Value: testAscore ]
Go to Record/Request/Page [Next; Exit after last ]
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.
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.
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...)
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.
if you define VisitID to be something like PatientNameAndDateOfVisit
I would define VisitID as an auto-entered serial number.
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.
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.