4 Replies Latest reply on Mar 14, 2012 8:59 AM by philmodjunk

# Calculating the difference between the latest and initial scores

### Title

Calculating the difference between the latest and initial scores

### Post

I'm trying to create a field that calculates the difference between two records of the same field as follows:

PORTAL (in Table: Customers), linking to related Table: MYMOP (where every 5th treatment a record is created, recording a score for each symptom)

Example:

Tx No          Sym 1       Sym 2

1                6              4

6                5              4

11               2               1

I'd like a field that calculates the difference between 'the latest score for symptom 1' and 'the initial score for symptom 1' (for each  customer)

I've created a field that finds the MAX ( Treatment No ) - i.e. the latest treatment, but not sure how to look up the value for the 'symptom 1 score' for that record, or how to then find the difference between that score and the initial score.

Any help would be much appreciated.

• ###### 1. Re: Calculating the difference between the latest and initial scores

I gather that you have two tables Customers---<MyMop    (---< means one to many)

In customers, a calculation field can refer to MyMop::Sym 1 to refer to the first related record and Last ( MyMop::Sym 1 ) to refer to the latest related record--provided that the relationship does not specify a sort order or that specifies a sort order that keeps the rcords in the order that they were created.

• ###### 2. Re: Calculating the difference between the latest and initial scores

That sounds like it should do the job - what's the syntax for specifying which record to refer to?

Presumably once that's in place, it's as simple as :

MYMOP::Symptom 1 Score [Last Related Record] - MYMOP::Symptom 1 Score [First Related Record]

• ###### 3. Re: Calculating the difference between the latest and initial scores

Ah - after a bit of looking around, I now see that your answer did actually specify the syntax. I'd got the calculation as:

GetNthRecord ( MYMOP::Symptom 1 Score ; 1 ) - Last ( MYMOP::Symptom 1 Score )

I see that not specifying which record automatically retrieves the first - is there any value in specifying it explicitly as I have done above?