Take a look at GetNthRecord. It can be used to access data in the previous record i the current found set.
Score - If ( Get ( RecordNumber ) > 1 ; GetNthRecord ( Score ; Get ( RecordNumber ) - 1 ) ; 0 )
Thanks very much for your quick response. That does the trick for differences between adjacent scores.
One last question, how would I get the difference in scores between the first and last scores, not knowing how many related records there are in total?
I think I figured it out.
GetNthRecord(Score; countScores) - GetNthRecord(Score; 1)
where countScores is a summary field for count of ID on the evaluation table. Seems to work.
Thanks again for your help,
Can these calculations be used in a report? For example can i display a list of all scores grouped by Patient, but display the difference between First and Last score for each patient in the list somehow? My current list displays the difference between the first and last score in the list (e.g. -2) in every record.
Are all the records for a given patient included in the report or only some of them?
All of them. I believe I have finally sorted it out. Using your calculation works because it is on the Person table. Thanks for all your help!
Actually I just noticed that the diffence between scores doesn't work. It takes the value from the previous patient. Darn.
We are looking at two different calculations--one that computes a difference from the previous record and one that computes the difference of the first and last scores.
The difference from preceding score can be modified to check to see if the preceding record is for the same patient.
If ( GetNthRecord ( PatientID ; Get ( RecordNumber ) - 1 ) = PatientID ; Score - GetNthRecord ( Score ; Get ( RecordNumber ) - 1 ) )
The difference between first and last records can be computed via a Self Join relationship between the table of scores and a second occurrence of scores, linked by PatientID
Scores::PatientID = ScoresSamePatient::PatientID
Last ( ScoresSamePatient::Score ) - ScoresSamePatient::Score
would compute the difference between the first and last score for a given patient's total set of records in the Scores table.
I have yet another twist to this problem.
The client would like to
1. report based on who provided the score (patient or parent) so I have created a separate report for each of the criteria "completed by" = "parent" or "completed by" = "patient"
2. group by visit number inside the "completed by" report.
How would I calculate this relative "visit number" assigned according to date for existing Parent Score records and then again for Patient Score records?