5 Replies Latest reply on Jul 21, 2011 1:45 PM by philmodjunk

    Calculation to get most recent status for a log of records

    NicoleHartman1367

      Title

      Calculation to get most recent status for a log of records

      Post

      Hi,

      I am trying to create a calculated field that gives me the most recent status for a record.

      I have a log of records that each have an ID, the ID can be duplicated in the database many times with different statuses which have a timestamp to them.

      I want to be able to create a calculated field that indicates that that perticular record is the most recently created status for a given ID. 

      How would I go about doing this? The field would need to be such that it would constantly update as new records are logged.

      Thanks!

      Nicole

        • 1. Re: Calculation to get most recent status for a log of records
          philmodjunk

          Do you have this relationship?

          Maintable::ID = LogTable::ID

          If so, the last function will return the most recent related LogTable record provided no sort order was specified for the relationship.

          You can also set up a sorted relationship to the log table that sorts the related records so that the most recently created record is listed first. Then you can simply refer directly to the related fields in the LogTable and the relationship will refer to the most recent related record. Establishing this sort order would require sorting on a field with an auto-entered value such as sorting by a creation date field or a serial number field in descending order.

          • 2. Re: Calculation to get most recent status for a log of records
            NicoleHartman1367

            I actually only have one table, so I just have one table with thousands of records that have the three fields: ID, Status, Timestamp.

            Since this table is a read-only view from a SQL database I can only add calculated fields to the table.

            Is there not a way to write a calculation that evaluates each record to determine if it is the most recent for the ID based on the timestamp?

            Thanks,

            Nicole

            • 3. Re: Calculation to get most recent status for a log of records
              philmodjunk

              You can still add a second occurrence of this table and relate the two occurrences by ID. If you want, you can sort the related records by the TimeStamp field in descending order. This will work as long as you don't have two records created with the same ID an timestamp (Might happen if to different users generate a log record at the same time.)

              To add a second occurrence, select the existing table in Manage | Database | Relationships and then click the button with two green plus signs.

              You can also perform a find on your existing table for a specific ID, then sort by the timestamp field in descending order to make the most recent record the first one listed.

              • 4. Re: Calculation to get most recent status for a log of records
                NicoleHartman1367

                Ok, thanks - I have set-up the second occurrence in the table but I think I am still missing something.

                What do I need to do to get to a table/view that will always list every ID and only their most recent status?

                Thanks again for your help, you have become my personal FileMaker lifeline....

                Nicole

                • 5. Re: Calculation to get most recent status for a log of records
                  philmodjunk

                  You can set up a summary report on a list view layout where you replace the body layout part with a sub summary part "when sorted by" ID.

                  You can place the ID, This calculation field and any other fields from this table inside the sub summary part.

                  If you then sort your records by ID, you'll get one row for each ID value in the log table.

                  You could also import the data from this table in to a FileMaker table with Unique values specified for ID. That table will give you one record for each ID and you could use it in a similar relationship with your calcualtion field defined in this table instead of the log table.