6 Replies Latest reply on Jul 1, 2014 9:39 AM by gregdc

# Calculation field between two records in a related table

### Title

Calculation field between two records in a related table

### Post

Two tables

Organization -------------------<   Scorecard

PKOrgID                                        FKOrgID

DifOfLastTwoYears                    ScorecardYear

Score

DifOfLastTwoYears  = Score of largest ScorecardYear - Score of next smallest ScorecardYear

I need help creating the Calculation for DifOfLastTwoYears field in Organization

Example data:

FKOrgId                     01           01         01

ScorecardYear    2012          2010   2009

Score                        20             18        16

DifOfLastTwoYears should be 2.

Can this even be done?

• ###### 1. Re: Calculation field between two records in a related table

Last ( Scorecard::Score ) - Let ( c = Count ( Scorecard::Score ) ;  If ( c > 1 ; GetNthRecord ( Scorecard::Score ; c - 1 ) ; 0 ) )

• ###### 2. Re: Calculation field between two records in a related table

That is one of the simplest solutions to a problem of this type I have seen.   Thank you.    One question remains in my mind, since there are multiple Organizations in the Scorecard table, am I correct that the only records that this calculation will pickup on match the PKOrgId and FKOrgId because of the relationship between the two tables?

If so this is fantastic, if not how do I handle this?

• ###### 3. Re: Calculation field between two records in a related table

OK, I see how it works, thanks.  I got it.

• ###### 4. Re: Calculation field between two records in a related table

OK I don't have it :((

I put in the calculation and I thought it was working fine.  Then I started adding data to the Scorecard table and the calc'ed field started returning bad results.

In the attached file I show the following:

The Relationship between the Organization and Scorecard table is based on OrganizationID

The Scorecard table's field layouts

The data in Scorecard table

The field calculation for ScorecardDif  is   ( Last ( OrganizationsScorecard::ScorecardFactor1 ) - Let ( c = Count (OrganizationsScorecard::ScorecardFactor1 ) ;  If ( c > 1 ; GetNthRecord (OrganizationsScorecard::ScorecardFactor1 ; c - 1 ) ; 0 ) )

Finally the results of the ScorecardDif calculation in my Portal/Display (ScorecardDif is labeled Changes on the portal/display)

Since Scorecard data is not always entered sequentially (year 2013 can be entered before 2010, etc)  The physical order of the table records seems to be throwing off the ScorecardDif calculation.

In this example the "Last(OrganizationScorecard::ScorecardFactor1) = 2.9[from year 2012] and the GetNthRecord is retriving 3.3[from year 2013], making ScorecardDif = -.4.   What should be happening is that Last should = 3.2[from year=2014] and GetNthRecord should be = 3.3[from year= 2013], making ScorecardDif = -.1.

So after play with this for a couple of hours, I need to ask -- How do I get the calc field to pick the largest year and subtract the next largest year scores?

• ###### 5. Re: Calculation field between two records in a related table

Since Scorecard data is not always entered sequentially (year 2013 can be entered before 2010, etc)  The physical order of the table records seems to be throwing off the ScorecardDif calculation.

That's it exactly.

In an Unsorted relationship--the default option for FileMaker relationships, the last related record will be the last related record to be created. To get the Last (and next to last) related records to be the Newest ( and next to newest) related records, go to manage | database | Relationships and double click the relationship line connecting these two table occurrences. Click the sort button on the OrganizationScorecard side of the relationship and specify an ascending sort order on some field that will put the records in the order that you need. That would appear to be a field of type date or type timestamp from what I can see here.

• ###### 6. Re: Calculation field between two records in a related table

PhilModJunk,   OK now I feel just pain .   I had forgotten where the Sort function was.  I knew it existed, I just couldn't remember where it was.   Thanks yet again for you great advise.