3 Replies Latest reply on Jun 11, 2017 5:32 AM by fmpdude

    Showing latest 5 records from each category


      I have a table with following fields:




      I am showing them in list view and have a summary by category. So currently it looks like this:


      • Category 1
        • Transaction Description 6
        • Transaction Description 5
        • Transaction Description 4
        • Transaction Description 3
        • Transaction Description 2
        • Transaction Description 1
      • Category 2
        • Transaction Description 3
        • Transaction Description 2
        • Transaction Description 1


      How can I show only the 5 latest transaction description in each category?

        • 1. Re: Showing latest 5 records from each category

          This gets interesting. Possible but interesting. I would use eSQL to run a query to get the last 5 keys from each category. Load those to a global variable in another table and the use GTRR to get the correct found set for the summary list.

          • 2. Re: Showing latest 5 records from each category

            If you set up a table with one record for each category with a relationship linking it to your current category, you could set up a list view on your category table with a sorted, 5 row portal to show the most recent 5 records.


            You might need additional match fields or a portal filter if your report lists a subset of all transactions.

            • 3. Re: Showing latest 5 records from each category

              This is a common record selection problem. Since I wasn't sure how you were identifying the "most current 5", I added a date column (field) for my test table. Also, I would imagine you would really model this a 1:M relationship since one category could have multiple descriptions.


              In any case, in FileMaker since AFAIK (apologies to all if I've missed this somehow) FileMaker doesn't support SUB-SELECTS, you could do a regular ExecuteSQL to get a list limited by the date column (using FETCH FIRST, and GROUP BY, etc.). Then, process that list in a script to get the exact data you want within each category.


              Or, try some of the other great suggestions already posted.




              Note that in MySQL, this query is doable in a single SELECT statement. There are many, many examples online how to do exactly what you want in MySQL as one example.


              So, given this data:


              And, this query:



                  a.id ,

                  a.category ,



                  TopFiveWithinEachCategory AS a






                          TopFiveWithinEachCategory b


                          b.category = a.category

                      AND b.date >= a.date

                  ) <= 5

              ORDER BY

                  a.category ASC ,

                  a.date DESC




              You would get these results (quickly):


              4    category1    2017-06-24

              6    category1    2017-06-14

              1    category1    2017-06-12

              5    category1    2017-06-08

              2    category1    2017-06-02


              14    category2    2017-07-04

              7      category2    2017-06-30

              12    category2    2017-06-28

              11    category2    2017-06-16

              10    category2    2017-06-13




              HOPE THIS HELPS.

              1 of 1 people found this helpful