Calc: Show second record contents

[AlanP]

This field does *not* use repeating fields. What I need to do is show the 'prior' record, not the current one.

I.e. the following are records for this related field (based on date):

1/1/2006 Content A

1/3/2006 Content B

1/5/2006 Content A

So this field will show "Content B" because it is the second most current data.

Hope that makes sense and thanks for the help!

[David Jondreau]

Don't know if this is most efficient way....

I'm assuming all the dates are unique (otherwise how do you know which is the penultimate data?).

Make sure that relationship is sorted by Date (descending). Create a calc field in the parent file MaxDate = childFile::date. That calculation should return the first matching record i.e. the highest date.

Create another TO of the childFile and a relationship between the parent and 2nd child with the same criteria as the first, except add one more criterion.

parentFile::MaxDate > childFile2::Date.

Have that relationship sorted by Date (descending) and create another calc field PriorContent = childFile2::Content.

[AlanP]

Hi David...thanks! I am following you, but also not following you at the same time. When it comes to relationships in FM I'm not too up to speed on that.

They all have unique dates (using timestamp, although it would always be more unique by having different dates).

So basically the solution is to do relationships and there is no one calculation that can be performed?

[comment]

If all you need is to show it, you can show it in a portal (using your existing relationship). Sort the portal by the timestamp field descending, and format it to show row #2 only. However, if there are two related records with the same timestamp...

[David Jondreau]

Comment's answer is the simpler one unless you need to use the data somehow. Keep in mind a portal is just a way of displaying data from a relationship. So if you want to use that field in a calculation, for instance, you'll need to use another method.

In later versions of Filemaker (8.0+) than what's listed on your profile, there's a function called GetNthRecord which does this real easy.

[AlanP]

Thanks guys I appreciate it! This data is just for 'viewing' purposes on a report so this works

We're waiting for our budget to get in so we can finally upgrade to 8.5!