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

    Calculation field between two records in a related table

    gregdc

      Title

      Calculation field between two records in a related table

      Post

           Using FMP 13 Advanced:

           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
          philmodjunk

               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
            gregdc

                 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
              gregdc

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

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

                     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
                  philmodjunk
                       

                            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
                    gregdc

                         PhilModJunk,   OK now I feel just pain blush .   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.