New to FMP, please excuse my ignorance; perhaps you can use max(date) in your query or
sort by date desc and select top 1?
In another thread I read this...
"If you use sub summary layout parts and remove the body layout part, we can produce a summary report with just one line for each summarized group of records."
If you Sort the Relationship (not the portal, but the relationship setup itself) so the related record you want will be first (latest date?), don't need a portal on your report, just one instance of the related fields.
They will show the "top-most" data of the related records.
I think I wasn't clear on what I am trying to accomplish.
For the member Rudolph Graham I want to produce in the report just the top value on in the DES portal on the Members Window; the REINSTATED record. I DO NOT want to get the other portal records (Expelled and Suspended).
What I do not want is shown in the 2nd image above. I WANT Reinstated and not Expelled or Suspended.
And, the report does NOT have a portal on it. It is just a LIST.
Create a script that goes from members to the 'real' table (DES?), search for the specific member and desired state, then go to the report-layout, sort. Voila!
The script can get the member-ID from the member table via $Variable
Yes, that would work if I had just a single member. But, I have 150+
members and each needs to show just the most current DES (portal) record in
the sub-summary report.
As you can see, the sub-summary report is sorted on DES::Type which creates
the sub-summary report but also does not differentiate between the most
current DES record and the lesser dated DES records.... grrrrr
thanks for the thought
On Tue, Jul 12, 2016 at 11:22 PM, Markus Schneider <firstname.lastname@example.org>
Why can't you just go to the list layout and perform a find where DES::Type = "Reinstated"?
If you don't tell FileMaker what records you are looking for, it has no idea that you only want the "Reinstated" ones...
Yes, I 'could' go to the layout and find DES::Type="Reinstated" BUT, there are 150+ members and some have a most current of Reinstated. Others may have a current record of "Deceased" etc.... I have no way of knowing what that most current record will be for any given user and the report should show the most current DES::Type no matter what it is.
I want to have FM group by DES::Type and then show the most current DES::Type record; and not show the rest.
If you reference the DES::Type field from the context of Members outside of a portal (don't put the focus on a portal row), it will automatically pull the first record. You can use this to your advantage. Couple of different ways to accomplish this:
1) Put a calculation field (unstored) in the Members table equal to DES::ID (whatever your unique ID is). Use a ListOf Summary field to assemble all those IDs into a list. Go to the report layout (presumably based on DES) and loop over the list, creating a Find request for each ID. Perform the Find and you'll have all the "first" DES records from your original found Members set. (Caution: May be slow.)
2) As in 1, create the DES::ID calc field in Members. Create a new relationship to DES based on that ID field. Use Go to Related Record (entire found set). This will also isolate all DES records corresponding to the "first" record for each member.
3) Write a script to find all DES records for the current Members found set (possibly using GTRR). Sort the list by member ID and then by date. Loop over the records, omitting all but the first one.
One cat, many skinning methods.
Got it. I misunderstood. You want the most recent DES record, not just the "Reinstated" one.
There are a few ways to approach this, Mike explained a few of them in the post above. I've used the ListOf summary field approach, but it does add overhead to the table.
Here is an example of looping over the members and gathering the PK IDs of the "current" DES record based on the relationship that is sorted by date into a return separated list, then going to the DES layout and doing a looping "Extend Found Set" to find all of the DES records in your list.
Thank you for the code and clear explanation.
While I am sure this would work, I find it surprising that there isn't some kind of Filemaker 'setting' or 'workaround' that would facilitate what I am after without the coding.
After all, "Is this situation not uncommon"?
Almost got it... but no cigar.
When I add a 2nd 'Type' (Expelled) ...
The report fails to show the most current 'Expelled' and instead shows ALL Expelled
I wonder, "SInce I want each member and their most current TYPE (Expelled, Suspended etc) perhaps I should be basing the report on Members and not DES...???