Show only the lasted related record in the body of a report?

[Jhestler]

Hi All,

I have a very nice correspondence tracking database that works perfectly. The main report is a subsummary printed report that shows the status of every Letter mailed to a Customer (eg, Sent, Received, etc.).

I've now been tasked to get the report to show the last Action taken on each outgoing Letter. The Actions are in a separate table; this table is visible as a portal when the user is in the Letters layout.

I now need the only the latest Action taken (each one has a date field that will be populated) on each Letter to appear in the subsummary Letter status report.

For example, the report needs to show the status of the letter (eg, Out for Review) as well as the last action taken (eg, "4/10/10 - Initial draft emailed to Bob Dobolena.").

I don't want the report to show ALL the actions taken; that's easy enough to do, but it's not what's desired (it'd produce a long report of irrelevant info).

I'm totally stuck on this one; I hope there's a way that uses relationships. If I had FM 10, I could use a script trigger to populate the parent field with the contents of a related field when a new data is entered into a portal row, but I don't have FM 10, and I'd still love to use a relationship anyway, if possible.

Any thoughts on this problem would be much appreciated, thanks!

J

[IdealData]

I hope there's a way that uses relationships

And there is...

You need a relationship that is sorted in reverse date order, and use the fields from that relationship on your report.

[comment]

You can also use the existing relationship as is and place the related action fields inside a one-row portal sorted by date, descending.

Or calculate the latest action info by using the Last() function (assuming records are entered in chronological order).

Yet another way is to produce the report from the Actions table, summarized by letter. Sort the actions by date, ascending and make the sub-summary trailing (or vice-versa) and place the required action fields in the sub-summary. Delete the body part.

[Jhestler]

Wow. Thank you, gentlemen (and hello Comment, my old friend!). I will try these right now...

[Jhestler]

Hi Comment,

I love the "Last" function method, and it is dragging the related info into the parent report; the only problem is that it's bringing the oldest related record, rather than the latest.

I tried adding a function to the sort command for the script that generates the report so that it lastly sorts the report by ChildDatabase_ActionDate - Descending. No luck. Tried Ascending as well, just in case Last really means "last," lol. No dice...

Any thoughts? It's soooooo close!

[Jhestler]

Think I might have discovered this issue...

The Parent_Child relationship between Letters and ActionsTakenOnLetters is currently sorted by ActionDate "ascending," and necessarily so for the purpose of displaying a portal of actions in the Letters database when the user is in Browse mode.

For the purpose of this report, however, I duplicated this relationship and sorted it by ActionDate "descending" which seems to give me what I want.

I checked the data on the report against what's in the database, and it all correct!

I can't think of any reason why this won't operate efficiently every time, regardless of how many multi-users; is there anything I need to think about in that respect?

In the meantime, thanks to all for your help!

[comment]

I love the "Last" function method, and it is dragging the related info into the parent report; the only problem is that it's bringing the oldest related record, rather than the latest.

If the relationship is sorted by date, ascending, then Last() will will get data from the latest date. If the relationship is sorted by date, descending, then you can simply place the related action fields on the layout of Letters (the data will come from the first related record).

[Jhestler]

Yes, that did the trick; thank you so much for your help, Comment; your skills seem to know no bounds! Respect : )

J

[Fenton]

One little thing about the Last() function. FileMaker Help says: Returns the last valid, non-blank value in field.

So, almost all the time, it is from the last record matching the relationship (taking into account the sort order). However, if the field is blank in the last record, it will get the value of the field from the the next-to-last, etc., until it finds a value.

Whereas a relationship always returns from the 1st record matching, according to the sort order, whether the field is empty or not.

The Help goes on to say, : "Last(Payments::PaymentDate) returns the payment date in the last matching record in the Payments table."

Which is not entirely accurate. It would only do so if the date field had a value. Which it likely would. But even so; it is not the "last matching record", it is the "last non-empty occurrence of the particular field, of the relationship, taking into account the sort order".

Yeah, I don't really blame them; it's hard to describe.