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.
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.
1 of 1 people found this helpful
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:
TopFiveWithinEachCategory AS a
b.category = a.category
AND b.date >= a.date
) <= 5
a.category ASC ,
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.