Finding Oldest Date

[Therber]

Hello. I am creating a vendor ordering system for a business and I

[Therber]

I think I located something that may work here:

http://www.maclane.com/cgi-bin/ultimatebb.cgi?/ubb/get_topic/f/20/t/000326.html#000005

[Therber]

We'll, I'm not having much luck getting this to work. In my line items file, each item entered has the vendor ID and the date the item was entered. These should be the only two fields I need, right? I create another occurance of my line items table and make a self-relationship based upon a calculated field. I think my calculation is where I'm having trouble. If anyone know of an example file with this type of behavior, please let me know.

Thanks

[Therber]

OK, I think I figured it out. I had created a self-relationship using the vendor ID field with the line items file. I created another (3rd) instance of the line items file and made a self-relationship with the 2nd instance using the date entered field. The relationship sorts the date entered field ascending to show the oldest date. A calculation field is needed which simply shows the date entered field. "Evaluate this calculation to..." at the top of the create calculation window should referance the 2nd instance of the line items file. Hope this helps anyone else trying to do this.

[Fenton]

Wouldn't your relationship be sorted "descending"?

There is also the Last() function:

Last ( relationship::field)

will get the "last" instead of the first value of a related field. "Last" means the last entered, in creation order. In most cases this is the last in all senses of the word.

People don't see the Last() function because it's in the Repeating Fields section of the drop-down, and says:

Last ( repeatingField)

That's 'cause it was in FileMaker 2, before FileMaker became relational (in 3). But it works with relationships.

So do the Aggregate functions, which just say:

Sum ( field1, field2, etc.)

Also from FileMaker 2.

I believe a sorted relationship is a tiny bit faster (someone else tested thousands, not me).

The Last() of a sorted descending relationship would the first. It's a good idea to label your sorted relationships.