2 Replies Latest reply on Feb 10, 2009 11:27 AM by kwseattle

    calculated global fields not propagated to other tables and layouts



      calculated global fields not propagated to other tables and layouts


      We have a two tables:



      - Participant_Key

      - Last_Name




      - Qtr

      - Participant_Key

      - Course


      And there is a relationship:

        Participants:: Participant_Key = Participant_Enrollments:: Participant_Key


      For existing searches and layouts, we had created a bunch of global fields and calculated fields under Participant_Enrollments:



      - Qtr_of_interest_g  (Global, Text)

      - Qtr1_of_interest_year_g  (Global, Calculated Text based off Qtr_of_interest_g)

      - Qtr2_of_interest_year_g  (Global, Calculated Text based off Qtr_of_interest_g)

      - Qtr3_of_interest_year_g  (Global, Calculated Text based off Qtr_of_interest_g)

      - Qtr4_of_interest_year_g  (Global, Calculated Text based off Qtr_of_interest_g)


      If you entered 2008-1, in Qtr_of_interest_g, Qtr1.. Qtr4 became 2007-4, 2008-1, 2008-2, 2008-3.  I.e., the normal quarters of the academic year containing the quarter of interest.


      We now wanted to show the courses a participant has taken throughout the year.


      We attempted to propagate the QtrX... definitions up into Participants:



      - Qtr1_of_interest_year_g

         (Global, Calculated = Participant_Enrollments::Qtr1_of_interest_year_g)

      - Qtr2_of_interest_year_g

         (Global, Calculated = Participant_Enrollments::Qtr2_of_interest_year_g)

      - Qtr3_of_interest_year_g

         (Global, Calculated = Participant_Enrollments::Qtr3_of_interest_year_g)

      - Qtr4_of_interest_year_g

         (Global, Calculated = Participant_Enrollments::Qtr4_of_interest_year_g)


      We then defined a set of table occurances and relationships to Participants as:


      Qtr1_PE - based upon Participant_Enrollments

        Relationship: Qtr1_PE::Qtr = Participants::Qtr1_of_interest_year_g AND

                           Qtr1_PE:: Participant_Key = Participants:: Participant_Key


      etc. for new table occurances Qtr2_PE ... Qtr4_PE. To access the enrollment records for particular academic quarters.


      We created a new layout based upon the Participants table that had

      - Participants::Last_Name

      - Qtr1_PE::Course

      - Qtr2_PE::Course

      - Qtr3_PE::Course

      - Qtr4_PE::Course


      Which we expected to show the courses the participant was enrolled in for the academic year (in our case, each participant can take exactly zero or one course per quarter). 


      On a different layout, we change the Participant_Enrollments::Qtr_of_interest_g.


      That changed the calculated values of the globals within Participant_Enrollments, but there was no propagation of this change into the calculated values in Participants and therefore our layout displaying the courses the participant was enrolled in did not update to the newly selected academic year.


      Is this a bug?  Why would Filemaker act this way -- these are all global values. Shouldn't they propagate through?  


      We verified that the original Participant_Enrollments::Qtr_of_interest_g propagated through onto the new view, but the global fields in Participants based upon this value were never re-calculated.  That is where the problem seems to be -- the table's global fields aren't being re-calculated.


        • 1. Re: calculated global fields not propagated to other tables and layouts



          Thank you for your post.


          First, I'm not sure why you have two sets of global fields; one set in each table.  The values are stored once and available to all records.  Relating Global fields to Global fields is not recommended, because it returns all records.  In your instance, you are using global fields to grab global values from a related table and then relinking the fields again.  Definitely not recommended.  If you want to view Participant Enrollments in any year, remove the global fields from Participant_Enrollments and link the global fields from Participants into Participants_Enrollments::Qtr.  This will definitely get you all students for the current year (or for whatever year you want).



          FileMaker, Inc. 

          • 2. Re: calculated global fields not propagated to other tables and layouts

            TSGal, Thank you for your reply!  I'm new at the relational part of Filemaker, and my husband (who knows non-FileMaker databases) was helping me.  Things certainly weren't working the way he's used to them working.  With your advice, we were able to get it to work, but we had to keep the global fields in the Participant_Enrollments table rather than the Participants table.  


            Thanks again!