2 Replies Latest reply on Sep 26, 2014 5:27 AM by KhomanPhang

    Using values from portal lines in a calculation

    KhomanPhang

      Title

      Using values from portal lines in a calculation

      Post

      Hello. I'm trying to do something pretty straightforward and am very close but final step alludes me. It involves calculations using data found in a portal and is related to posts Calculations with portal data. and Using values from portal lines in a calculation.

      I am tracking attendance and want to determine whether a student was present on a given day.  I have two tables, a Students list and an Attendance_Log:

      Table 1 (parent), Students, with the following fields:
         - Student ID
         - Name

      Table 2 (child), Attendance_Log, with the following fields:
         - Student ID
         - Date

      Attempt 1: Using a portal 
         I successfully created a portal on the parent table, and can see
         all the dates that a student attended class. But how does one go
         about searching inside the portal for a specific date?
         
      Attempt 2: Using a calculation 
         Alternately, I tried adding a calculation field in the parent
         table to determine whether an entry exists for each student for a
         particular date. Conceptually, the calculation would be 

            Students::Sept1? = If ( Attendance_Log::Date ="Sept 1"  ; yes ; no )

         but this calculation fails because the result only represents the
         result of the search of the last matching record, and not of all
         the matching records
      found in the child table.

      Knowing how to work with the portal data would open many doors. Any help is much appreciated.

        • 1. Re: Using values from portal lines in a calculation
          philmodjunk

          Using a Portal:

          You could setup a portal filter with an expression that refers to a date in either a global field or a field defined in Students. Enter a date in this field, update the window's display of the data and only the attendance record for that student for that date should show in the portal.

          or without a portal needed:

          you can define a different relationship to a different table occurrence of Attendance_Log where an additional field in student specifies the desired date so that this relationship matches by student ID AND by date.

          Using a calculation:

          ExecuteSQL (if you have FileMaker 12 or newer) could return the desired data by specifying the Student ID in a join clause and the specified date in a WHERE clause.

          If ( Attendance_Log::Date ="Sept 1"  ; yes ; no )

          Just a note of caution, "Sept 1" is NOT a date. It's text and should not equal any value of Attendance_Log::Date as Date should store and actual date value, not text.

          but this calculation fails because the result only represents the  result of the search of the last matching record

          Actually, the calculation will refer only to the Date field of the FIRST related record. (Portal sort orders, Filters do not affect which related record is first.)

          • 2. Re: Using values from portal lines in a calculation
            KhomanPhang

            PhilModJunk,

            I followed your Tutorial: What are Table Occurrences?Tutorial: What are Table Occurrences? (3d8b364a6c) and was able to accomplish the task without a portal. Thank you for both the response and the key tutorial.