3 Replies Latest reply on Dec 6, 2012 4:43 PM by Malcolm

    Last date in table

    ajayz

      Hi guys,

       

      I have an incident registers which tracks all incidents for our company. I would like to calculate how many days have passed since the last reported incident. Bascially just a NUMBER.

       

      I'm not too sure how to write the a calculation find the last incidnet date and then subtract this from current date to get the number of days passed.

       

      The date is stored in a field called: "IncidentDate"

        • 1. Re: Last date in table
          Malcolm

          I'm not too sure how to write the a calculation find the last incidnet date and then subtract this from current date to get the number of days passed.

           

           

          The date is stored in a field called: "IncidentDate"

           

          show all records

          sort by date

          go to last record

          get(currentDate) - incidentDate

           

           

          malcolm

          • 2. Re: Last date in table
            ajayz

            Hi Malcolm

             

            I ended up creating two new calculated fields:

             

            a) Lastdate = Max(IncidentDate)

             

            b) LastIncident = Get(CurrentDate) - Lastdate

             

            This works, however I want number to be displaced on Form and display the same number independant of the records being view.

             

            incident image.jpg

            The issue I have is the value changes when I view or search records.  I did try duplicating the table but it still didn't work - any ideas?

            • 3. Re: Last date in table
              Malcolm

              I ended up creating two new calculated fields:

               

               

              a) Lastdate = Max(IncidentDate)

               

               

              b) LastIncident = Get(CurrentDate) - Lastdate

               

               

              This works, however I want number to be displaced on Form and display the same number independant of the records being view.

               

              No, that doesn't work. You aren't giving Max() the correct information. It needs a list.

               

              If you are using FMP12 you could use SQL to get the list (and the max?).

               

              Alternatively, you create a self-join relationship and then LastDate = Max(selfjoin::IncidentDate)

               

              Malcolm