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

    Showing latest 5 records from each category

    nexgen

      I have a table with following fields:

      category

      transaction_description

       

      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
          bigtom

          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
            philmodjunk

            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
              fmpdude

              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:

               

              SELECT

                  a.id ,

                  a.category ,

                  date

              FROM

                  TopFiveWithinEachCategory AS a

              WHERE

                  (

                      SELECT

                          COUNT(*)

                      FROM

                          TopFiveWithinEachCategory b

                      WHERE

                          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