7 Replies Latest reply on Aug 7, 2013 7:44 AM by philmodjunk

    Reporting only the Last Occurrence



      Reporting only the Last Occurrence


           I have a database in filemaker pro that is being continuously added to everyday.  I am trying to find a way to create a report that only has the last instance of the last time each object was used.  For example, the database I am creating involves trailers and their movement around a general area.  So each trailer name is specific, but the trailer may be used multiple times in a week.  The soltion I am trying to create would be a report that only shows the last time each trailer had been used along with its various information tied to that record when it was created.  Any help or insight would be greatly appreciated!

        • 1. Re: Reporting only the Last Occurrence

               Does your database include a table where a new record is created each time the item is used? Is the date recorded in that table's record?

               If so, you can use sorting to access the record of the last time an item was used by sorting on the date field in descending order. And relationships as well as portals can specify such a sort order as well.

               And from the context of a related table where the relationship is not sorted, the Last function can return data from a field in the last related record.

          • 2. Re: Reporting only the Last Occurrence

                 I have the date included with the record, but I only want each item and only the last record accociated with it.  So if i sort baed on item and descending date then I get the items broken up by date and all the times the item was used.  I simply want only the last time each item was used in the report, but I cannot seem to find a solution to this problem

            • 3. Re: Reporting only the Last Occurrence

                   That is not necessarily the case.

                   Say you have this data model:


                   Items::__pkItemID = Usage::_fkItemID

                   Specify in this relationship that Usage be sorted by Date in descending order. (double click the relationship line in Manage | Database | Relationships)

                   Then you can set up a layout in Items that lists each item, and you can include fields onto this layout from Usage to show both the date most recently used as well as any other details from that usage record that you need.

              • 4. Re: Reporting only the Last Occurrence

                     I understand the basics of what you are describing, but I am a new user to FileMaker pro and complex database work in general. This being the case forming relationships is confusing to me as I only have one table and trying to create a report from only this table.  Is your proposed solution utilizing what filemaker calls "self-join" relationship?  If this is the case I would join the item to the last used date? after this is where I get stuck simply because I do not know the inner workings of the software

                • 5. Re: Reporting only the Last Occurrence

                       I was not recommending a self join--a relationship between two occurrences of the same table, but rather that you add a new table. I would think that you'd find such a table where you have one record for each item very useful. I did forget to include this info in my last post:

                       For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                       It might be possible to set up a report based on just your current table as long as all you need to see are the most recent dates for each item. This method does not enable you to see additional data from that same record.

                       Create a new blank layout.

                       While still in layout modek double click the label for your Body layout part to open Part Setup to change it to a sub summary part "when sorted by" some field in your table that uniquely identifies each different item.

                       Define a summary field in your table as the maximum of your date field. Place this summary field and any name, description and/or id fields in the sub summary part.

                       When you return to browse mode, your layout will appear blank. But select Show All Records then sort them in a sort order that includes the "when sorted by" field you specified and you should see one row for each item of equipment and the date it was most recently used.

                  • 6. Re: Reporting only the Last Occurrence

                         Thank you! This really helped a lot, but there is one more snag. I completed all the steps you posted except for when it came time to show all.  I was not able to click on show all because it was grayed out.  I could see each last occurence in the continuous list view, but it still said my records were sorted and I couldnt show all.  I was wondering if you might know why this is the case ?

                    • 7. Re: Reporting only the Last Occurrence

                           It just means that Show All Records won't do anything because all records in the table are already in your found set.