3 Replies Latest reply on Apr 7, 2017 9:33 AM by pademo57

    Calculate and Display Dates from Portal in Parent

    pademo57

      I have a relationship between Course table and Course_Details table:

      CRS::_pk_CRS_ID -> CRSDET::_fk_CRSdet_ID

       

      In the Course Layout you can add a new Course which has a Course Name, Overall Start Date and Overall End Date.

      In the portal from Course_Details you can add Sections.

       

      Each Section requires a CRSDET_Name, CRSDET_Start Date, CRSDET_Number of Days, CRSDET_End Date

       

      What I would like to do is when someone changed the first Section Start Date it will automatically put it into the Course::Overall Start Date

       

      Also when someone has finished adding sections that it puts in the last portal record CRS::End_Date into the Course::Overall End Date.

       

      I tried using a script trigger that when someone modifies a Start Date or and End Date it updates the Overall field but it doesn't work.

       

      Can someone shed some light on this please.

       

      Here is a snapshot:

      Course Days.PNG

      I put the dates in to show where I would like them to show up

        • 1. Re: Calculate and Display Dates from Portal in Parent
          philmodjunk

          If you were to enter layout mode, make a copy of the start date field object in the portal row and drag it up to take the place of the start date field to the right of Course Type, it would automatically show the start date of the first row of your portal if it is unsorted. To make sure that this is the earliest start date, you can specify a sort order in the relationship that sorts by Start date. With that setup, you could also define the start date field in CRS as a calculation field that simply copies the value of the related start date, but you might not even need that field given the fact that you can just refer to the related detail record's start date field in many contexts such as this layout.

           

          And with that sorted relationship, there are two ways to show the needed end date:

           

          Use a one row portal to the details table and sort it by end date in Descending order. Place the End Date field from Details in this one row portal.

           

          or

           

          Define the End Date field in the CRS table as:

          Max ( CRSDET::End Date )

           

          Hmmm, come to think of it, Min ( CRSDET::Start Date )  could be used to show the earliest start date even with out any sorted relationship being used.

          • 2. Re: Calculate and Display Dates from Portal in Parent
            Jaymo

            Maybe you can make the Start and End Date fields in the Course table into calculations that simply reflect what's in the Course Details table:

             

            Start Date:

            Min(Course_Details::Start Date)

             

            End Date:

            Max(Course_Details::End Date)

            • 3. Re: Calculate and Display Dates from Portal in Parent
              pademo57

              Thank you both philmodjunk and Jaymo,

               

              Yes using Min and Max worked wonderfully once I realized that in order for it to work (and this is the embarrassing part) you have to pay attention to that area at the bottom that says "Calcualtion result is ..." AND once I changed it to date it worked.

               

              Why doesn't Filemaker recognize the field and change the "Calculation result" especially if there is only one field???