1 Reply Latest reply on Nov 18, 2010 9:20 AM by philmodjunk

    Consecutive Dates in Portal



      Consecutive Dates in Portal


      We have 2 tables:



      On the TEAM form we have a portal that displays the Schedule for that TEAM. Since each TEAM can be located in a HOMETEAMID field or an AWAYTEAMID field, we followed PhilModJunk's suggestion by filtering the recordset in his option 2 using FMP11.


      I would like to have 2 calculations on this portal, neither of which I have figured out.

      1. Create a column that indicates if the TEAM in the selected record won the game based on 2 data input values of the scores in the GAMES table. The problem I am facing is that this calculation is going to show exact opposite values for the same GAME depending on which teams filemaker record is being viewed.  How do I calculate a portal entry field based on the master records value?

      2. The second column I am trying to identify when the TEAM has BACK-to-BACK GAMES based on the date field.  The field should be blank on the portal record if the previous portal record was not the previous day and a static value of "YES" or "B2B" if they did play the previous day.

      Thanks for any suggestions.

        • 1. Re: Consecutive Dates in Portal

          That first calculation is an interesting challenge.

          Because of the X operator in your relationship, there's no way via the relationship to get the calculation to consistently refer to the correct parent record. Using my other option, setting up a list of the two key values would have the same limitation.

          I'd set a script trigger with the OnRecordLoad trigger to run this script:

          Set Variable [$$CurrentTeam ; Value:: Team::TeamID]

          Then your calculation can use the data in $$CurrentTeam to correctly produce an "W" or "L" result.

          Case ( $$CurrentTeam = HomeTeamID and Score1 > Score2 ; "W" ;
                     $$CurrentTeam = HomeTeamID ; "L" ;
                     Score1 > Score2 ; "L" ;
                     "W" )

          This calculation assumes that ties are not possible in this sport or you'd need one more pair of parameters in this case function.

          Your back to back game calculation can be done by using GetNthRecord function to check the game date of the previous record. Something like:

          If ( ( GetNthRecord ( GameDate; Get ( RecordNumber ) - 1 ) + 1 ) = GameDate ; "B2B" ; "" )

          Make this an unstored calculation.