Looking to sort based on date of current record

[eyeballs]

Thanks for helping out.... I have a series of records, one of the fields being the date the record was entered. I would like to show fields from the previous record, or, the second to most recent record. So when I am looking at a record, I would like to see some data from a field from the last record entered, the second to last date.

I have my layout currently set with a portal to show the field, but when I sort, I sort in a decending manner, and it shows me the information for the current record that I am in because that is the most recent field, so I am hoping that it is possible to sort decending in a portal, while skipping the most recent record and show the second most recent record in the portal.

Thanks so much!

-Steve

[transpower]

Set up a non-equijoin table occurrence. The connection will be all records not equal to the current record.

[-Queue-]

To elaborate, make a self-relationship of table::serial <> table 2::serial and sort it by datefield descending. You don't need a portal for this; putting the related fields on the layout by themselves will work, unless you want to see more than one previous record at a time. And if you want to reference fields in the previous record, just use table 2::field.

[eyeballs]

Thanks, I did set up a self-relationship, and I did sort the 2nd table by the date, and when I look at a record, I can view the field from the table 2, but it is always showing the data from the most recent record, and I would like to see the data from the previous record, when sorted by date.

Record 1 6/10/2000

Record 2 7/10/2000

Record 3 8/10/2000

When I view record 3, I would like to be able to show certain data from record 2, and when I view record 2, I would like to see data from record 1.

Thanks again for you help, I am a novice, bay am learning fast that Filemaker is a very underappreciated database!

Thanks

-Steve

[transpower]

Try the relationship "<" in the Relationships Graph.

[-Queue-]

Actually, it does work, but only once you commit the new record you've created.

Try adding table::date > table 2::date to the table::serial <> table 2::serial relationship. This way the relationship won't evaluate until both fields are entered.

[eyeballs]

Excellent Idea! That should do the trick. I thought I would have to use counters, but I will try this out, I'm sure it will work, thanks to both Queue and Transpower for your help! Probably saved me hours upon hours.

-Steve