10 Replies Latest reply on Sep 19, 2012 4:06 PM by daveeed

# Calculations using values from related records

### Title

Calculations using values from related records

### Post

For the following data structure

Tables: Patient ->> Evaluation

A patient completes many functional evaluations over many weeks.

For a given patient I would like to calculate the difference in their scores between the first and second evaluation as well as the difference in their scores between the second and third evaluation.

There is no limit to the number of evaluations that can take place (normally less than 6). I am storing PatientID, ID, Date and Score in the evaluation table.

Is this possible with Filemaker calculation fields?

Thanks,

David

• ###### 1. Re: Calculations using values from related records

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 )

• ###### 2. Re: Calculations using values from related records

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?

Thanks,

David

• ###### 3. Re: Calculations using values from related records

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.

David

• ###### 4. Re: Calculations using values from related records

That's one option.

Related table::Score - Last ( Related Table::score )

Might be a simpler way to get the same result, however.

• ###### 5. Re: Calculations using values from related records

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.

• ###### 6. Re: Calculations using values from related records

Are all the records for a given patient included in the report or only some of them?

• ###### 7. Re: Calculations using values from related records

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!

David

• ###### 8. Re: Calculations using values from related records

Actually I just noticed that the diffence between scores doesn't work. It takes the value from the previous patient. Darn.

• ###### 9. Re: Calculations using values from related records

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.

• ###### 10. Re: Calculations using values from related records

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?